Lesson 2 of 15 · 16 min
INNER JOIN: keep only the matches
By the end of this lesson you can write an INNER JOIN between two tables, predict how many rows it returns, and explain why rows without a match disappear.
In the last lesson you wrote JOIN … ON and got one row per order. Now let's be exact about what PostgreSQL does, because every other join type is a variation on it.
The rule
Conceptually, an inner join considers every pair of rows, one from each table, and keeps a pair only if the ON condition is true for it. Each kept pair becomes one result row containing the columns of both rows.
Two consequences follow directly from that rule:
- A row that matches several rows on the other side appears several times, once per match. A customer with three orders appears three times.
- A row that matches nothing disappears. A customer who has never ordered has no pair that satisfies the condition, so no row in the result mentions them.
That is why it is called inner: only the overlap between the two tables survives. (PostgreSQL doesn't really compare every pair; it uses indexes and hash tables to find matches quickly. The result is the same as if it had.)
This lesson, its 4 graded exercises and every other Members lesson in SQL Quest come with a Formula Quest membership that includes SQL (Single Quest with SQL selected, Quest Trio including SQL, or Quest Unlimited), or with SQL access from your organization.
Free to everyone: all of SQL Foundations and Retrieving and Filtering Data, the first lesson of every track, the playground, the dataset library and the SQL reference. See free lessons