Level 9: Linking two tables with JOIN

Lesson

In a well-designed database, you avoid repeating information. The movies table does not contain the director's name, only director_id, a number. The name is stored once, in the directors table. If a director changes name, you only fix it in one place.

director_id is a foreign key: it points to the id column of directors, its primary key, which identifies each director uniquely. Night Train has director_id = 1: its director is the row of directors whose id is 1, Nora Ellis.

JOIN rebuilds that link. FROM movies JOIN directors ON directors.id = movies.director_id pairs each movie with the matching director row. ON gives the matching condition. The result has the columns of both tables.

To write less, you give each table a short alias: FROM movies m JOIN directors d ON d.id = m.director_id. You then prefix the columns: m.title, d.name. The prefix becomes mandatory when a column exists in both tables (id, name…): otherwise SQL does not know which one you mean.

JOIN (or INNER JOIN) only keeps the rows that have a match on both sides. Deep Current, without a director, disappears; Sara Diaz, without a movie, too. The next level shows how to keep them.

After a join, you can filter, sort and group as on a single table. When you group, group on the id as well as the name (GROUP BY d.id, d.name): two directors can share the same name. Our data has none, but it is common in a real database: build the habit now.

Syntax

SELECT a.col, b.col
FROM table_a a
JOIN table_b b ON b.id = a.b_id;

Worked example

SELECT m.title, d.name AS director
FROM movies m
JOIN directors d ON d.id = m.director_id;

Each movie is paired with its director. Deep Current, whose director_id is empty, doesn't appear.

Example result

Key points

• ON links the foreign key to the id.
• Prefix the columns with their table's alias.
• JOIN keeps only the rows that match.

Common pitfalls

• Two tables often have a name column: without a prefix, SQL reports an ambiguous column.
• Forgetting ON: SQLite then pairs each row with every row of the other table, without any error.
• GROUP BY a.name would merge two namesakes: group on a.id, a.name.

SQLite specifics

SQLite (like MySQL) accepts a JOIN without ON and treats it as a Cartesian product. PostgreSQL, SQL Server and Oracle reject the query: in standard SQL, JOIN requires ON (or USING). When you really want every combination, write it explicitly with CROSS JOIN.

The level’s 5 exercises

1. Guided · Show the title of each book and the name of its author. (Tables: books, authors)
2. Practice · Show the name of each player and the name of their team. (Tables: players, teams)
3. Practice · Show the hotel name, the type and the price of the rooms in hotels located in 'Paris'. (Tables: rooms, hotels)
4. Practice · Show the name of each artist and their number of songs. (Tables: songs, artists)
5. Challenge · Show the airline, arrival city and price of the 3 most expensive flights to a country other than France. (Tables: flights, airports)

In SpeedQL, every query is checked straight away: its result is compared with the expected one, then the query is run again on a hidden control database. Each exercise has written hints, to show only if you get stuck.

See also: the cheat sheet card · the SQL exercises with solutions on this topic