LEFT, RIGHT and FULL OUTER JOIN

Why outer joins?

An inner join answers "show me the pairs". Many business questions are about everyone, including those with nothing: every customer with their number of orders (0 included), every product with its sales, every department with its headcount. If you use an inner join there, the zeros silently vanish, and the report looks complete when it is not.

LEFT JOIN

LEFT JOIN (also written LEFT OUTER JOIN) keeps every row of the table written before it, the left table. When a left row finds a partner, the columns of the right table are filled in as usual. When it finds none, the row is still kept, and every right-hand column is NULL.

SELECT c.name, o.id AS order_id, o.status
FROM customers AS c
LEFT JOIN orders AS o ON o.customer_id = c.id;
-- Asha Verma  | 101  | Delivered
-- Asha Verma  | 103  | Delivered
-- ...
-- Sneha Patil | 107  | Placed
-- Arjun Reddy | NULL | NULL      <- kept, no orders

Counting with zeros

Combine a LEFT JOIN with GROUP BY from module 4. Count a column from the right table, not *. COUNT(o.id) skips NULLs, so Arjun gets 0. COUNT(*) counts rows, and his single NULL-filled row would wrongly give 1.

SELECT c.name, COUNT(o.id) AS order_count
FROM customers AS c
LEFT JOIN orders AS o ON o.customer_id = c.id
GROUP BY c.id, c.name;
-- Asha Verma 3, Ravi Kumar 2, Priya Nair 1, Imran Khan 1, Sneha Patil 1, Arjun Reddy 0

The anti-join: rows with no match

An anti-join returns the rows of one table that have no partner in another. With a LEFT JOIN, the unmatched rows are exactly the ones where the right table's key is NULL. Test the right table's primary key (or the join column), because it is never NULL in a real match.

SELECT p.id, p.name
FROM products AS p
LEFT JOIN order_items AS oi ON oi.product_id = p.id
WHERE oi.product_id IS NULL;
-- 8 | Power Bank 10000mAh

RIGHT JOIN

RIGHT JOIN keeps every row of the table written after it. a RIGHT JOIN b is the same as b LEFT JOIN a, with the columns in a different order. Most teams write only LEFT JOINs, because reading "keep everything from the table I started with" is easier. If you meet a RIGHT JOIN, swap the two tables and make it a LEFT JOIN.

-- These two return the same 9 rows:
SELECT c.name, o.id FROM orders o RIGHT JOIN customers c ON c.id = o.customer_id;
SELECT c.name, o.id FROM customers c LEFT JOIN orders o ON o.customer_id = c.id;

FULL OUTER JOIN

FULL OUTER JOIN keeps unmatched rows from both sides. It is useful for comparing two lists, for example last month's and this month's stock, or employees and departments, where both sides may have orphans.

SELECT e.name AS employee, d.name AS department
FROM employees AS e
FULL OUTER JOIN departments AS d ON d.id = e.dept_id;
-- ... 10 matched pairs ...
-- Deepak Yadav | NULL    <- employee with no department
-- NULL         | Legal   <- department with no employee

-- MySQL: emulate FULL OUTER JOIN
SELECT e.name, d.name FROM employees e LEFT JOIN departments d ON d.id = e.dept_id
UNION
SELECT e.name, d.name FROM employees e RIGHT JOIN departments d ON d.id = e.dept_id;

Common mistakes

• Using COUNT(*) after a LEFT JOIN and getting 1 instead of 0. Count a right-table column, such as COUNT(o.id).
• Testing the wrong column for the anti-join, for example WHERE o.status IS NULL, which also matches real orders with an unknown status. Test the right table's key.
• Putting a filter on the right table in WHERE, which removes the NULL rows and turns the LEFT JOIN back into an inner join. Topic 5 shows the fix: put it in ON.
• Writing FULL OUTER JOIN in MySQL and getting a syntax error. Use the UNION version.