Real join conditions are rarely a single equality
The joins so far linked two tables with one condition — an id equals an id. Real questions bundle more into the same join: "Engineering staff earning over 700000", "Oslo projects that are active". You can express that as a join and a filter, or you can fold both into the ON clause as a compound condition.
At the same time, the data is full of NULLs, and NULLs refuse to behave like values. Omar has no department; the Research department has no city. Once NULLs sit on either side of a join key or a condition, matching breaks in ways that are silent rather than loud. This lesson pairs the two ideas, because compound conditions are exactly where NULL behaviour starts to bite.
flowchart LR
R["a row of employees"] --> C1{"department_id = d.id?"}
C1 -->|yes| C2{"salary over 700000?"}
C2 -->|yes| K["paired with department"]
C1 -->|no| D["not paired"]
C2 -->|no| D
style K fill:#0e7490,color:#fff
style D fill:#b45309,color:#fff
ON accepts AND and OR just like WHERE
The ON clause is a condition like any other, so it takes AND, OR, and parentheses exactly as WHERE does. Each conjunct narrows which rows count as a pair.
SELECT e.name, d.name
FROM employees e
JOIN departments d
ON e.department_id = d.id AND e.salary > 700000;
For an INNER JOIN, putting a filter in the ON versus the WHERE makes no difference to the rows returned — both run after the join is built. The distinction only starts to matter with LEFT JOIN, where a filter stranded in WHERE can quietly undo the outer join. Until then, treat ON as the natural home for conditions that mention both tables.
SELECT e.name, d.name FROM employees e JOIN departments d ON e.department_id = d.id AND e.salary > 700000 ORDER BY e.salary DESC;
A NULL in the join key matches nothing
Equality has a blind spot: comparing anything to NULL gives unknown, and unknown is never true. So a row whose join key is NULL cannot match any row on the other side, no matter what sits there. Omar's department_id is NULL, which means every inner join of employees to departments drops him without comment.
This is the silent failure mode of joins. A mistyped column usually throws an error you can chase; a NULL in a key simply removes rows from the result, and the only clue is a count that looks one short. Whenever a row you expected is missing from a join, check first whether one of its keys is NULL.
flowchart LR
O["Omar<br/>department_id is NULL"] --> M{"NULL = d.id?"}
M -->|"unknown, never true"| D["no department matched"]
A["Ada<br/>department_id = 1"] --> M2{"1 = d.id?"}
M2 -->|"true for dept 1"| K["Engineering attached"]
style D fill:#b45309,color:#fff
style K fill:#0e7490,color:#fff
SELECT e.name AS employee, d.name AS department, d.city FROM employees e JOIN departments d ON e.department_id = d.id WHERE d.city IS NULL;
Two kinds of NULL in a result
A NULL you see in a joined result has two possible causes, and debugging depends on knowing which. The first is the join itself: a LEFT JOIN fills missing right-side columns with NULL, so Omar's department columns are NULL because no department attached. The second lives in the stored data: the Research department genuinely has a NULL city, so its employees Mia and Nils show NULL even though their join matched perfectly.
Same symbol, two reasons. When a NULL surprises you, ask whether the join manufactured it (no matching row) or whether it was already sitting in the table (a real empty field). Testing a guaranteed non-null column — a primary key — tells the two apart cleanly.
SELECT e.name AS employee, d.name AS department, d.city FROM employees e LEFT JOIN departments d ON e.department_id = d.id AND d.city = 'Oslo';
Compound conditions with OR
When the ON joins conditions with OR, a row can pair even if one branch is unknown, provided the other branch is true. ON a.dept = b.id OR a.role = b.role keeps a pair whenever either side holds. NULLs make individual branches unknown without sinking the whole condition, which is subtle — and a good reason to parenthesise any ON that mixes AND and OR, so the grouping reads back exactly as you intend.
Compound conditions are powerful precisely because they let one join encode a richer question, but every extra condition is another place a NULL can hide. Keep the conditions explicit, parenthesise generously, and test suspect rows against IS NULL rather than =.
Pair each employee with their department, but only when the employee earns more than 700000. Return the employee name and department name using a compound ON.
SELECT e.name, d.name FROM employees e JOIN departments d ON e.department_id = d.id -- add the salary condition to the ON here ;
Extend the ON with
AND e.salary > 700000.Only employees above the threshold keep their department in the result.
SELECT e.name, d.name FROM employees e JOIN departments d ON e.department_id = d.id AND e.salary > 700000;
Find the project whose department has no city stored. Return the project name and the department's city (which will be NULL). Join projects to departments and test for the missing value correctly.
SELECT p.name, d.city FROM projects p JOIN departments d ON p.department_id = d.id -- filter for the department with no city here ;
= NULLmatches nothing — useIS NULL.Filter with
WHERE d.city IS NULLafter the join.
SELECT p.name, d.city FROM projects p JOIN departments d ON p.department_id = d.id WHERE d.city IS NULL;
This query tries to list employees whose department has no city, but d.city = NULL inside the ON matches nothing, so it returns zero rows. Fix the NULL test so it returns the Research staff.
SELECT e.name, d.name FROM employees e JOIN departments d ON e.department_id = d.id AND d.city = NULL ;
= NULLis never true, even inside an ON clause.Replace
d.city = NULLwithd.city IS NULL.
SELECT e.name, d.name FROM employees e JOIN departments d ON e.department_id = d.id AND d.city IS NULL;
List employees who work in an Oslo department and earn more than 600000. Put both filters in the ON clause and return the employee name, department name, and salary.
SELECT e.name, d.name, e.salary FROM employees e JOIN departments d ON e.department_id = d.id -- add the Oslo and salary conditions to the ON here ;
Add
AND d.city = 'Oslo'to the ON.Add
AND e.salary > 600000so only the better-paid Oslo staff remain.
SELECT e.name, d.name, e.salary FROM employees e JOIN departments d ON e.department_id = d.id AND d.city = 'Oslo' AND e.salary > 600000;
Omar's department_id is NULL. Why does he never appear in employees INNER JOIN departments ON e.department_id = d.id?
Equality against NULL yields unknown, and unknown never satisfies the ON condition, so Omar's NULL key fails to pair with any department. The inner join drops him silently; only a LEFT JOIN would keep him, filling the department columns with NULL.
Recap
ONtakesAND,OR, and parentheses likeWHERE; each conjunct narrows which rows pair.- For INNER JOIN, a filter in
ONorWHEREgives the same rows; the split matters only for outer joins. - A NULL in a join key matches nothing — the row vanishes from the result without any error.
- Test for absence with
IS NULL, never= NULL, wherever the test sits. - A NULL in a result has two causes — manufactured by the join, or stored in the data — and a primary-key test tells them apart.
You have now linked, chained, crossed, and conditioned tables. The next module turns raw rows into answers a manager asks for, by rolling many rows up into sums, counts, and averages.