Session 7 · Joins#
The big idea#
Good design splits data across tables, so to answer real questions you join them back together. A join matches rows from two tables using a condition (usually foreign key = primary key) and returns them side by side. The kind of join decides what happens to rows that find no match.
Everyday example: a class register and a fee register. The register lists names; the fee register
lists payments by roll number. To see “name next to amount paid” you line them up by roll number.
Students who have not paid are the interesting case: do you still list them (with a blank amount) or
leave them out? That choice is LEFT JOIN versus INNER JOIN.
The queries#
\echo '-- INNER JOIN: only movies that have a director'
SELECT m.title, d.name AS director FROM movies m INNER JOIN directors d ON d.director_id = m.director_id ORDER BY m.year;
\echo '-- LEFT JOIN: keep every movie, even without a director'
SELECT m.title, d.name AS director FROM movies m LEFT JOIN directors d ON d.director_id = m.director_id ORDER BY m.year DESC LIMIT 3;
\echo '-- LEFT JOIN from directors: who has no movies?'
SELECT d.name FROM directors d LEFT JOIN movies m ON m.director_id = d.director_id WHERE m.movie_id IS NULL;
\echo '-- joining three tables: movie, actor, via the link table'
SELECT m.title, a.name AS actor FROM movies m JOIN movie_cast mc ON mc.movie_id = m.movie_id JOIN actors a ON a.actor_id = mc.actor_id WHERE m.title = 'Vikram' ORDER BY a.name;
\echo '-- join plus GROUP BY: movies per director'
SELECT d.name, count(m.movie_id) AS movies FROM directors d LEFT JOIN movies m ON m.director_id = d.director_id GROUP BY d.name ORDER BY movies DESC, d.name;
\echo '-- the classic mistake: forgetting ON gives every combination'
SELECT count(*) AS rows_without_on FROM movies, directors;
SELECT count(*) AS rows_with_on FROM movies m JOIN directors d ON d.director_id = m.director_id;
-- INNER JOIN: only movies that have a director
title | director
-------------------+------------------
Nayakan | Mani Ratnam
Thalapathi | Mani Ratnam
Baasha | Suresh Krissna
Muthu | K. S. Ravikumar
Sivaji | Shankar
Pariyerum Perumal | Mari Selvaraj
Kaithi | Lokesh Kanagaraj
Soorarai Pottru | Sudha Kongara
Karnan | Mari Selvaraj
Vikram | Lokesh Kanagaraj
(10 rows)
-- LEFT JOIN: keep every movie, even without a director
title | director
------------------+------------------
Untitled Project |
Vikram | Lokesh Kanagaraj
Karnan | Mari Selvaraj
(3 rows)
-- LEFT JOIN from directors: who has no movies?
name
-------------
Vetrimaaran
(1 row)
-- joining three tables: movie, actor, via the link table
title | actor
--------+------------------
Vikram | Fahadh Faasil
Vikram | Kamal Haasan
Vikram | Suriya
Vikram | Vijay Sethupathi
(4 rows)
-- join plus GROUP BY: movies per director
name | movies
------------------+--------
Lokesh Kanagaraj | 2
Mani Ratnam | 2
Mari Selvaraj | 2
K. S. Ravikumar | 1
Shankar | 1
Sudha Kongara | 1
Suresh Krissna | 1
Vetrimaaran | 0
(8 rows)
-- the classic mistake: forgetting ON gives every combination
rows_without_on
-----------------
88
(1 row)
rows_with_on
--------------
10
(1 row)
Reading the results#
INNER JOIN ... ON d.director_id = m.director_idreturned 10 rows. “Untitled Project” has no director and “Vetrimaaran” has no movie, so neither appears.LEFT JOINkeeps every row from the left table (movies). “Untitled Project” is still there, with an empty director (NULL).Finding what is missing:
LEFT JOIN ... WHERE m.movie_id IS NULLlists directors with no movies. This “anti-join” pattern answers “who has never…?” questions.Three tables joined through the link table list Vikram’s four actors.
LEFT JOINplusGROUP BYcounts movies per director, including a zero for Vetrimaaran. Countingm.movie_id(not*) is what makes the zero correct.The last two queries show the most common bug. Without an
ON, every movie pairs with every director: 11 x 8 = 88 rows. With theON, 10.
Aliases make joins readable#
movies m and directors d are short names (aliases) for the tables. They save typing and are
required when the same table appears twice. Prefix columns with the alias (m.title) so the reader
and the database both know which table you mean.
Common mistakes#
Forgetting the
ONcondition. You get a “cartesian product”, every row paired with every row (88 here). If a result is suspiciously huge, check your joins first.Using
INNER JOINwhen you needed to keep the unmatched rows. Rows silently disappear.Filtering a
LEFT JOINinWHEREon the right table, e.g.WHERE d.name = 'X', which turns it back into an inner join. Put such conditions in theONclause instead.Selecting a column that exists in both tables without a prefix. PostgreSQL says “column reference is ambiguous”.
count(*)after a left join. It counts the NULL row as 1. Count a column from the right table.
Hands-on exercises#
Exercise 1. List every movie with its director’s name, showing only movies released after 2018.
Solution
SELECT m.title, d.name AS director, m.year
FROM movies m JOIN directors d ON d.director_id = m.director_id
WHERE m.year > 2018
ORDER BY m.year;
Exercise 2. Which actors have never been cast in a movie? (Hint: left join and look for NULL.)
Solution
SELECT a.name
FROM actors a LEFT JOIN movie_cast mc ON mc.actor_id = a.actor_id
WHERE mc.movie_id IS NULL;
With the practice data every actor has at least one movie, so this returns no rows. Insert a new actor first to see it work.
Exercise 3. Show each actor and how many movies they appear in, most first.
Solution
SELECT a.name, count(mc.movie_id) AS movies
FROM actors a LEFT JOIN movie_cast mc ON mc.actor_id = a.actor_id
GROUP BY a.name
ORDER BY movies DESC, a.name;
Rajinikanth comes first with 4 movies.
Practice#
Take the Session 7 quiz on join types. Extra worked examples (employees and departments) are in the reference section.