What is the blood pressure of people who take a medicine for high blood pressure? In the NHANES database the answer lies in four tables: the readings in bp_reading, the medicines in prescription, the reasons for each medicine in rx_reason, with hypertension coded I10. A first attempt joins them:
SELECT COUNT(*) AS rows_, COUNT(DISTINCT r.seqn) AS people, AVG(r.sbp) AS mean_sbp
FROM bp_reading AS r
JOIN prescription AS p ON p.seqn = r.seqn
JOIN rx_reason AS x ON x.rx_id = p.rx_id
WHERE x.icd10 = 'I10' AND r.reading <= 3;
It returns a mean systolic pressure of 137.69 mm Hg, computed from 6,490 rows that describe 1,497 people. Each person appears once for every combination of one of their readings with one of their hypertension medicines, so someone with three readings and four such medicines counts twelve times. The mean of the 1,497 people, each counted once, is 136.47. The query is valid, runs without error, and answers a different question from the one asked.
Joins are how a relational database answers questions that span tables, and they are where most wrong answers in SQL come from. This article builds them up from the Cartesian product, covers the inner and outer joins and the less common forms, and ends with the problem above: knowing how many rows a join will produce. The pandas side of the same operations, merge with its validate and indicator checks, is in Enriching Data; the keys that joins follow are described in Relational Databases.
Joins as filtered products
Codd (1970) defined the natural join of a relation $R$ on columns $(a, b)$ with a relation $S$ on $(b, c)$ that share the domain $b$:
$$R * S = \{(a, b, c) : R(a, b) \wedge S(b, c)\},$$
the set of combinations of a row of $R$ and a row of $S$ that agree on the shared column. Every join in SQL has this form: take every pairing of a row from the left table with a row from the right table, the Cartesian product, and keep the pairs that satisfy a condition. The SQLite documentation puts it directly: "All joins in SQLite are based on the cartesian product of the left and right-hand datasets" (SQLite, n.d.-a). The database does not usually form the product, which would be wasteful; it uses indexes and other methods to find the matching pairs. But the product is the right mental model, because it says what the result contains.
Without a condition the product is the result. The person table has 9,254 rows and the race table 6, so
SELECT COUNT(*) FROM person CROSS JOIN race; -- 55,524
returns $9{,}254 \times 6 = 55{,}524$ rows, each person paired with every race. Writing FROM person, race with no WHERE condition does the same thing by accident, and on two large tables the result can be enormous. A forgotten join condition is easy to spot when the row count is checked.
Inner joins
An inner join keeps the pairs that match. The condition goes in an ON clause:
SELECT r.label, COUNT(*) AS people
FROM person AS p
JOIN race AS r ON p.race = r.code
GROUP BY r.label
ORDER BY people DESC;
| label | people |
|---|---|
| Non-Hispanic White | 3,150 |
| Non-Hispanic Black | 2,115 |
| Mexican American | 1,367 |
| Non-Hispanic Asian | 1,168 |
| Other Hispanic | 820 |
| Other or multiracial | 634 |
Each person's race code matches exactly one row of race, so the join has 9,254 rows, one per person, and gains the label column. JOIN means INNER JOIN. The short names after the tables, p and r, are aliases; they keep long queries readable and are required when a table appears twice. The older form puts the condition in WHERE, FROM person AS p, race AS r WHERE p.race = r.code, and gives the same 9,254 rows; in SQLite the comma, JOIN and INNER JOIN are interchangeable (SQLite, n.d.-a). The JOIN ... ON form is preferred because it keeps the conditions that link tables apart from those that filter rows, a distinction that starts to matter with outer joins.
When both tables have a column of the same name, a reference to it must say which table it means: SELECT seqn FROM person JOIN exam ON person.seqn = exam.seqn fails with "ambiguous column name: seqn". When the join condition is equality on identically named columns, USING (seqn) states it briefly and produces a single seqn column in the result.
NATURAL JOIN goes one step further and joins on every column name the two tables share. It looks convenient and is fragile. exam NATURAL JOIN lab joins on seqn and gives the expected 7,435 rows. person NATURAL JOIN race shares no column names (the key is race in one table and code in the other), and the SQLite documentation notes that in that case "the NATURAL keyword has no effect on the results of the join" (SQLite, n.d.-a): the result is the full product of 55,524 rows. Worse, a natural join changes meaning whenever someone adds a column to either table whose name happens to occur in the other. Stating the join columns is safer.
A row whose join column is NULL matches nothing, because NULL = NULL is not true (Simple SQL Queries). The 134 prescriptions with no drug identifier disappear from prescription JOIN drug, which has 14,300 rows, not 14,434. pandas behaves differently: its merge matches missing keys with each other, a difference Enriching Data quotes from the pandas documentation.
Outer joins
An inner join drops rows that have no partner. An outer join keeps them, filling the columns of the missing side with NULL. Of the 9,254 people, 550 were interviewed but not examined:
SELECT COUNT(*) AS rows_, COUNT(e.seqn) AS with_exam, SUM(e.seqn IS NULL) AS without_exam
FROM person AS p
LEFT JOIN exam AS e ON e.seqn = p.seqn;
A LEFT JOIN keeps every row of the left table, here all 9,254 people, and for the 550 without an examination fills the exam columns with NULL. Testing e.seqn IS NULL identifies them. (This is the same 550 that Enriching Data finds with a left merge of the demographic and blood pressure files in pandas.) RIGHT JOIN keeps every row of the right table, and FULL JOIN keeps every row of both.
The four types are clearest on two tables that overlap only partly. The smoking questionnaire, smoking, has 6,724 people aged 12 and over who were interviewed. The laboratory table, lab, has 7,435 examined people aged 6 and over. Joining them on seqn:
| Join | Keeps | Rows |
|---|---|---|
smoking JOIN lab |
people in both | 6,401 |
smoking LEFT JOIN lab |
all of smoking |
6,724 |
smoking RIGHT JOIN lab |
all of lab |
7,435 |
smoking FULL JOIN lab |
everyone in either | 7,758 |
The unmatched parts can be explained, which is a useful habit. The 323 people in smoking but not in lab were interviewed but not examined. The 1,034 in lab but not in smoking are all aged 6 to 11, below the age of the smoking questions. An unexplained group of unmatched rows usually means a key problem: a code that differs between tables, a filter applied to one table and not the other, or a misunderstanding of what the tables contain.
The diagram is the common way of picturing joins, and it needs the caveat in its caption. Circles show sets of keys. When a key appears more than once in either table, a join pairs every copy with every matching copy, and the result is not a region of the diagram but something larger, as the final section shows.
RIGHT and FULL joins in SQLite
SQLite added RIGHT and FULL OUTER JOIN only in version 3.39.0, released in June 2022, with the comment "long overdue" (SQLite, n.d.-b). The SQLite built into a given Python depends on the build: the Python used for this site's tests carries SQLite 3.38.4, which rejects the query with "RIGHT and FULL OUTER JOINs are not currently supported", while the newer Python that ran this article's scripts has 3.49.1. MySQL has no FULL JOIN at all: its join syntax accepts inner, cross, left and right joins only (MySQL, n.d.). A right join can always be rewritten as a left join with the tables swapped, and a full join as a left join plus the rows of the right table that have no partner:
SELECT s.seqn, s.smq020, l.seqn, l.chol
FROM smoking AS s LEFT JOIN lab AS l ON l.seqn = s.seqn
UNION ALL
SELECT s.seqn, s.smq020, l.seqn, l.chol
FROM lab AS l LEFT JOIN smoking AS s ON s.seqn = l.seqn
WHERE s.seqn IS NULL;
The first half returns everyone in smoking, matched or not; the second adds the people in lab who have no row in smoking. UNION ALL stacks the two results without removing duplicates, which is correct here because the halves cannot overlap. On this database the emulation returns the same 7,758 rows as the native FULL JOIN, row for row. UNION without ALL would also remove duplicate rows that were genuinely in the data, so UNION ALL is the right choice.
ON against WHERE
For an inner join, a condition gives the same result whether it is written in ON or in WHERE. For an outer join it does not, and the SQLite documentation explains why: "the extra NULL rows for non-matched rows on the other operand are added after ON clause processing but before WHERE clause processing" (SQLite, n.d.-a). Two queries over the 5,265 examined adults aged 20 and over show the difference:
-- the condition on lab is part of the match
SELECT COUNT(*) AS rows_, COUNT(l.seqn) AS matched
FROM person AS p
LEFT JOIN lab AS l ON l.seqn = p.seqn AND l.chol >= 240
WHERE p.age >= 20 AND p.status = 2; -- 5,265 rows, 518 matched
-- the condition on lab filters the joined rows
SELECT COUNT(*) AS rows_, COUNT(l.seqn) AS matched
FROM person AS p
LEFT JOIN lab AS l ON l.seqn = p.seqn
WHERE p.age >= 20 AND p.status = 2 AND l.chol >= 240; -- 518 rows
The first keeps every adult and attaches a laboratory row only to the 518 with high cholesterol, the right form for "all adults, with their result if it is high". In the second, the WHERE condition is unknown for every adult whose l.chol is NULL, including those padded with NULLs by the left join, so they are all removed and the left join has silently become an inner join. A WHERE condition on the right-hand table of a left join is a common source of this mistake; if a query needs the unmatched rows, conditions on the optional table belong in ON.
Other forms of join
Self-joins
A table can be joined to itself, with two aliases. Joining the prescription table to itself on the person pairs every medicine a person takes with every other one, and counting people per pair finds the drugs most often taken together:
SELECT d1.name AS drug_1, d2.name AS drug_2, COUNT(DISTINCT a.seqn) AS people
FROM prescription AS a
JOIN prescription AS b ON b.seqn = a.seqn AND a.drug_id < b.drug_id
JOIN drug AS d1 ON d1.drug_id = a.drug_id
JOIN drug AS d2 ON d2.drug_id = b.drug_id
GROUP BY a.drug_id, b.drug_id
ORDER BY people DESC
LIMIT 5;
| drug_1 | drug_2 | people |
|---|---|---|
| METFORMIN | ATORVASTATIN | 185 |
| LISINOPRIL | METFORMIN | 179 |
| LISINOPRIL | ATORVASTATIN | 154 |
| METOPROLOL | ATORVASTATIN | 124 |
| AMLODIPINE | ATORVASTATIN | 120 |
Diabetes, high cholesterol and high blood pressure often occur together, and so do their treatments. The condition a.drug_id < b.drug_id does two jobs: it stops a medicine pairing with itself, and it counts each pair once instead of twice (once in each order). Without it the self-join has 88,600 rows, the sum over people of the square of their number of prescriptions; the person with 22 medicines alone contributes 484. Self-joins grow quadratically in group size, and on tables with large groups they are expensive.
Semi-joins and anti-joins
Often a join is used only to test whether a match exists. A semi-join keeps the rows of one table that have a match in another, without adding columns or duplicating rows; an anti-join keeps those that have none. SQL has no keyword for either; they are written with EXISTS and NOT EXISTS (Subqueries), with IN and NOT IN, or with a left join and a NULL test. For the examined people who have no laboratory row, all three forms give 1,269:
SELECT COUNT(*) FROM exam AS e
LEFT JOIN lab AS l ON l.seqn = e.seqn
WHERE l.seqn IS NULL; -- 1,269
SELECT COUNT(*) FROM exam AS e
WHERE NOT EXISTS (SELECT 1 FROM lab AS l WHERE l.seqn = e.seqn); -- 1,269
SELECT COUNT(*) FROM exam
WHERE seqn NOT IN (SELECT seqn FROM lab); -- 1,269
All 1,269 are children aged 0 to 5, too young for the blood tests in these files. The forms agree here because lab.seqn is a primary key and cannot be NULL. When the column in the subquery can be NULL, NOT IN can return nothing at all, and NOT EXISTS is the safe form, as Subqueries shows on a real case.
Non-equijoins
A join condition need not be an equality. A band join matches each row with the row of another table whose interval contains it. The adult BMI classes are intervals, and storing them as a table, with a lower and an upper bound for each, turns classification into a join:
SELECT c.label, COUNT(*) AS adults
FROM exam AS e
JOIN person AS p USING (seqn)
JOIN bmi_class AS c ON e.bmi >= c.lower AND e.bmi < c.upper
WHERE p.age >= 20
GROUP BY c.label;
The classes hold 82, 1,255, 1,667, 1,132, 569 and 470 adults, from underweight to obesity class 3, 5,175 together, exactly the number of adults aged 20 and over with a BMI. That total is the check: with half-open intervals each adult falls in one class. Writing the condition as e.bmi BETWEEN c.lower AND c.upper gives 5,261 rows, because the 86 adults whose BMI sits exactly on a boundary such as 25.0 match two classes, the trap described in Special Operators and Clauses. A band join is the SQL form of pd.cut, with the bins kept as data.
Cardinality: how many rows a join returns
The number of rows a join returns depends on how many rows match each key on each side. If a key value occurs $m$ times in the left table and $n$ times in the right, an inner join produces $m \times n$ rows for it. The result has
$$\sum_k m_k \, n_k$$
rows, summed over the key values $k$. For a key that is unique in one table, as with a foreign key joined to the primary key it refers to, each $n_k$ is 0 or 1 and the join cannot have more rows than the many side. When the key repeats on both sides, the counts multiply.
That is what happened in the opening example. The blood pressure table has up to three readings per person in the query, and a person can take several medicines for hypertension. Joining the two through the person creates every combination: 6,490 rows for 1,497 people. The mean over those rows is a weighted mean, in which each person counts in proportion to the number of their readings times the number of their hypertension medicines. People taking more medicines have higher pressures:
| Hypertension medicines | People | Mean systolic (mm Hg) |
|---|---|---|
| 1 | 938 | 134.84 |
| 2 | 400 | 137.63 |
| 3 | 124 | 140.19 |
| 4 | 27 | 148.04 |
(and 8 people take five to seven), so the weighting pulls the mean up, to 137.69 against the 136.47 of the 1,497 people each counted once. Even after averaging each person's readings first, joining the per-person table to the prescriptions still repeats each person once per medicine, 2,261 rows with a mean of 138.07. The error is not a matter of syntax; it is that the grain of the result, one row per combination, is not the grain of the question, one row per person.
There are three ways to keep the grain.
- Test for existence instead of joining. The question "people who take a medicine for hypertension" is a semi-join.
WHERE EXISTS (SELECT 1 FROM prescription AS p JOIN rx_reason AS x ON x.rx_id = p.rx_id WHERE p.seqn = b.seqn AND x.icd10 = 'I10')keeps each person once, whatever their number of medicines, and gives 136.47. - Aggregate before joining. Reducing each table to one row per person first, as the
person_bpview of Aggregate Functions does for the readings, makes every join one-to-one. - Use DISTINCT with care.
COUNT(DISTINCT seqn)counts people correctly in a multiplied join, butAVG(DISTINCT sbp)averages distinct values, not people, and is wrong in a different way.DISTINCTis a repair for counts, not for sums and means.
The guard against all of these is to predict the number of rows before running a join and to check it afterward, with COUNT(*) beside COUNT(DISTINCT key). Enriching Data shows the same failure at a larger scale, a pandas merge on a non-unique key that turns 2,808 rows into more than 12 million, and the validate argument that catches it. SQL has no such argument; its equivalent is the schema, where a primary key or a UNIQUE constraint on the right-hand table guarantees that a join to it cannot multiply rows.
Joins and performance
Because a query is declarative, the database chooses how to perform a join: which table to read first, and whether to find matches by scanning, by an index or by a hash table built for the purpose. The choice matters for speed, not for the result. An index on the join columns of the larger table is the usual requirement for a fast join; primary keys are indexed automatically, but foreign key columns, such as prescription.seqn, are not unless an index is created. EXPLAIN QUERY PLAN shows the choice (Simple SQL Queries). When several tables are joined, the SQLite documentation describes the result as if the joins were computed from left to right (SQLite, n.d.-a), but the planner is free to reorder inner joins; the documentation notes that writing CROSS JOIN is the way to stop it (SQLite, n.d.-a).
In Python
import sqlite3
import pandas as pd
con = sqlite3.connect("nhanes.db")
# the join in SQL ...
both = pd.read_sql("""SELECT s.seqn, s.smq020, l.chol
FROM smoking AS s JOIN lab AS l USING (seqn)""", con)
# ... or in pandas, with the checks that SQL leaves to the schema
smoking = pd.read_sql("SELECT * FROM smoking", con)
lab = pd.read_sql("SELECT * FROM lab", con)
merged = smoking.merge(lab, on="seqn", how="outer", validate="one_to_one", indicator=True)
merged["_merge"].value_counts() # both 6,401, left_only 323, right_only 1,034
The numbers in this article are computed by tools/experiments/data-science/sql-joins.py in the site's repository, which also runs the FULL JOIN test against the older SQLite, and the figure is drawn by tools/figures/sql-joins.py.
References
- Codd, E. F. (1970). A relational model of data for large shared data banks. Communications of the ACM, 13(6), 377–387. https://doi.org/10.1145/362384.362685 record · 3 pages
- MySQL (n.d.). JOIN clause. MySQL 8.4 Reference Manual. https://dev.mysql.com/doc/refman/8.4/en/join.html record
- SQLite (n.d.-a). The SELECT statement. https://sqlite.org/lang_select.html record · 5 pages
- SQLite (n.d.-b). Release 3.39.0 on 2022-06-25. https://sqlite.org/releaselog/3_39_0.html record · 3 pages