Inner Join vs Outer Join
Learn the difference between INNER and OUTER JOINs in SQL with examples, NULL handling, and common interview mistakes to avoid.
Expected Interview Answer
An INNER JOIN returns only the rows that have matching values in both tables, while an OUTER JOIN (LEFT, RIGHT, or FULL) returns matched rows plus unmatched rows from one or both sides, filling the missing side with NULLs.
INNER JOIN is the strictest form: a row survives only if the join condition finds a partner in the other table, so rows without a match on either side are dropped entirely. LEFT OUTER JOIN keeps every row from the left table regardless of a match, RIGHT OUTER JOIN keeps every row from the right table, and FULL OUTER JOIN keeps every row from both, using NULLs where no counterpart exists. Choosing between them depends on whether missing relationships are meaningful data you need to see or noise you want filtered out.
- INNER JOIN keeps result sets small and matched-only
- LEFT/RIGHT JOIN preserves records with no counterpart
- FULL OUTER JOIN surfaces gaps on both sides
- Choosing correctly avoids silently dropped rows
AI Mentor Explanation
An INNER JOIN is like a team sheet that lists only players who both trained this week and played in the match — anyone who trained but did not play, or played without training records, is left off. A LEFT OUTER JOIN instead starts from every player who trained, and shows a blank match entry for those who did not play. FULL OUTER JOIN would list every trainee and every match participant, blanks on whichever side has no counterpart. The choice changes whether missing data disappears or shows up as an empty slot.
Step-by-Step Explanation
Step 1
Identify the base table
Decide whether missing matches from the left, right, or both tables must still appear in results.
Step 2
Pick the join type
Use INNER for matched-only, LEFT/RIGHT to preserve one side, FULL to preserve both.
Step 3
Write the join condition
Specify the ON clause linking the foreign key to the primary key.
Step 4
Handle NULLs
Account for NULLs in unmatched columns with COALESCE or IS NULL checks downstream.
What Interviewer Expects
- Clear distinction between matched-only and preserved-side semantics
- Awareness that LEFT/RIGHT/FULL are all OUTER join variants
- Understanding of how NULLs appear in unmatched columns
- A concrete example showing dropped vs preserved rows
Common Mistakes
- Thinking OUTER JOIN is a single distinct join type separate from LEFT/RIGHT
- Forgetting that INNER JOIN silently drops unmatched rows
- Confusing LEFT and RIGHT direction relative to the FROM clause
- Not handling NULLs produced by outer joins in later filters
Best Answer (HR Friendly)
“An inner join gives me only the rows that match in both tables, while an outer join lets me keep rows from one or both tables even when there is no match, filling the gaps with NULLs. I pick inner joins when I only care about complete matched data, and outer joins when missing relationships are themselves meaningful information.”
Code Example
SELECT o.OrderID, c.CustomerName
FROM Orders o
INNER JOIN Customers c ON o.CustomerID = c.CustomerID;
SELECT c.CustomerName, o.OrderID
FROM Customers c
LEFT OUTER JOIN Orders o ON c.CustomerID = o.CustomerID;Follow-up Questions
- What is the difference between LEFT JOIN and RIGHT JOIN?
- How does a FULL OUTER JOIN behave when neither table has a match?
- What is a CROSS JOIN and how does it differ from these?
- How do NULLs from an outer join affect WHERE clause filtering?
MCQ Practice
1. Which join returns only rows with matches in both tables?
INNER JOIN returns only rows where the join condition matches in both tables.
2. A LEFT OUTER JOIN preserves all rows from which table?
LEFT OUTER JOIN keeps every row from the left (FROM) table, with NULLs for unmatched right-side columns.
3. What value appears for unmatched columns in an OUTER JOIN?
SQL fills unmatched columns from an outer join with NULL.
Flash Cards
INNER JOIN — Returns only rows with matches in both tables.
LEFT OUTER JOIN — Keeps all left table rows, NULLs where no right match exists.
FULL OUTER JOIN — Keeps all rows from both tables, NULLs on whichever side lacks a match.
Unmatched OUTER JOIN column — Filled with NULL.