SQL Joins Made Simple

Relational databases often store related information across multiple tables. To answer real-world questions, we need to connect these tables using SQL JOINs. The main types of joins - INNER, LEFT, RIGHT, and SELF JOIN A JOIN allows us to combine rows from two or more tables based on a related column (often a primary key and foreign key). | JOIN Type | What It Returns | When to Use | |---|---|---| | INNER JOIN | Only rows that match in both tables | When you want rows with data on both sides | | LEFT JOIN | All rows from the left table + matches from the right (NULLs if no match) | When you want to keep all left rows | | RIGHT JOIN | All rows from the right table + matches from the left (NULLs if no match) | When you want to keep all right rows | | SELF JOIN | A table joined to itself | When comparing rows within the same table | SQL Joins Explained Through a Duka Shop Database We’ll use three tables: Products (duka_products) → what you sell, their price, category, and supplier Customers (duka_customers) → who shops at your store Orders (duka_orders) → what each customer buys, when, and in what quantity To answer business questions, you need to connect these tables. That’s where SQL JOINs come in duka_products | product_id | product_name | product_category | price | stock_level | supplier | |---|---|---|---|---|---| | 1 | Mandazi Mix | Snacks & Bakery | 120.00 | 40 | Nairobi Foods Ltd | | 2 | Githeri Pack | Grains & Cereals | 210.00 | 55 | Rift Valley Millers | | 3 | Fresh Yogurt | Dairy | 95.00 | 25 | Brookside Dairy | | 4 | Herbal Tea | Beverages | 280.00 | 30 | Kenya Beverages Ltd | | 5 | Bar Soap | Household | 60.00 | 70 | Metro Wholesalers | duka_customers | customer_id | name | phone | location | |---|---|---|---| | 1 | Alice Kariuki | 0712345678 | Westlands | | 2 | Brian Oduor | 0723456789 | Kasarani | | 3 | Carol Wanjiru | 0734567890 | Rongai | | 4 | Daniel Mwangi | 0745678901 | Westlands | | 5 | Esther Njeri | 0756789012 | Thika | duka_orders | order_id | customer_id | product_id | quantity | order_date | |---|---|---|---|---| | 1 | 1 | 2 | 3 | 2026-06-01 | | 2 | 2 | 4 | 1 | 2026-06-02 | | 3 | 3 | 1 | 2 | 2026-06-03 | | 4 | 1 | 3 | 1 | 2026-06-04 | | 5 | 4 | 5 | 4 | 2026-06-05 | 1. INNER JOIN - Matching Rows Only Concept: Returns only rows where there is a match in both tables. Use Case: When you only want rows with data on both sides. Query: Which customer placed which order? SELECT c.name, o.product_id, o.quantity FROM duka.duka_customers c INNER JOIN duka.duka_orders o ON c.customer_id = o.customer_id; Output: | name | product_id | quantity | |---|---|---| | Alice Kariuki | 2 | 3 | | Alice Kariuki | 3 | 1 | | Brian Oduor | 4 | 1 | | Carol Wanjiru | 1 | 2 | | Daniel Mwangi | 5 | 4 | Customers without orders are excluded. 2. Multi-Table INNER JOIN - Richer Insights Concept: You can join more than two tables to get detailed reports. Use Case: When you want to combine customers, orders, and products. Query: Which customers ordered which products, and in what quantity? SELECT c.name, p.product_name, o.quantity FROM duka.duka_orders o INNER JOIN duka.duka_customers c ON c.customer_id = o.customer_id INNER JOIN duka.duka_products p ON p.product_id = o.product_id; Output: | name | product_name | quantity | |---|---|---| | Alice Kariuki | Githeri Pack | 3 | | Alice Kariuki | Fresh Yogurt | 1 | | Brian Oduor | Herbal Tea | 1 | | Carol Wanjiru | Mandazi Mix | 2 | | Daniel Mwangi | Bar Soap | 4 | This helps you understand customer preferences. 3. LEFT JOIN - Keep All Left Rows Concept: Returns all rows from the left table, plus matches from the right. Use Case: When you want to keep all left rows, even if no match exists. Query: Which products have never been ordered? SELECT p.product_name FROM duka.duka_products p LEFT JOIN duka.duka_orders o ON p.product_id = o.product_id WHERE o.order_id IS NULL; Output: | product_name | |---| | Herbal Tea | | Mandazi Mix | Useful for inventory management - these products are unsold. 4. RIGHT JOIN - Keep All Right Rows Concept: Returns all rows from the right table, plus matches from the left. Use Case: When you want to keep all right rows, even if no match exists. Query: Show all customers, even those who never ordered. SELECT c.name, o.quantity FROM duka.duka_orders o RIGHT JOIN duka.duka_customers c ON o.customer_id = c.customer_id; Output: | name | quantity | |---|---| | Alice Kariuki | 3 | | Brian Oduor | 1 | | Carol Wanjiru | 2 | | Daniel Mwangi | 4 | | Esther Njeri | NULL | Customers like Esther Njeri appear even if they haven’t ordered. 5. SELF JOIN - Compare Within the Same Table Concept: A table joined to itself. Use Case: Comparing rows within the same table. Query: Which customers live in the same location? SELECT a.name AS customer1, b.name AS customer2, a.location FROM duka.duka_customers a INNER JOIN duka.duka_customers b ON a.location = b.location AND a.customer_id <> b.customer_id; Output | customer1 | customer2 | location | |---|---|---| | Alice Kariuki | Daniel Mwangi | Westlands | Helps you plan local promotions. General Notes on Joins INNER JOIN → Only matched rows LEFT JOIN → All left rows + matches (NULLs for missing right rows) RIGHT JOIN → All right rows + matches (NULLs for missing left rows) SELF JOIN → Compare within the same table Top comments (0)