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).

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

duka_customers

duka_orders

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:

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:

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:

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:

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

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