LEFT, RIGHT, and FULL OUTER JOIN

INNER JOIN drops rows that do not match on both sides, which is sometimes exactly right. But “all users — and their orders if they have any” is a different question, and it calls for an outer join: one that keeps rows from a side even when the other side has nothing to offer, filling the gaps with NULL.

Join	Keeps
LEFT JOIN	all rows from the left table, plus matches from the right (NULLs where none)
RIGHT JOIN	the mirror — all rows from the right
FULL OUTER JOIN	all rows from both sides, NULL-filled wherever there is no match

LEFT JOIN keeps every left-side row

Recall our three users — Asha and Carlos have orders, Bo has none. An inner join would lose Bo; a left join keeps her, with NULLs where the order would be:

SELECT u.name, u.country, o.id AS order_id, o.product
FROM   users u
LEFT JOIN orders o ON o.user_id = u.id
ORDER BY u.name;

name	country	order_id	product
Asha	IN	101	Pencil
Asha	IN	102	Laptop
Bo	CN	NULL	NULL
Carlos	US	103	Coffee

Bo survives, and her right-side columns are NULL — the engine’s way of saying “no match here.” That is exactly what powers the include-zeros pattern: counting per group while keeping the empty groups.

SELECT u.name, COUNT(o.id) AS num_orders
FROM   users u
LEFT JOIN orders o ON o.user_id = u.id
GROUP BY u.name;

name	num_orders
Asha	2
Bo	0
Carlos	1

Two details earn their keep here. COUNT(o.id) counts non-NULL order ids, so Bo gets 0 — whereas COUNT(*) would have counted her single result row as 1. And for a sum, COALESCE(SUM(...), 0) turns the NULL that SUM returns over nothing into a clean zero.

The ON-vs-WHERE trap

When you filter the optional (right) side, where you put the filter changes everything. Filter inside ON and the left side is preserved:

... LEFT JOIN orders o ON o.user_id = u.id AND o.product = 'Laptop'

name	order_id	product
Asha	102	Laptop
Bo	NULL	NULL
Carlos	NULL	NULL

Every user stays; only matching Laptop orders attach. But move that same filter to WHERE, and the NULL rows fail the test and vanish — silently collapsing the left join back into an inner join:

... LEFT JOIN orders o ON o.user_id = u.id
    WHERE o.product = 'Laptop'      -- drops Bo and Carlos!

name	order_id	product
Asha	102	Laptop

RIGHT JOIN is just LEFT JOIN with the tables swapped — almost nobody writes it, since you can always put the table you care about on the left. FULL OUTER JOIN keeps everything from both sides and is the tool for reconciliation reports (“every row in the source, every row in the warehouse, and where they line up”).

Practice

Quick check

0/3

Q1You want every user with their order count (zero for users who never ordered). Correct query?

FROM users JOIN orders ON … GROUP BY userFROM users LEFT JOIN orders ON … GROUP BY user, with COUNT(o.id)FROM users LEFT JOIN orders ON … GROUP BY user, with COUNT(*)FROM orders LEFT JOIN users ON … GROUP BY user

Check answer

Q2What happens with LEFT JOIN orders o ON … WHERE o.status = 'paid'?

Works as intendedIt silently becomes an INNER JOIN — the WHERE drops the NULL rowsSyntax errorReturns no rows

Check answer

Q3What does FULL OUTER JOIN return for a row that matches on neither side?

Nothing — only matched rows appearThe unmatched row, with the other side's columns NULLAn errorTwo rows

Check answer