LEFT & RIGHT OUTER JOIN
BeginnerLEFT JOIN returns all rows from the left table and matching rows from the right; unmatched right-side columns are NULL — RIGHT JOIN is the mirror image.
Overview
LEFT OUTER JOIN (usually written as LEFT JOIN) is one of the most used joins in reporting: it shows all records from one table regardless of whether a matching record exists in another. The keyword OUTER is optional. Unmatched rows from the right table produce NULL in all of that table's projected columns. A common mistake is to add a WHERE condition on a right-table column that filters out NULL rows, effectively converting the LEFT JOIN into an INNER JOIN. RIGHT JOIN is rarely used because any RIGHT JOIN can be rewritten as a LEFT JOIN by swapping the table order — prefer LEFT JOIN for readability. Moving filters on the right table into the ON clause preserves the outer join semantics.
LEFT JOIN Basics and the NULL Filter Trap
Use LEFT JOIN when you need to find "all A with or without B" — typical for optional relationships. The critical trap: filtering on a right-table column in WHERE silently converts the LEFT JOIN to an INNER JOIN.
-- LEFT JOIN: all users and their order count (0 if no orders)
SELECT
u.id,
u.email,
COUNT(o.id) AS order_count
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
GROUP BY u.id, u.email;
-- Trap: WHERE on right-table column kills the LEFT JOIN!
-- This returns only users WHO HAVE orders (= INNER JOIN behavior)
SELECT u.id, u.email, o.total_amount
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
WHERE o.status = 'pending'; -- NULL rows filtered out → accidental INNER JOIN
-- Fix: move the filter into the ON clause
SELECT u.id, u.email, o.total_amount
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
AND o.status = 'pending'; -- users with no pending orders still appear (o cols = NULL)
-- Find users who have NEVER placed an order (anti-join using LEFT JOIN)
SELECT u.id, u.email
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
WHERE o.id IS NULL; -- o.id is NULL only for unmatched usersRIGHT JOIN and Rewriting to LEFT JOIN
Any RIGHT JOIN can be rewritten as a LEFT JOIN by swapping the table order. Sticking to LEFT JOIN throughout a codebase improves readability and consistency.
-- RIGHT JOIN: all departments with employee count (0 if no employees)
SELECT
d.name AS department,
COUNT(e.id) AS employee_count
FROM employees e
RIGHT JOIN departments d ON d.id = e.department_id
GROUP BY d.id, d.name;
-- Equivalent LEFT JOIN (preferred style):
SELECT
d.name AS department,
COUNT(e.id) AS employee_count
FROM departments d
LEFT JOIN employees e ON e.department_id = d.id
GROUP BY d.id, d.name;
-- Chained LEFT JOINs: users → orders → shipments (all users, even those without orders/shipments)
SELECT
u.email,
o.id AS order_id,
s.tracking_no
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
LEFT JOIN shipments s ON s.order_id = o.id
WHERE u.status = 'active';Key Points to Remember
- 1LEFT JOIN: all rows from left table; NULL for unmatched right-table columns.
- 2Filter on right-table column in WHERE converts LEFT JOIN to INNER JOIN — put it in ON instead.
- 3Use LEFT JOIN + WHERE right_col IS NULL to find rows with no matching child (anti-join).
- 4Any RIGHT JOIN can be written as a LEFT JOIN by swapping tables — prefer LEFT JOIN.
- 5Chained LEFT JOINs preserve "parent rows without children" through the full chain.
- 6In JPA, LEFT JOIN FETCH loads optional associations without discarding parent entities.
Interview Questions
Sign in to ask AriaHow does adding a WHERE clause on a right-table column affect a LEFT JOIN?
Write a query to find all customers who have never placed an order.
What is the difference between filtering in ON vs WHERE for a LEFT JOIN?
Why is RIGHT JOIN rarely used in practice?
How would you count orders per user including users with zero orders?
Ask Aria about LEFT & RIGHT OUTER JOIN
Your personal AI tutor — ask anything about this concept
Revision Status
Personal Notes
Sign in to save personal notes for this topic.
Discussion
Sign in to join the discussion.