JOINs
Real databases split data into several tables, so each fact is written once. A JOIN puts the rows back together. Follow SELECT name, title FROM authors JOIN books ON books.author_id = authors.id as the DBMS tests every author against every book, keeps the pairs that match, and then see how LEFT JOIN keeps an author who has no books.
5 quick questions
Pick an answer to see why. Your score saves with your progress.
01Why does the library store author_id in books instead of writing the author's name on every book?
Storing each fact once means there is only one copy to keep right. author_id points at that one row, and a JOIN fetches the name when you need it.
02With the tables in the figure, how many rows does SELECT name, title FROM authors JOIN books ON books.author_id = authors.id return?
16 pairs are tested and 4 match: Herbert with Dune, Gaiman with Coraline, Gaiman with Stardust, and Sachar with Holes. Clarke has no book, so she gets no row.
03Gaiman is stored once in authors. Why does he appear twice in the answer?
A JOIN gives one row for each matching pair. Coraline and Stardust both have author_id 2, so Gaiman is paired with each of them.
04You change JOIN to LEFT JOIN. What happens to Clarke, who has no books?
A LEFT JOIN keeps every row of the left table. When a left row has no match, the columns from the right table are filled with NULL, which means no value.
05What is the difference between JOIN and INNER JOIN?
The PostgreSQL manual says INNER is the default, so JOIN and INNER JOIN mean the same thing. Both keep only the pairs that match.
Glossary
- JOIN
- A part of a query that combines rows of two tables into one row whenever they meet the ON rule. Short for INNER JOIN.
- ON
- The rule a JOIN uses to decide which rows belong together, such as books.author_id = authors.id.
- Foreign key
- A column whose values must match a row of another table, such as books.author_id pointing at authors.id.
- Primary key
- The column (or columns) that names each row uniquely, such as authors.id.
- INNER JOIN
- A join that keeps only the pairs of rows that match. Rows with no partner are left out.
- LEFT JOIN
- A join that keeps every row of the left table; when a row has no partner, the right table's columns are NULL.
- NULL
- A marker that means no value. Python's sqlite3 module gives it to you as None.
Notes and sources
- Without ORDER BY, SQL does not promise any order for the rows of an answer. The figure shows them author by author; add ORDER BY authors.id, books.id to ask for that.
- The DBMS does not really test every pair one by one: its planner picks a faster method, such as a hash join or an index. The answer is always the same as testing every pair.
- SELECT name, title works without table names because only authors has a name column and only books has a title column. When both tables share a column name, such as id, write authors.id or books.id.
- NULL means no value. psql shows it as an empty cell, and Python’s sqlite3 module turns it into None.
- REFERENCES authors (id) declares the foreign key, so the DBMS refuses a book whose author_id is not an id in authors. SQLite only enforces this after PRAGMA foreign_keys = ON.
- The Python example uses the sqlite3 module that ships with Python. The same SQL runs unchanged in PostgreSQL.
- PostgreSQL documentation: Table Expressions, Joined Tables
- PostgreSQL documentation: Tutorial, Joins Between Tables
- PostgreSQL documentation: Constraints, Foreign Keys
- PostgreSQL documentation: SELECT
- PostgreSQL documentation: Using EXPLAIN
- SQLite: SELECT (join operators)
- SQLite: Foreign Key Support
- Python Library Reference: sqlite3
- Silberschatz, Korth and Sudarshan, Database System Concepts (7th ed.), chapter 4.1, Join Expressions
Quiz Unlocks after the last step.