Joining Multiple Tables

Lesson 5

Introduction to SQL JOINs on Multiple Tables

Welcome back! In the previous lessons, we’ve covered various types of SQL JOINs, including INNER JOIN, LEFT JOIN, RIGHT JOIN, and FULL JOIN. You've practiced linking tables to gather desired data, and now it's time to extend that knowledge by joining multiple tables together.

Dataset Refresher

To give you a quick reminder, here are the three tables we are using:

Movies Table

movie_id	movie_name
1	Iron Man
2	The Incredible Hulk
3	Captain America: The First Avenger
4	The Avengers

Movie Details Table

movie_id	imdb_rating	box_office_million_usd
1	7.9	585.2
3	6.9	370.6
4	8.0	1519.6

Characters Table

character_id	movie_id	character_name
1	1	Tony Stark/Iron Man
2	1	Pepper Potts
3	1	James Rhodes
4	1	Obadiah Stane
5	3	Steve Rogers/Captain America
6	3	Peggy Carter
7	3	Bucky Barnes/Winter Soldier
8	3	Johann Schmidt/Red Skull
9	3	Howard Stark
10	4	Steve Rogers/Captain America
11	4	Tony Stark/Iron Man
12	4	Thor
13	4	Natasha Romanoff/Black Widow
14	4	Bruce Banner/Hulk
15	4	Clint Barton/Hawkeye
16	4	Loki
17	5	T'challa

The common key linking all these tables is movie_id.

Joining Multiple Tables

So far, we have focused on joining two tables together. Now we will expand to join multiple tables together.

When joining multiple tables, the process involves using several consecutive JOIN operations to combine data from more than two tables. Here is the generalized syntax for joining multiple tables:

SQL
1SELECT column1, column2, ...
2FROM table1
3JOIN table2 ON table1.matching_column = table2.matching_column
4JOIN table3 ON table2.matching_column = table3.matching_column
5...

SELECT column1, column2, ...: This specifies the columns you want to retrieve from the tables you are joining. You can list columns from any of the tables involved in the JOINs.
FROM table1: This is the starting table for your query. The JOIN operations will add information from other tables to this base table.
JOIN table2: This specifies the second table to join with the first table (table1).
ON table1.matching_column = table2.matching_column: This condition specifies how to match rows from table1 and table2. It uses a common column (here, matching_column) to identify which rows in table1 correspond to which rows in table2.
JOIN table3: After joining table1 and table2, this specifies the third table to join with the result of the previous join.
ON table2.matching_column = table3.matching_column: This condition specifies how to match rows from the result of table1 joined with table2 to rows in table3. Again, it uses a common column (here, matching_column) to establish this connection.

Multiple Tables JOIN Example

SQL
1SELECT movies.movie_name, movie_details.box_office_million_usd, characters.character_name 
2FROM movies 
3INNER JOIN movie_details ON movies.movie_id = movie_details.movie_id
4INNER JOIN characters ON movies.movie_id = characters.movie_id;

In this code:

We select movie_name from the movies table, box_office_million_usd from the movie_details table, and character_name from the characters table.
First, we INNER JOIN the movies and movie_details tables on movie_id.
Next, we INNER JOIN the characters table on movie_id.

The output is:

Plain text
1             movie_name             | box_office_million_usd |        character_name        
2------------------------------------+------------------------+------------------------------
3 Iron Man                           |                  585.2 | Obadiah Stane
4 Iron Man                           |                  585.2 | James Rhodes
5 Iron Man                           |                  585.2 | Pepper Potts
6 Iron Man                           |                  585.2 | Tony Stark/Iron Man
7 Captain America: The First Avenger |                  370.6 | Howard Stark
8 Captain America: The First Avenger |                  370.6 | Johann Schmidt/Red Skull
9 Captain America: The First Avenger |                  370.6 | Bucky Barnes/Winter Soldier
10 Captain America: The First Avenger |                  370.6 | Peggy Carter
11 Captain America: The First Avenger |                  370.6 | Steve Rogers/Captain America
12 The Avengers                       |                 1519.6 | Loki
13 The Avengers                       |                 1519.6 | Clint Barton/Hawkeye
14 The Avengers                       |                 1519.6 | Bruce Banner/Hulk
15 The Avengers                       |                 1519.6 | Natasha Romanoff/Black Widow
16 The Avengers                       |                 1519.6 | Thor
17 The Avengers                       |                 1519.6 | Tony Stark/Iron Man
18 The Avengers                       |                 1519.6 | Steve Rogers/Captain America
19(16 rows)

