Inner and Outer Joins

Where you put the filter matters

An INNER JOIN keeps only matching pairs; a LEFT JOIN keeps every left row and fills right columns with NULL when there is no match. A condition on the right table in WHERE removes those NULL rows, quietly turning the LEFT JOIN into an inner join. Put conditions that restrict which right rows match in the ON clause to keep unmatched left rows. FULL OUTER JOIN keeps unmatched rows from both sides; CROSS JOIN produces every combination.

Combine tables correctly

Joins are where most wrong numbers come from: lost rows, extra rows and silent filters.

The same filter in WHERE and in ON, run

I ran this with psql on PostgreSQL 16.2 against the course sample data (shown in the first section). With the filter in WHERE, John (no paid orders) disappears; with the filter in ON, John stays with a NULL order_id.

-- Filter in WHERE: turns the LEFT JOIN back into an inner join
SELECT c.name, o.id AS order_id
FROM customers c LEFT JOIN orders o ON o.customer_id = c.id
WHERE o.status = 'paid' ORDER BY c.name, o.id;

-- Filter in ON: keeps customers without paid orders
SELECT c.name, o.id AS order_id
FROM customers c LEFT JOIN orders o ON o.customer_id = c.id AND o.status = 'paid'
ORDER BY c.name, o.id;

Output:

 name  | order_id 
-------+----------
 Asha  |      101
 Asha  |      102
 Asha  |      106
 Meera |      105
 Ravi  |      104
(5 rows)

 name  | order_id 
-------+----------
 Asha  |      101
 Asha  |      102
 Asha  |      106
 John  |         
 Meera |      105
 Ravi  |      104
(6 rows)

Count rows before and after joins

Comparing row counts quickly reveals lost rows (filters) or extra rows (fan-out).

Quick check: What happens when you filter a LEFT JOIN's right table in WHERE?

• Nothing changes
• Unmatched left rows are removed, like an inner join
• All rows are duplicated
• The query fails