Lesson 9 of 15 · 18 min
One-to-many relationships and the grain of a join
By the end of this lesson you can name the cardinality of a relationship, predict how a parent-to-child join changes the grain, count parents correctly with COUNT(DISTINCT), and avoid summing a parent's value once per child.
Cardinality: how many can match?
Every relationship between two tables has a cardinality, the answer to how many rows on each side can be linked to one row on the other:
| relationship | example in shop | one row on the left matches… |
|---|---|---|
| one-to-one (1:1) | (rare; e.g. a product and its extended description) | at most one row |
| one-to-many (1:N) | one order → its order lines; one customer → their orders | zero, one or many rows |
| many-to-one (N:1) | an order line → its product | exactly one row (or none) |
| many-to-many (N:M) | orders ↔ products (through order_items) | many, through a bridge table |
The foreign key always sits on the many side: order_items.order_id points to orders, so one order has many lines. The table with the primary key is the parent, the table with the foreign key the child.
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