LEFT OUTER JOIN keeps all rows from the left table even when there is no match on the right—essential for “customers with zero orders” reports.
LEFT JOIN
SELECT c.name,
o.id AS order_id,
o.total
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id;Practice: Seed customers and orders sample data, then run each query. Verify row counts manually.
Customers without orders appear with NULL order columns.
Finding orphans
SELECT c.name
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
WHERE o.id IS NULL;Classic pattern: left join then filter where right-side key IS NULL.
RIGHT and FULL
-- RIGHT JOIN: all rows from right table
SELECT *
FROM orders o
RIGHT JOIN customers c ON c.id = o.customer_id;
-- FULL OUTER: both sides preserved (Postgres; SQLite lacks FULL)RIGHT JOIN is equivalent to swapped LEFT JOIN. FULL OUTER support varies—check dialect tracks.
Important interview questions and answers
- Q: LEFT vs INNER?
A: LEFT keeps unmatched left rows; INNER drops them. - Q: NULL in outer join?
A: Missing matches fill with NULL—do not use = NULL; use IS NULL.
Self-check
- How do you list customers who never placed an order?
- What appears in order columns for customers without orders in LEFT JOIN?
Tip: LEFT JOIN + WHERE right.id IS NULL finds orphans on the left table.
Interview prep
- LEFT JOIN keeps?
All left rows; NULLs fill unmatched right columns.
- Find customers with no orders?
LEFT JOIN orders, WHERE orders.id IS NULL.