Reference Tables Used in This Section
Every join note below uses these same two tables, so you can see exactly how each join type treats the same data differently.
employees
| id | name | department_id |
|---|---|---|
| 1 | Aman | 1 |
| 2 | Riya | 2 |
| 3 | Karan | 1 |
| 4 | Neha | NULL |
departments
| id | department_name |
|---|---|
| 1 | Engineering |
| 2 | Marketing |
| 3 | Sales |
Notice: Neha has no department (department_id is NULL), and Sales has no employees at all. These two "unmatched" rows are what make the differences between join types visible.
The Core Difference
| INNER JOIN | LEFT JOIN | |
|---|---|---|
| Returns | Only matched rows | All rows from the left table, matched or not |
| Unmatched left rows | Excluded | Included, with NULLs for the right table's columns |
| Row count vs left table | Less than or equal to | Always greater than or equal to |
Side-by-Side Example
-- INNER JOIN
SELECT e.name, d.department_name
FROM employees e
INNER JOIN departments d ON e.department_id = d.id;
-- 3 rows — Neha excluded (no department)
-- LEFT JOIN
SELECT e.name, d.department_name
FROM employees e
LEFT JOIN departments d ON e.department_id = d.id;
-- 4 rows — Neha included, with NULL department_nameHow to Decide Which One You Need
- Ask: "Do rows without a match still matter for this report?"
- If no — an employee with no department is irrelevant to what you're building — use
INNER JOIN. - If yes — you need to see every employee regardless, or specifically want to find the ones without a match — use
LEFT JOIN.
Practical Use Case
A payroll report that should only include employees with a valid, assigned department: INNER JOIN. An HR audit report meant to catch employees missing a department assignment: LEFT JOIN with WHERE department_id IS NULL.
Common Mistake
Defaulting to INNER JOIN out of habit and silently dropping rows that a report actually needed to include — a very common, hard-to-notice bug, since the query runs fine and just returns fewer rows than expected with no error.
Interview Relevance
This comparison is asked constantly, often phrased as a scenario rather than a direct definition question — e.g. "Why does my report have fewer employees than the total employee count?" (Answer: an INNER JOIN is silently dropping unmatched rows.)
Practice Question
A report using INNER JOIN between employees and departments shows 3 rows, but the company has 4 employees. Explain why, and write the fix.
