LEFT and RIGHT OUTER JOIN

A left outer join keeps every row of the left table; where no right row matches, the right-side columns are NULL. RIGHT JOIN is the mirror image, and MySQL 524 rewrites it internally as a left join, so prefer LEFT JOIN and put the table you must keep first. OUTER is optional.

Which rows each join keeps, for A(a) = 1-4 and B(b) = 3-6

The classic outer-join bug is a filter in the wrong clause. Both queries look for the Malaysian customers' shipped orders:

A right-table filter in ON versus in WHERESQL

SELECT c.name, o.id FROM customers AS c
LEFT JOIN orders AS o ON o.customer_id = c.id AND o.status = 'shipped'
WHERE c.country = 'MY';
SELECT c.name, o.id FROM customers AS c
LEFT JOIN orders AS o ON o.customer_id = c.id
WHERE c.country = 'MY' AND o.status = 'shipped';

Output

+--------------+------+
| name         | id   |
+--------------+------+
| Chen Wei     | NULL |
| Farah Rahman | NULL |
+--------------+------+
2 rows in set (0.001 sec)
Empty set (0.000 sec)

In ON, the condition decides what matches, and unmatched customers stay. In WHERE, it runs after the padding, rejects the NULL status, and quietly turns the outer join into an inner one.

Counting orders per customer over such a join, COUNT(*) gives Gus and Hana, who never ordered, 1 each for the padded row; COUNT(o.id) gives 0, and SUM needs COALESCE(SUM(...), 0).

The padding also yields the anti-join: WHERE o.id IS NULL after the LEFT JOIN returns exactly Gus and Hana, as does NOT EXISTS (IN, ANY, ALL, and EXISTS), and EXPLAIN shows a "Nested loop antijoin" for both. Test a NOT NULL column such as the key.

MENU

LEFT and RIGHT OUTER JOIN