âš¡IntermediateJoins
LEFT JOIN (LEFT OUTER JOIN)
Return all rows from the left table, with NULLs where no match exists in the right table.
Overview
LEFT JOIN returns all rows from the left (first) table, combined with matching rows from the right table. When there is no match in the right table, NULL is returned for all right-table columns. This makes LEFT JOIN ideal for "find all X, even if they have no Y" queries all customers even if they have no orders, all products even if no one has ordered them.
The ORDER of tables matters: the "left" table is the one you write before LEFT JOIN. Its rows are always included. Use RIGHT JOIN to make the other table the always-included one (though it's more readable to just swap the tables and use LEFT JOIN).
Key Points
- •Returns ALL rows from left table + matching rows from right
- •Right-table columns are NULL when no match exists
- •Find rows with NO match: WHERE right_table.id IS NULL
- •"Anti-join" pattern: find customers with NO orders
- •Order of tables matters: the "left" table is always in the result
Examples
All customers with their order counts (including customers with 0 orders)
SELECT c.name, COUNT(o.id) AS order_count
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
GROUP BY c.name
ORDER BY order_count DESC;Anti-join: customers with NO orders
SELECT c.id, c.name
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
WHERE o.id IS NULL;The WHERE o.id IS NULL filters to keep only rows where no matching order was found.
Exercises
Write SQL to solve each exercise. Your progress is saved automatically.
Loading editor…
Completed: 0 / 5 exercises
Knowledge Check
Question 1 of 4