Data in two tables
A small library keeps two tables: authors and books. Each author is written down once, in authors, even though Gaiman wrote two books. Storing facts once means a fix to a name happens in one place.
Each book has an author_id. Its value is the id of a row in authors: Dune has author_id 1, and author 1 is Herbert. A column that points at another table like this is called a foreign key.
How a JOIN matches rows
To see each book beside its author’s name, ask for both tables at once. JOIN glues two tables together, and ON gives the rule for which rows belong together. The query goes to the DBMS.
As always, the DBMS starts with FROM. This time FROM names two tables: 4 authors and 4 books. It will look at every author together with every book: 4 × 4 = 16 pairs to test.
Take the first author, Herbert, id 1. The DBMS tests each book: is its author_id equal to 1? Only Dune passes, so the pair Herbert, Dune becomes one row of the answer.
Next, Gaiman, id 2. Two books pass: Coraline and Stardust. Each matching pair makes its own row, so Gaiman appears twice in the answer, though the name is stored only once.
Sachar matches Holes. Then Clarke, id 4: no book has author_id 4, so every test fails and she gets no row at all. A plain JOIN only keeps pairs that match.
INNER and LEFT
16 pairs were tested and 4 matched, so the answer has 4 rows of name and title. JOIN is short for INNER JOIN: only rows found in both tables come through.
Change JOIN to LEFT JOIN and every row of the left table, authors, stays. Clarke has no book, so she gets one row whose title is NULL, which means no value.
What happens
;; step 1/9 · tables table authors id name 1 Herbert 2 Gaiman 3 Sachar 4 Clarke table books id title author_id 1 Dune 1 2 Coraline 2 3 Holes 3 4 Stardust 2 Gaiman wrote Coraline and Stardust, yet appears once
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.