SQL / JOINS
The join model: matching rows across tables
Predict exactly which rows a join returns, and how many, by reading any join as candidate row pairs filtered by its ON condition.
What you will learn
- Predict a join's result by pairing every row on one side with every row on the other
- Count output rows from matches per row, not from the row counts of the tables
- Read ON as a boolean test on a single candidate pair, not as a lookup or a merge
- Know why NULL keys never match: NULL = NULL is UNKNOWN, and only TRUE pairs survive
Understanding The join model: matching rows across tables
A join does not glue two tables side by side, and it does not look values up the way a spreadsheet formula does. The model is pairing: take one row from the left table and one row from the right table, evaluate the ON condition against that pair, and if the condition is TRUE emit a single output row whose columns are the two rows stuck end to end. Every pair of rows is a candidate, so a 3-row table joined to a 3-row table has 9 candidates to judge. In the example below, ON 1 = 1 keeps all 9 candidates so you can see them; swap it for ON e.dept_id = d.dept_id and you get exactly the two rows marked TRUE.
Once you see output rows as surviving pairs, row counts stop being surprising. A row that matches nothing contributes zero output rows, a row that matches once contributes one, and a row that matches three times contributes three, so the size of the result is the total number of matching pairs and has no fixed relationship to the size of either input. This is why the useful question before writing a join is never "which tables do I need" but "for one row here, how many rows over there can match: none, one, or many?" That answer tells you whether the join will shrink your data, leave the count alone, or multiply it.
The ON condition is an ordinary boolean expression evaluated over the two candidate rows, and it is evaluated with SQL's three-valued logic. Equality between a foreign key and a primary key is only a convention, popular because keys identify rows uniquely; BETWEEN, <, <>, or any expression mixing columns from both sides is equally legal. Because the pair survives only when the condition is TRUE, an UNKNOWN result behaves like false, which is exactly why a row whose join key is NULL matches nothing at all, not even another NULL.
CREATE TABLE employees (emp_id INTEGER, name TEXT, dept_id INTEGER);
INSERT INTO employees VALUES (1, 'Ada', 10), (2, 'Brij', 20), (3, 'Cleo', 40);
CREATE TABLE departments (dept_id INTEGER, dept_name TEXT);
INSERT INTO departments VALUES (10, 'Design'), (20, 'Support'), (30, 'Legal');
-- ON 1 = 1 keeps every candidate pair, so the real join condition
-- can be shown as a column instead of silently filtering.
SELECT e.name,
d.dept_name,
CASE WHEN e.dept_id = d.dept_id THEN 'TRUE' ELSE 'not TRUE' END AS on_condition
FROM employees e
JOIN departments d ON 1 = 1
ORDER BY e.emp_id, d.dept_id;A join emits one row for every pair of rows, one from each side, whose ON condition evaluates to TRUE, and every other property of joins follows from that.
Worked examples
One row per matching pair, so rows can fan out
Shows that a single row on one side becomes several output rows when several rows on the other side satisfy the condition.
CREATE TABLE tags (tag_id INTEGER, name TEXT);
INSERT INTO tags VALUES (1, 'urgent'), (2, 'billing');
CREATE TABLE tickets (ticket_id INTEGER, tag_id INTEGER, subject TEXT);
INSERT INTO tickets VALUES (100, 1, 'card declined'),
(101, 1, 'app crash'),
(102, 2, 'double charge');
SELECT t.name AS tag, k.ticket_id, k.subject
FROM tags t
JOIN tickets k ON k.tag_id = t.tag_id
ORDER BY k.ticket_id;Example explained
Line 1ON k.tag_id = t.tag_id is tested once per (tag, ticket) pair: 2 x 3 = 6 candidates, of which 3 are TRUE.
Line 2The urgent row of tags appears twice because two tickets pair with it, and each pair is its own output row.
Line 3t.name is duplicated in those two rows, so a later SUM or COUNT over this result would count urgent twice.
Line 4Nothing was added to the tags table; a new row set was built, and its size came from the pair count, not from either input.
NULL keys never pair, not even with each other
Demonstrates that the ON condition must be TRUE, so a NULL = NULL comparison discards the pair.
CREATE TABLE shipments (id INTEGER, carrier_code TEXT);
INSERT INTO shipments VALUES (1, 'DHL'), (2, NULL);
CREATE TABLE carriers (carrier_code TEXT, carrier_name TEXT);
INSERT INTO carriers VALUES ('DHL', 'DHL Express'), (NULL, 'Unknown');
SELECT s.id, s.carrier_code, c.carrier_name
FROM shipments s
JOIN carriers c ON s.carrier_code = c.carrier_code;Example explained
Line 1Four candidate pairs are judged, and only ('DHL', 'DHL Express') makes the condition TRUE.
Line 2Shipment 2 and the Unknown carrier both hold NULL, but NULL = NULL evaluates to UNKNOWN rather than TRUE.
Line 3A pair survives only on TRUE, so UNKNOWN is discarded exactly like FALSE, and both NULL rows disappear.
Line 4This is why unmatched rows in real data are so often the rows whose foreign key was never filled in.
The condition does not have to be equality
Joins rows to lookup ranges with BETWEEN, showing that any boolean expression over the pair works as a join condition.
CREATE TABLE orders (order_id INTEGER, amount INTEGER);
INSERT INTO orders VALUES (1, 40), (2, 150), (3, 900);
CREATE TABLE tiers (tier TEXT, min_amt INTEGER, max_amt INTEGER);
INSERT INTO tiers VALUES ('small', 0, 99), ('medium', 100, 499), ('large', 500, 10000);
SELECT o.order_id, o.amount, t.tier
FROM orders o
JOIN tiers t ON o.amount BETWEEN t.min_amt AND t.max_amt
ORDER BY o.order_id;Example explained
Line 1ON o.amount BETWEEN t.min_amt AND t.max_amt reads columns from both candidate rows, which is all a join condition needs.
Line 2Nine pairs are considered and three are TRUE, so the result happens to have one row per order.
Line 3That one-to-one shape depends on the tier ranges not overlapping; overlapping ranges would emit one row per matching tier.
Line 4No key, constraint, or index is required here, because the model only asks whether the expression is TRUE for a pair.
Important notes
The all-pairs picture describes the result, not the execution: engines use indexes, hash tables, and sorted merges and never build every pair, so a join is not slow by nature.
If NULLs must count as equal, say so explicitly with IS NOT DISTINCT FROM in PostgreSQL, IS in SQLite, or <=> in MySQL, because plain = will never match them.
Common mistakes
Assuming the result keeps one row per row of the "main" table, then aggregating: a one-to-many join fans rows out and SUM silently double-counts the repeated values.
Treating a join as "add the other table's columns to this row", so a key that is not unique on the other side multiplies rows and inflates every count downstream.
Expecting rows with NULL keys on both sides to find each other, so those rows quietly drop out and totals come up short with no error message.
Try it yourself
Change, predict, then run
Create authors with 3 rows and books with 5 rows, arranged so two authors have two books each, one author has none, and one book carries an author_id that exists in no author row. Write down the row count you expect from authors JOIN books ON books.author_id = authors.author_id before running it, then run it and account for every row that is missing or repeated.
Open the SQL workspaceCheck your understanding
Table a has 4 rows and table b has 10 rows. Under the ON condition, two rows of a match 3 rows of b each, one row of a matches 1 row of b, and the last row of a matches nothing. How many rows does the join return?
- 4, one for each row of a
- 7
- 8, because the unmatched row of a still appears once
- 40, because every row of a is combined with every row of b
Show answer
Output rows count matching pairs, so add the matches per row of a: 3 + 3 + 1 + 0 = 7. The answer 4 comes from imagining the join as attaching columns to each row of a, which ignores that a row matching 3 rows produces 3 output rows; 40 would be the result only if the condition kept every candidate pair instead of filtering them.