LEFT JOIN
LEFT JOIN returns ALL rows from the left table, plus matching rows from the right table. If no match exists in the right table, NULL fills those columns. This is the go-to join for 'find all X and their optional Y' queries.
LEFT JOIN Key Points
- ALL rows from left table - always
- Matching rows from right table - when available
- NULL for right-table columns when no match
- Classic use: find rows with no related record
- LEFT JOIN + WHERE right.id IS NULL = anti-join (rows with NO match)
LEFT JOIN Examples
-- All users and their orders (including users with NO orders)
SELECT
u.name AS customer,
o.id AS order_id,
o.status,
o.total_amount
FROM users u
LEFT JOIN orders o ON u.id = o.user_id;
-- Result (Bob Lee appears with NULLs):
-- customer | order_id | status | total_amount
-- John Doe | 1 | delivered | 999.99
-- John Doe | 3 | shipped | 89.99
-- Alice Smith | 2 | pending | 599.99
-- Bob Lee | NULL | NULL | NULL ← no orders
-- Anti-join: find users who NEVER placed an order
SELECT u.name, u.email
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
WHERE o.id IS NULL;
-- Result: Bob Lee
-- All orders + payment info (including unpaid orders)
SELECT
o.id AS order_id,
o.status,
COALESCE(p.payment_method, 'Not Paid') AS payment
FROM orders o
LEFT JOIN payments p ON o.id = p.order_id;Quick Quiz
Tip
Tip
Practice LEFT JOIN in small, isolated examples before integrating into larger projects. Breaking concepts into small experiments builds genuine understanding faster than reading alone.
Common Mistake
Warning
A common mistake with LEFT JOIN is skipping edge case testing - empty inputs, null values, and unexpected data types. Always validate boundary conditions to write robust, production-ready sql code.
Practice Task
Note
Practice Task - (1) Write a working example of LEFT JOIN from scratch without looking at notes. (2) Modify it to handle an edge case (empty input, null value, or error state). (3) Share your solution in the Priygop community for feedback.
Key Takeaways
- LEFT JOIN returns ALL rows from the left table, plus matching rows from the right table.
- ALL rows from left table - always
- Matching rows from right table - when available
- NULL for right-table columns when no match