By progressively joining each table using a common column, you can combine information from all three tables into a comprehensive result set.

Multiple Tables JOIN Query using INNER JOIN and RIGHT JOIN

Let's take a look at joining multiple tables with different types of JOINs.

SQL
1SELECT movies.movie_name, movie_details.box_office_million_usd, characters.character_name 
2FROM movie_details
3INNER JOIN movies ON movie_details.movie_id = movies.movie_id
4RIGHT JOIN characters ON movies.movie_id = characters.movie_id;

In this code:

FROM movie_details: Start with the movie_details table.
INNER JOIN movies ON movie_details.movie_id = movies.movie_id: Perform an INNER JOIN between movie_details and movies on the movie_id column.
- This operation will return only the rows where there are matching movie_id values in both movie_details and movies.
RIGHT JOIN characters ON movies.movie_id = characters.movie_id: Perform a RIGHT JOIN with the characters table on the movie_id column from result of the previous JOIN.
- This operation ensures that all rows from the characters table are returned, along with the matching rows from the previous join (movies and movie_details). If there is no match, NULL will be introduced for columns from movies and movie_details.

The output is:

Plain text
1             movie_name             | box_office_million_usd |        character_name        
2------------------------------------+------------------------+------------------------------
3 Iron Man                           |                  585.2 | Tony Stark/Iron Man
4 Iron Man                           |                  585.2 | Pepper Potts
5 Iron Man                           |                  585.2 | James Rhodes
6 Iron Man                           |                  585.2 | Obadiah Stane
7 Captain America: The First Avenger |                  370.6 | Steve Rogers/Captain America
8 Captain America: The First Avenger |                  370.6 | Peggy Carter
9 Captain America: The First Avenger |                  370.6 | Bucky Barnes/Winter Soldier
10 Captain America: The First Avenger |                  370.6 | Johann Schmidt/Red Skull
11 Captain America: The First Avenger |                  370.6 | Howard Stark
12 The Avengers                       |                 1519.6 | Steve Rogers/Captain America
13 The Avengers                       |                 1519.6 | Tony Stark/Iron Man
14 The Avengers                       |                 1519.6 | Thor
15 The Avengers                       |                 1519.6 | Natasha Romanoff/Black Widow
16 The Avengers                       |                 1519.6 | Bruce Banner/Hulk
17 The Avengers                       |                 1519.6 | Clint Barton/Hawkeye
18 The Avengers                       |                 1519.6 | Loki
19 NULL                               |                   NULL | T'challa
20(17 rows)

The combined result set:

Includes all characters from the characters table.
Includes corresponding movie names and box office details whenever available.
Fills in NULL for movies or details when a character exists without corresponding entries in the movie_details or movies tables.

Summary and Next Steps

In this lesson, we put our knowledge of SQL JOINs into practice with multiple examples, covering INNER JOIN, LEFT JOIN, RIGHT JOIN, FULL JOIN, and combinations of multiple tables. Each example demonstrates how to retrieve complex data from relational tables efficiently.

Congratulations on completing the lesson and the course! You now have a solid understanding of SQL JOINs and how to use them in PostgreSQL. Continue practicing to reinforce these concepts and explore more complex queries. Great job, and happy querying!

Enjoy this lesson? Now it's time to practice with Cosmo!

Practice is how you turn knowledge into actual skills.