SQL joins, finally visualized

Joins are where SQL clicks or stays confusing for years. The Venn-diagram pictures help a little, but what really makes it stick is seeing what each join does to the actual rows. Let's use two simple tables: users and orders.

INNER JOIN — only matches

Returns rows that have a match in BOTH tables. Users with no orders are dropped.

inner.sql

sql

SELECT u.name, o.total
FROM users u
INNER JOIN orders o ON o.user_id = u.id;
-- only users who have placed at least one order

LEFT JOIN — keep everything on the left

Returns every row from the left table (users), with matching order rows where they exist — and NULLs where they don't.

left.sql

sql

SELECT u.name, o.total
FROM users u
LEFT JOIN orders o ON o.user_id = u.id;
-- ALL users; o.total is NULL for users with no orders

LEFT JOIN + IS NULL is the classic way to find missing rows: 'which users have NEVER ordered?' Join, then keep only the rows where the right side came back NULL.

find-missing.sql

sql

SELECT u.name
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
WHERE o.id IS NULL;   -- users with zero orders

RIGHT and FULL

RIGHT JOIN — keeps every row on the right instead of the left. Rarely used; you can always rewrite it as a LEFT JOIN by swapping the tables.
FULL OUTER JOIN — keeps everything from both sides, NULLs where either lacks a match. Useful for reconciliation ('what's in one table but not the other').

The NULL gotcha

Filtering a LEFT JOIN in the WHERE clause on the right table's column silently turns it back into an INNER JOIN (NULLs fail the condition). If you need to filter the right side but keep unmatched left rows, put the condition in the ON clause instead.

Think in rows, not diagrams: INNER keeps matches, LEFT keeps all of the left, and LEFT JOIN ... IS NULL finds what's missing. Those three cover almost every query you'll write.