SQL Fundamentals: Joins, NULL, Aggregate Functions, and Subqueries
Contents — Part 2 of 18: Join Types and What Each One Keeps
- 1.Overview
- 2.Join Types and What Each One Keeps
- 3.Self Joins and Non-Equi Joins
- 4.Cross Joins and the Cartesian Product
- 5.NULL Is Not Zero, Not Empty String, and Not False
- 6.Three-Valued Logic: TRUE, FALSE, and UNKNOWN
- 7.NULL in Joins, Aggregates, GROUP BY, and Sorting
- 8.COALESCE, NULLIF, and Handling NULL in Expressions
- 9.GROUP BY and Aggregate Functions in Depth
- 10.HAVING vs. WHERE and Query Execution Order
- 11.Subqueries: Scalar, Column, and Table
- 12.Correlated Subqueries
- 13.EXISTS vs. IN vs. JOIN
- 14.Set Operations: UNION, INTERSECT, EXCEPT
- 15.Window Functions for Ranking and Running Totals
- 16.Common Table Expressions (CTEs)
- 17.Transactions and Why They Matter Here
- 18.Common Mistakes Worth Remembering
Join Types and What Each One Keeps
A join combines rows from two tables based on a condition. The join types differ only in what happens to rows on one side that have no match on the other:
| Join | Keeps |
|---|---|
INNER JOIN | Only rows with a match on both sides |
LEFT JOIN | Every row from the left table, with NULLs filled in where there's no match on the right |
RIGHT JOIN | Every row from the right table, with NULLs filled in where there's no match on the left (rarely used in practice — usually written as a LEFT JOIN with the tables swapped instead, since it reads more naturally) |
FULL OUTER JOIN | Every row from both tables, matched where possible, NULL-filled where not |
Finance has zero employees, which makes it the perfect test case for whether a join preserves unmatched rows:
-- INNER JOIN: Finance disappears entirely — no match, no row
SELECT d.dept, e.name
FROM Departments d
INNER JOIN Employees e ON d.dept = e.dept;
-- LEFT JOIN: Finance still appears, with NULL for the employee columns
SELECT d.dept, e.name
FROM Departments d
LEFT JOIN Employees e ON d.dept = e.dept;
-- Finance | NULL
Combined with GROUP BY and COUNT(), this is exactly how you'd correctly report "every department, including ones with zero employees" — a report that an INNER JOIN would silently get wrong by dropping Finance instead of showing it with a zero:
SELECT d.dept, COUNT(e.id) AS employee_count
FROM Departments d
LEFT JOIN Employees e ON d.dept = e.dept
GROUP BY d.dept;
-- Finance correctly shows 0, not a missing row
That last point deserves its own callout, because it's a classic interview trap: COUNT(e.id) (a specific, nullable column) counts 0 for Finance, but COUNT(*) in the same query would count 1 for Finance — because the LEFT JOIN still produces one output row for Finance, just with every Employees column NULL, and COUNT(*) counts rows regardless of what's in them. Always aggregate a column from the joined (nullable) side, never *, when counting across a LEFT JOIN.
FULL OUTER JOIN, and how to fake it where it isn't supported
SELECT d.dept, e.name
FROM Departments d
FULL OUTER JOIN Employees e ON d.dept = e.dept;
This returns everything a LEFT JOIN would, plus any employee rows that had no matching department (there are none in this data, since every Employees.dept value happens to also exist in Departments, but if one didn't, this is where it would surface). Some engines (notably older MySQL versions) don't support FULL OUTER JOIN directly — the standard workaround is a LEFT JOIN UNION a RIGHT JOIN (or a UNION of two LEFT JOINs with the tables swapped), deduplicated by UNION's default behavior.