LEFT & RIGHT JOIN

An INNER JOIN throws away every row that has no partner in the other table. Often that is exactly the information you need. Which customers signed up but never ordered? Which products has nobody bought? Which employees have no manager? An inner join can never answer these questions, because the interesting rows are the ones it drops.

Outer joins keep those rows. In this lesson you will use LEFT JOIN and RIGHT JOIN, find the rows with no match, count zeros correctly and learn the most common outer-join bug: a condition in the wrong place.

LEFT JOIN keeps every row from the left table

In A LEFT JOIN B, table A is the left table (it is written first) and B is the right table. A left join returns every row of A. Where B has a matching row, its columns are filled in; where it does not, they are filled with NULL.

SELECT c.customer_id, c.first_name, o.order_id, o.status
FROM customers AS c
LEFT JOIN orders AS o ON o.customer_id = c.customer_id
WHERE c.customer_id >= 9
ORDER BY c.customer_id;

customer_id | first_name | order_id | status
------------+------------+----------+----------
9           | Arjun      | 111      | pending
10          | Isha       | 112      | delivered
11          | Farhan     | 114      | delivered
12          | Divya      | NULL     | NULL
(4 rows)

Arjun, Isha and Farhan each have one order. Divya (customer 12) has none, but she still appears, with NULL in the order columns. An inner join would have left her out. (The WHERE here only keeps the output short; remove it in the editor to see all 16 rows.)

The full name is LEFT OUTER JOIN, but the word OUTER is optional and rarely written.

Finding rows with no match

Because unmatched rows have NULL on the right side, you can keep only them by testing for NULL. This finds every customer who has never placed an order:

SELECT c.customer_id, c.first_name, c.city
FROM customers AS c
LEFT JOIN orders AS o ON o.customer_id = c.customer_id
WHERE o.order_id IS NULL;

customer_id | first_name | city
------------+------------+-----
12          | Divya      | Pune
(1 row)

Test a column that can never be NULL in a real match, such as the right table's primary key (o.order_id). The same pattern finds products nobody has bought:

SELECT p.product_id, p.product_name, p.stock
FROM products AS p
LEFT JOIN order_items AS oi ON oi.product_id = p.product_id
WHERE oi.item_id IS NULL;

product_id | product_name    | stock
-----------+-----------------+------
12         | Graphics Tablet | 12
(1 row)

The Graphics Tablet has 12 units in stock and no sales: a good candidate for a discount. EXISTS, ANY & ALL shows another way to ask the same question.

Counting with a LEFT JOIN

To count orders per customer including customers with zero orders, combine LEFT JOIN with GROUP BY. But be careful what you count:

SELECT c.first_name,
       COUNT(*) AS wrong_count,
       COUNT(o.order_id) AS order_count
FROM customers AS c
LEFT JOIN orders AS o ON o.customer_id = c.customer_id
WHERE c.customer_id >= 9
GROUP BY c.customer_id, c.first_name
ORDER BY c.customer_id;

first_name | wrong_count | order_count
-----------+-------------+------------
Arjun      | 1           | 1
Isha       | 1           | 1
Farhan     | 1           | 1
Divya      | 1           | 0
(4 rows)

COUNT(*) counts rows, and Divya has one row (filled with NULLs), so it says 1. COUNT(o.order_id) counts only non-NULL order IDs, so it correctly says 0. With a left join, always count a column from the right table.

Put right-table conditions in ON, not WHERE

Suppose you want every customer with their number of delivered orders, including those with none. The natural attempt puts the status test in WHERE:

SELECT c.first_name, COUNT(o.order_id) AS delivered_orders
FROM customers AS c
LEFT JOIN orders AS o ON o.customer_id = c.customer_id
WHERE o.status = 'delivered'
GROUP BY c.customer_id, c.first_name
ORDER BY c.customer_id;

first_name | delivered_orders
-----------+-----------------
Aarav      | 2
Priya      | 2
Rohan      | 1
Kabir      | 1
Meera      | 1
Sneha      | 1
Isha       | 1
Farhan     | 1
(8 rows)

Only eight customers are left. Ananya, Vikram, Arjun and Divya disappeared, because their rows have a status other than delivered, or NULL, and WHERE throws those rows away after the join. The outer join has effectively turned back into an inner join.

The fix is to put the condition in the ON clause, so it becomes part of the matching rule:

SELECT c.first_name, COUNT(o.order_id) AS delivered_orders
FROM customers AS c
LEFT JOIN orders AS o
  ON o.customer_id = c.customer_id AND o.status = 'delivered'
GROUP BY c.customer_id, c.first_name
ORDER BY c.customer_id;

first_name | delivered_orders
-----------+-----------------
Aarav      | 2
Priya      | 2
Rohan      | 1
Ananya     | 0
Kabir      | 1
Meera      | 1
Vikram     | 0
Sneha      | 1
Arjun      | 0
Isha       | 1
Farhan     | 1
Divya      | 0
(12 rows)

All twelve customers are there, with 0 for the four who have no delivered order. The rule: conditions on the right table of a left join go in ON; conditions on the left table (like c.customer_id >= 9 earlier) can go in WHERE.

RIGHT JOIN

RIGHT JOIN is the mirror image: it keeps every row of the right table, the one written after RIGHT JOIN. Here order_items comes first and products second, and the query still finds the product that was never ordered:

SELECT p.product_name, oi.order_id
FROM order_items AS oi
RIGHT JOIN products AS p ON p.product_id = oi.product_id
WHERE oi.item_id IS NULL;

product_name    | order_id
----------------+---------
Graphics Tablet | NULL
(1 row)

This is the same question as the LEFT JOIN query earlier, with the two tables swapped. Any right join can be rewritten as a left join in this way. Most developers do exactly that, because queries are easier to follow when the "keep everything" table comes first. You will still meet RIGHT JOIN in other people's code, so it is worth recognising.

Common mistakes

• Testing the right table in WHERE. It removes the NULL rows and turns the left join into an inner join. Move the condition into ON.
• Using COUNT(*) after a LEFT JOIN. Unmatched rows still count as one. Count a right-table column such as COUNT(o.order_id).
• Following a LEFT JOIN with an INNER JOIN. In customers LEFT JOIN orders JOIN order_items, the inner join drops Divya again because her NULL order has no items. Make the later joins LEFT JOIN too.
• Mixing up left and right. The left table is the one written before LEFT JOIN. If the wrong rows are missing, check which table comes first.

Key points

• LEFT JOIN keeps every row from the left table and fills missing matches with NULL.
• WHERE right_table.id IS NULL after a left join finds rows that have no match.
• Count a right-table column, not *, to get correct zeros.
• Conditions on the right table belong in ON; in WHERE they cancel the outer join.
• RIGHT JOIN keeps every row from the right table; it can always be rewritten as a LEFT JOIN.
• MySQL has no FULL OUTER JOIN; combine left and right joins with UNION instead.

SELECT p.product_name,
       COALESCE(SUM(oi.quantity), 0) AS units_sold
FROM products AS p
LEFT JOIN order_items AS oi ON oi.product_id = p.product_id
GROUP BY p.product_id, p.product_name
ORDER BY units_sold, p.product_name;