SQL LEFT JOIN

LEFT JOIN keeps every row of the left table. Where the right table has no match, its columns come back as NULL.

pick a join type

customers
id	name
1	Ada
2	Grace
3	Linus
4	Hedy

orders
id	customer_id
101	1
102	2
103	1
108	9

SELECT c.name, o.id, o.total
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id;

Every customer, matched or not. Customers without orders get NULL in the order columns. Order 108 still drops out because it is on the right.

Result · 5 rows
name	order id	total
Ada	101	131
Ada	103	9.5
Grace	102	34.5
Linus	NULL	NULL
Hedy	NULL	NULL

Run this on the full sample database

LEFT JOIN example, row by row

Linus and Hedy have no orders, so they appear once with NULL order columns. Order 108 (customer 9) is dropped because it is only on the right.

SELECT c.name, o.id, o.total
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id;

▶ Run it on the full sample database

What to know about LEFT JOIN

LEFT JOIN and LEFT OUTER JOIN are the same thing; OUTER is optional in every major database.
A condition on the right table belongs in ON, not WHERE. WHERE o.total > 50 removes the NULL rows and quietly turns the query into an INNER JOIN.
Add WHERE r.key IS NULL to keep only left rows with no match (an anti-join: "customers who never ordered").
If one left row matches several right rows, it appears once per match, so counts can grow. Aggregate with GROUP BY if you need one row each.

Other join types

INNER JOIN — only rows that match in both tables
RIGHT JOIN — all right rows + matching left rows (null when unmatched)
FULL OUTER JOIN — every row from both tables
All SQL joins compared, including anti-joins, CROSS JOIN and self joins

Syntax for everything else, from GROUP BY to window functions, is on the SQL cheat sheet.

LEFT JOIN questions

What is a LEFT JOIN in SQL?

A LEFT JOIN returns all rows from the left (first) table and the matching rows from the right table. When a left row has no match, the right table’s columns are filled with NULL.

What is the difference between LEFT JOIN and INNER JOIN?

INNER JOIN keeps only rows that match in both tables. LEFT JOIN also keeps left rows with no match, padding the right side with NULL. With the sample data above, INNER JOIN returns 3 rows and LEFT JOIN returns 5.

What is the difference between a LEFT JOIN and a RIGHT JOIN?

They mirror each other. LEFT JOIN keeps every row of the first table, RIGHT JOIN every row of the second. a LEFT JOIN b gives the same rows as b RIGHT JOIN a, which is why most people only write LEFT JOINs.

What is the difference between a LEFT JOIN and a FULL JOIN?

LEFT JOIN keeps unmatched rows from the left table only. FULL OUTER JOIN keeps unmatched rows from both tables, so you also see right rows with no partner (order 108 in the example).

Why does my LEFT JOIN return more rows than the left table?

Because a left row that matches several right rows appears once per match. Ada has two orders, so she appears twice. Join to a pre-aggregated subquery or use GROUP BY to get one row per left row.

Why does my LEFT JOIN act like an INNER JOIN?

Almost always because a WHERE condition references the right table. NULL fails any comparison, so the unmatched rows are filtered out. Move that condition into the ON clause, or add OR r.col IS NULL.