What is a LEFT JOIN?
SQLLevel: FoundationJOINs Basics
What is a LEFT JOIN?
Hints
- Write the join keys and relationship cardinality.
- What happens to duplicates and unmatched rows? Check WHERE versus ON.
Answer
A LEFT JOIN returns all rows from the left table, and matching rows from the right table. Where there's no match, the right table's columns are NULL.
-- All customers, even those with no orders
SELECT c.customer_id, c.name, o.order_id
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id;
"Left" means the table on the left side of the JOIN keyword is fully preserved.
Common follow-up: "How do you find customers with NO orders?" — filter for NULL on the right side:
SELECT c.customer_id, c.name
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
WHERE o.order_id IS NULL;
Source and licence: SQL Interview Questions — Arda Kocadoru · MIT