The Art of SQL JOINs: Combining Data from Multiple Tables

A JOIN combines rows from two or more tables based on a related column. That's it. No magic.

Real databases split data across tables. Movies in one table, actors in another, casting in a third. JOINs stitch them back together so you can ask questions that span multiple tables.

Here's a series of increasingly complex JOIN queries using a movie database.

Basic queries (no JOINs yet)

Films from 1962:

SELECT id, title
FROM movie
WHERE yr = 1962

Year of Citizen Kane:

SELECT yr
FROM movie
WHERE title = 'Citizen Kane'

All Star Trek films, ordered by year:

SELECT id, title, yr
FROM movie
WHERE title LIKE 'Star Trek%'
ORDER BY yr

These are single-table queries. Simple. But limited -- you can only access data in one table at a time.

Enter JOINs

Finding an actor's ID:

SELECT id
FROM actor
WHERE name = 'Glenn Close'

Getting a movie's ID:

SELECT id
FROM movie
WHERE title = 'Casablanca'

Now the real question: who was in Casablanca? That answer lives across three tables. You need a JOIN.

Cast list for Casablanca

SELECT actor.name
FROM actor
JOIN casting ON casting.actorid = actor.id
WHERE casting.movieid = 11768

The JOIN connects actor and casting through the actorid column. The WHERE filters to a specific movie.

Cast list for Alien (using movie title)

SELECT actor.name
FROM actor
JOIN casting ON casting.actorid = actor.id
JOIN movie ON movie.id = casting.movieid
WHERE movie.title = 'Alien'

Two JOINs. Three tables connected. Instead of hardcoding a movie ID, we join the movie table and filter by title.

All films with Harrison Ford

SELECT movie.title
FROM movie
JOIN casting ON casting.movieid = movie.id
JOIN actor ON casting.actorid = actor.id
WHERE actor.name = 'Harrison Ford'

Same three-table JOIN, different direction. Start from movies, connect through casting, filter by actor.

Harrison Ford -- non-starring roles only

SELECT movie.title
FROM movie
JOIN casting ON casting.movieid = movie.id
JOIN actor ON actor.id = casting.actorid
WHERE actor.name = 'Harrison Ford'
AND casting.ord != 1

The ord column indicates role order. ord = 1 means starring role. Adding ord != 1 excludes those.

The mental model

Think of JOINs as bridges between tables. Each JOIN ... ON clause defines which rows match across the bridge. You can chain multiple JOINs to connect as many tables as needed.

The benefit: you can keep your data normalized (no duplication) and still answer complex questions.

The cost: JOINs can get slow on large tables without proper indexes. If your query is joining millions of rows, you'll want to check your execution plan.