SQL LEFT OUTER JOIN: NULL Handling
Most candidates think LEFT JOIN 'keeps all the data.' The interviewer is checking whether you know LEFT JOIN drops rows the moment you put the right-table filter in the WHERE clause instead of the ON clause. That one mistake is the single most common LEFT JOIN bug in phone screens.
LEFT JOIN Syntax
LEFT JOIN and LEFT OUTER JOIN are byte-for-byte identical to the parser. The OUTER keyword is cosmetic. Most teams drop it because five characters aren't worth the debate in code review. The real action is in the ON clause, which is where the interviewer is going to set a trap.
LEFT JOIN Syntax Reference
-- These two are identical
SELECT * FROM a LEFT JOIN b ON a.id = b.a_id;
SELECT * FROM a LEFT OUTER JOIN b ON a.id = b.a_id;
-- Full syntax
SELECT
left_table.column1,
right_table.column2
FROM left_table
LEFT JOIN right_table
ON left_table.key = right_table.key
[AND additional_conditions]
WHERE [post-join_filters];Interview note: The table listed first (after FROM) is the left table. The table after LEFT JOIN is the right table. Table order matters for LEFT JOIN but not for INNER JOIN. Swapping the tables in a LEFT JOIN changes the semantics entirely.
LEFT JOIN vs INNER JOIN
The difference only matters when unmatched rows exist. If every left-table row has at least one match, both join types return the same result.
| Behavior | LEFT JOIN | INNER JOIN |
|---|---|---|
| Unmatched left rows | Kept, with NULLs for right columns | Dropped entirely |
| Unmatched right rows | Dropped (use FULL OUTER JOIN to keep both) | Dropped |
| Result row count | At least as many rows as the left table (more if right has duplicates) | Only matching rows |
| NULL generation | Yes, for right-side columns where no match exists | No NULLs generated by the join itself |
| Use case | Preserve all entities even without related data | Only entities with matching data |
LEFT vs INNER Row Count Example
-- customers: Alice (id=1), Bob (id=2), Carol (id=3)
-- orders: order for Alice (customer_id=1), order for Bob (customer_id=2)
-- LEFT JOIN: 3 rows (Carol has NULLs for order columns)
SELECT c.name, o.order_id
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id;
-- Alice | 101
-- Bob | 102
-- Carol | NULL <-- preserved by LEFT JOIN
-- INNER JOIN: 2 rows (Carol dropped)
SELECT c.name, o.order_id
FROM customers c
INNER JOIN orders o ON c.customer_id = o.customer_id;
-- Alice | 101
-- Bob | 1025 LEFT JOIN Patterns
From basic entity preservation to the anti-join and the critical ON vs WHERE distinction. These patterns cover the full range of LEFT JOIN usage in interviews and production.
Basic LEFT JOIN
LEFT JOIN returns all rows from the left table. For rows that match the ON condition, columns from the right table are populated. For rows that do not match, right-side columns are filled with NULL. The keyword OUTER is optional: LEFT JOIN and LEFT OUTER JOIN are identical.
Anti-Join (LEFT JOIN + WHERE IS NULL)
The anti-join finds rows in the left table that have no match in the right table. LEFT JOIN produces NULLs for unmatched rows. Adding WHERE right_table.key IS NULL filters to only those unmatched rows. This is one of the most powerful and commonly tested patterns in SQL interviews.
LEFT JOIN with Aggregation
Combining LEFT JOIN with GROUP BY and aggregate functions is a common reporting pattern. The LEFT JOIN preserves all entities from the left table. The aggregation summarizes the related data. COALESCE handles the NULLs generated by unmatched rows, converting them to zeros or default values for clean reporting.
Multiple LEFT JOINs
You can chain multiple LEFT JOINs to bring in data from several related tables while preserving all rows from the leftmost table. Each LEFT JOIN independently adds columns. If any join has no match, those columns are NULL. This pattern builds wide denormalized views for analytics and reporting.
LEFT JOIN with WHERE vs ON Conditions
Conditions in the ON clause filter the right table before the join. Conditions in the WHERE clause filter the joined result after the join. For LEFT JOINs, moving a right-table filter from ON to WHERE can eliminate rows that should have been preserved, effectively turning the LEFT JOIN into an INNER JOIN.
Anti-Join Pattern
-- Customers who have never placed an order
SELECT c.customer_id, c.name
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
WHERE o.customer_id IS NULL;
-- Alternative: NOT EXISTS (often same performance)
SELECT c.customer_id, c.name
FROM customers c
WHERE NOT EXISTS (
SELECT 1 FROM orders o
WHERE o.customer_id = c.customer_id
);
-- Alternative: NOT IN (careful with NULLs)
SELECT customer_id, name
FROM customers
WHERE customer_id NOT IN (
SELECT customer_id FROM orders WHERE customer_id IS NOT NULL
);ON vs WHERE: Critical Distinction
-- CORRECT: filter in ON clause (preserves unmatched customers)
SELECT c.customer_id, c.name, o.order_id
FROM customers c
LEFT JOIN orders o
ON c.customer_id = o.customer_id
AND o.status = 'completed';
-- Customers with no completed orders: order_id = NULL (preserved)
-- WRONG: filter in WHERE clause (drops unmatched customers)
SELECT c.customer_id, c.name, o.order_id
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
WHERE o.status = 'completed';
-- Customers with no completed orders are DROPPED
-- because o.status is NULL for them, and NULL != 'completed'Common LEFT JOIN Mistakes
These three mistakes produce queries that run successfully but return incorrect results. Each one has cost real candidates real offers.
WHERE clause on right table nullifies the LEFT JOIN
Adding WHERE right_table.column = 'value' drops all rows where the right table has no match, because the column is NULL for those rows, and NULL does not equal anything. This silently converts the LEFT JOIN into an INNER JOIN. Fix: Move right-table filter conditions to the ON clause.
Using COUNT(*) instead of COUNT(column) after LEFT JOIN
COUNT(*) counts all rows, including those with NULLs from unmatched joins. If you LEFT JOIN customers to orders and GROUP BY customer, COUNT(*) returns 1 for customers with no orders. COUNT(order_id) returns 0, which is the correct count. Fix: Use COUNT(right_table.column) when counting related entities after a LEFT JOIN.
Not handling NULLs in calculations
LEFT JOINs generate NULLs. Any arithmetic with NULL produces NULL: salary + NULL = NULL. Any comparison with NULL returns UNKNOWN: NULL > 0 is not TRUE. Fix: Wrap right-side columns in COALESCE: COALESCE(o.amount, 0) for arithmetic. Use IS NULL / IS NOT NULL for comparisons.
4 LEFT JOIN Interview Questions
These questions test join mechanics, anti-join patterns, fan-out understanding, and the ON vs WHERE distinction.
What is the difference between LEFT JOIN and INNER JOIN? Give an example where they return different results.
INNER JOIN drops unmatched rows from both sides. LEFT JOIN keeps all left-table rows, filling NULLs for unmatched right-side columns. Example: 100 customers, 80 have orders. INNER JOIN returns rows for the 80 customers who have orders. LEFT JOIN returns rows for all 100 customers; the 20 without orders have NULL in the order columns.
Write a query to find all products that have never been ordered.
SELECT p.product_id, p.name FROM products p LEFT JOIN order_items oi ON p.product_id = oi.product_id WHERE oi.product_id IS NULL. The IS NULL check must be on the join key from the right table. NOT EXISTS is an alternative. Warn about NOT IN with NULLs: if the subquery returns any NULL, NOT IN returns empty results.
A LEFT JOIN query unexpectedly returns more rows than the left table. What happened?
The join key is not unique in the right table. Each order creates a separate joined row. Solutions: pre-aggregate the right table before joining, or use a subquery with ROW_NUMBER to pick one row per key, or use DISTINCT ON.
Explain why moving a WHERE condition on the right table to the ON clause changes the result of a LEFT JOIN.
The ON clause filters before the join: rows that do not meet the condition are excluded from the right table, so the left row appears with NULLs. The WHERE clause filters after the join: rows where the right-side column is NULL are filtered out entirely. This effectively converts the LEFT JOIN to an INNER JOIN for that condition.
LEFT JOIN FAQ
What is a LEFT OUTER JOIN in SQL?+
What is the difference between LEFT JOIN and INNER JOIN?+
What is an anti-join in SQL?+
Why does my LEFT JOIN return more rows than the left table?+
The ON vs WHERE Trap Is Still Free Real Estate for Interviewers
- 01
Active recall beats re-reading by 50%
Cognitive-science meta-reviews (Dunlosky et al., 2013) rank practice testing as a top-tier study technique, while re-reading and highlighting rank near the bottom
- 02
76% of hiring managers reject on the coding task, not the resume
From HackerRank's 2024 Developer Skills Report. Candidates who look strong on paper still fail the live screen if they haven't done timed, executable practice
- 03
Five problem shapes cover 80% of data engineer loops
Dedup, sessionization, top-N-per-group, slowly-changing dimensions, partition tricks. Writing the shapes by hand turns the unfamiliar into pattern recognition