LEFT & RIGHT JOIN
Overview
Unlike the destructive INNER JOIN, Outer Joins are designed to protect data. A LEFT JOIN grabs EVERYTHING from the 'Left' table (the table written first, right after FROM), and attempts to find matches in the 'Right' table. If it finds a match, it attaches the data. If it fails to find a match, it still keeps the Left row, and simply fills the missing Right data with NULLs. This is how you find 'Users who have NEVER placed an order'.
Syntax
-- 1. LEFT JOIN (Preserves the 'users' table!)
-- All users are returned. If they have no orders, order_id will be NULL.
SELECT u.username, o.order_id
FROM users u
LEFT JOIN orders o
ON u.id = o.user_id;
-- 2. RIGHT JOIN (Preserves the 'orders' table!)
-- Identical logic, but prioritizes the table written after the JOIN keyword.
-- (Rarely used in production, developers just rewrite the query as a LEFT JOIN).
SELECT u.username, o.order_id
FROM users u
RIGHT JOIN orders o
ON u.id = o.user_id;Common Pitfalls
- Accidentally destroying a
LEFT JOINby placing a strict filter in theWHEREclause. If youLEFT JOIN orders(creating NULLs for users with no orders), and then writeWHERE orders.status = 'shipped', you instantly delete all the NULL rows! TheWHEREclause executes after the join, forcing theLEFT JOINto behave exactly like a destructiveINNER JOIN. To fix this, move the filter into theONclause:ON u.id = o.user_id AND o.status = 'shipped'. - Assuming
LEFT JOINprevents duplicate fan-out. It doesn't! If one User on the Left matches 50 Orders on the Right, that User row will be duplicated 50 times in the output to accommodate the data.
Interview Questions
LEFT JOIN to find records that absolutely DO NOT have a match in the second table?This is called a 'Left Anti-Join'. You perform a standard LEFT JOIN, and then explicitly filter for the generated NULLs in the WHERE clause: WHERE table_b.id IS NULL.
Real-World Example
Generating a complete roster of all Departments, including those that currently have zero employees.
SELECT
d.department_name,
-- COUNT() safely ignores the NULLs generated by the LEFT JOIN!
COUNT(e.emp_id) AS total_employees
FROM departments d
LEFT JOIN employees e
ON d.id = e.department_id
GROUP BY d.department_name;Check Your Knowledge
Test your understanding of LEFT & RIGHT JOIN with these quick questions.