LEFT JOIN
LEFT JOIN keeps every row from the left table and fills the right side with NULL when there is no match.
In this page:
Syntax
SELECT columns
FROM table1
LEFT JOIN table2 ON table1.key = table2.key;LEFT JOIN
Use it to list all customers even if they have no orders. Filtering on a right-table column in WHERE turns it back into an inner join, so put such conditions in ON.
A common trick is LEFT JOIN plus WHERE right.id IS NULL to find rows without a partner.
Note:
LEFT JOIN with IS NULL finds rows that have no match.
Example: LEFT JOIN
CREATE TABLE customers (id INTEGER PRIMARY KEY, name TEXT);
CREATE TABLE orders (id INTEGER PRIMARY KEY, customer_id INTEGER, total INTEGER);
INSERT INTO customers VALUES (1, 'Ada'), (2, 'Bob'), (3, 'Cy');
INSERT INTO orders VALUES (10, 1, 50), (11, 1, 20), (12, 2, 75);
SELECT c.name, o.total FROM customers c LEFT JOIN orders o ON o.customer_id = c.id ORDER BY c.id, o.id;
SELECT c.name AS never_ordered FROM customers c LEFT JOIN orders o ON o.customer_id = c.id WHERE o.id IS NULL;
-- Output:
-- name | total
-- Ada | 50
-- Ada | 20
-- Bob | 75
-- Cy | NULL
-- never_ordered
-- CyLogin to try C/C++/Java/PHP code in the editor
Related Topics
Common Mistakes
- Filtering the right table in WHERE
- Assuming NULLs mean the join failed
- Confusing left and right tables
Chapter Summary
- LEFT JOIN keeps all left rows
- Unmatched right columns are NULL
- Filter right tables in ON
- IS NULL finds unmatched rows
🔒
Chapter Quiz — Complete all 7 topics to unlock
0/7 topics done
Complete these topics first: