INNER JOIN vs LEFT JOIN in SQL
INNER JOIN vs LEFT JOIN in SQL: see how each handles unmatched rows, when to use LEFT JOIN with NULLs, and the anti-join pattern, with clear examples.
Expected Interview Answer
An INNER JOIN returns only rows that have matching values in both tables, while a LEFT JOIN returns all rows from the left table plus matching rows from the right, filling unmatched right-side columns with NULL.
Both joins combine tables on a join condition, but they differ in how they treat rows without a match. INNER JOIN keeps a row only when the condition is satisfied in both tables, so unmatched rows from either side disappear. LEFT JOIN (LEFT OUTER JOIN) preserves every row from the left table regardless of a match; where the right table has no match, its columns come back NULL. Choosing wrongly silently drops or invents rows, so the difference matters for correctness.
- INNER JOIN returns only matched, complete pairs
- LEFT JOIN preserves all left-table rows
- LEFT JOIN surfaces missing relationships via NULLs
- Enables finding unmatched rows with a NULL check
- Both support multi-column and multi-table joins
AI Mentor Explanation
Imagine one list of players and another of centuries scored. An INNER JOIN pairs only players who actually scored a century, dropping everyone else. A LEFT JOIN keeps every player on the roster and shows their centuries, marking those with none as blank — so you still see the players who never reached a hundred rather than losing them from the report entirely.
Step-by-Step Explanation
Step 1
Pick the driving table
Decide which table's rows must all be preserved — that becomes the left table.
Step 2
Define the join condition
Match rows with ON, usually a foreign-key to primary-key equality.
Step 3
Choose the join type
Use INNER for matched-only pairs; LEFT to keep every left row regardless of match.
Step 4
Handle NULLs from the right
Unmatched right-side columns are NULL in a LEFT JOIN; account for them in SELECT and WHERE.
Step 5
Find unmatched rows
Add WHERE right.key IS NULL to a LEFT JOIN to list left rows with no match.
What Interviewer Expects
- Clear definition of both join types
- Knows LEFT JOIN keeps all left rows with NULLs on the right
- Can explain the anti-join pattern (IS NULL)
- Understands filtering right-table columns in WHERE turns LEFT into INNER
- Gives a correct, concrete example
Common Mistakes
- Believing LEFT JOIN and INNER JOIN return the same rows
- Putting a right-table filter in WHERE, silently converting LEFT to INNER
- Forgetting unmatched right columns are NULL
- Confusing LEFT JOIN with RIGHT JOIN direction
- Using INNER JOIN when unmatched left rows must be reported
Best Answer (HR Friendly)
“An INNER JOIN only returns records that have a match in both tables, while a LEFT JOIN returns everything from the first table and fills in blanks where the second table has no match. LEFT JOIN is what you use when you don't want to lose rows that lack a partner.”
Code Example
SELECT c.name, o.order_id, o.total
FROM customers c
INNER JOIN orders o ON o.customer_id = c.id;SELECT c.name, o.order_id
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id;
-- Customers who have never ordered
SELECT c.name
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
WHERE o.order_id IS NULL;Follow-up Questions
- How do you find rows in the left table with no match in the right?
- Why can a WHERE filter turn a LEFT JOIN into an INNER JOIN?
- What is the difference between LEFT JOIN and RIGHT JOIN?
- What does a FULL OUTER JOIN return?
- How do NULLs from a LEFT JOIN affect aggregate functions like COUNT?
MCQ Practice
1. A LEFT JOIN returns:
LEFT JOIN keeps every row from the left table and fills unmatched right columns with NULL.
2. Which pattern finds left rows with no matching right row?
A LEFT JOIN plus WHERE on the right key IS NULL is the classic anti-join for unmatched rows.
3. Filtering a right-table column in WHERE on a LEFT JOIN usually:
NULLs from unmatched rows fail the WHERE predicate, effectively dropping them like an INNER JOIN.
Flash Cards
INNER JOIN returns? — Only rows with a match in both tables.
LEFT JOIN returns? — All left-table rows, plus matching right rows; unmatched right columns are NULL.
How to find unmatched left rows? — LEFT JOIN then WHERE right.key IS NULL (anti-join).
Why can a right-table WHERE break a LEFT JOIN? — NULLs from unmatched rows fail the predicate, converting it to an INNER JOIN.