Explain INNER JOIN vs LEFT JOIN with a practical example.

Detailed explanation

Choose the join by asking: which rows must I not lose? If every customer must appear in a report, the customer table is the left side of a LEFT JOIN. If only customers who actually ordered matter, an INNER JOIN says so explicitly.

Sample data

Asha has two orders (one cancelled), Ben has one, Chen has none.

CREATE TABLE customers (customer_id INT PRIMARY KEY, name TEXT NOT NULL);
CREATE TABLE orders (
  order_id    INT PRIMARY KEY,
  customer_id INT NOT NULL REFERENCES customers,
  status      TEXT NOT NULL,
  amount      NUMERIC(10, 2) NOT NULL
);
INSERT INTO customers VALUES (1, 'Asha'), (2, 'Ben'), (3, 'Chen');
INSERT INTO orders VALUES
  (101, 1, 'delivered', 100.00),
  (102, 1, 'cancelled',  20.00),
  (103, 2, 'delivered',  20.00);

Both joins side by side

SELECT c.name, o.order_id, o.amount
FROM customers c
INNER JOIN orders o ON o.customer_id = c.customer_id
ORDER BY c.name, o.order_id;

name	order_id	amount
Asha	101	100.00
Asha	102	20.00
Ben	103	20.00

SELECT c.name, o.order_id, o.amount
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.customer_id
ORDER BY c.name, o.order_id;

name	order_id	amount
Asha	101	100.00
Asha	102	20.00
Ben	103	20.00
Chen	NULL	NULL

Asha appears twice in both results: a join repeats the left row once per match. Only the left join keeps Chen.

Counting per customer, including zero

SELECT c.name,
       COUNT(o.order_id)          AS orders,
       COUNT(*)                   AS wrong_count,
       COALESCE(SUM(o.amount), 0) AS revenue
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.customer_id
GROUP BY c.customer_id, c.name
ORDER BY c.name;

name	orders	wrong_count	revenue
Asha	2	2	120.00
Ben	1	1	20.00
Chen	0	1	0

COUNT(*) counts Chen’s padded row as one order; COUNT(o.order_id) ignores the NULL. SUM over no values is NULL, hence the COALESCE.

The WHERE trap

Count only delivered orders, still listing every customer:

-- Wrong: the filter runs after the join and removes Chen's NULL row
SELECT c.name, COUNT(o.order_id) AS delivered
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.customer_id
WHERE o.status = 'delivered'
GROUP BY c.name
ORDER BY c.name;

name	delivered
Asha	1
Ben	1

-- Right: the condition is part of the match
SELECT c.name, COUNT(o.order_id) AS delivered
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.customer_id AND o.status = 'delivered'
GROUP BY c.name
ORDER BY c.name;

name	delivered
Asha	1
Ben	1
Chen	0

Chen is back with 0. In the wrong version, o.status = 'delivered' is NULL (unknown) for Chen’s padded row, so WHERE drops him. A condition on the right table belongs in ON; a condition on the left table belongs in WHERE.

When to use which

Question	Join
Orders with their customer details	INNER (every order has a customer)
Every customer, with order stats if any	LEFT from customers
Customers with no orders	LEFT ... WHERE o.order_id IS NULL, or NOT EXISTS
Reconcile two systems, show both sides’ gaps	FULL OUTER

Common mistakes

1. COUNT(*) instead of COUNT(right_key) after a left join.
2. Filtering the right table in WHERE, which turns the left join into an inner join.
3. Assuming a left join cannot add rows. If the right side has several matches, the left row is repeated once per match, and sums over left-table columns are inflated.
4. Chaining an inner join after a left join (customers LEFT JOIN orders INNER JOIN payments), which drops the customers the left join kept, for the same reason as the WHERE trap.