✏️ Explanatory Question
Level: Intermediate — Tests whether you truly understand how matched vs unmatched rows are handled.
The core difference lies in how they treat rows that have no match in the other table.
LEFT JOIN = LEFT OUTER JOIN.
| Feature | INNER JOIN | OUTER JOIN |
|---|---|---|
| Returns | Only matching rows | Matching + unmatched rows |
| Unmatched rows | Excluded | Included with NULLs |
| NULLs in result | No (from the join) | Yes (for missing matches) |
| Row count | Smaller / equal | Larger / equal |
| Sub-types | Just one | LEFT, RIGHT, FULL |
Imagine employees where one employee has no department, and departments where one department has no employees:
-- INNER JOIN: only employees that have a matching department
SELECT e.name, d.dept_name
FROM employees e
INNER JOIN departments d ON e.dept_id = d.dept_id;
-- LEFT OUTER JOIN: all employees, dept_name is NULL if unmatched
SELECT e.name, d.dept_name
FROM employees e
LEFT JOIN departments d ON e.dept_id = d.dept_id;
-- Practical use: find employees WITHOUT a department
SELECT e.name
FROM employees e
LEFT JOIN departments d ON e.dept_id = d.dept_id
WHERE d.dept_id IS NULL;