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 (1)
The duka dataset is the right call for this — the join semantics only stick when you can read the missing rows yourself. Two things I would add to the SELF JOIN section. The <> filter removes only a row matching itself, so a customer pair sharing Westlands comes back twice, and with three people in one location you get six rows. a.customer_id < b.customer_id gives each pair once, which matters the moment you count pairs instead of listing them.
Also the LEFT JOIN ... WHERE o.order_id IS NULL shape is the one I use most in practice, under a name beginners do not expect: an anti-join, the standard way to ask "which of these never happened". One dialect footnote for readers following along: MySQL has RIGHT JOIN but no FULL OUTER JOIN, so the last row of your table usually ends up rewritten as a LEFT JOIN with the tables swapped.