Joining Multiple Tables
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:
-
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 theJOINs. -
FROM table1: This is the starting table for your query. TheJOINoperations 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 fromtable1andtable2. It uses a common column (here,matching_column) to identify which rows intable1correspond to which rows intable2. -
JOIN table3: After joiningtable1andtable2, 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 oftable1joined withtable2to rows intable3. Again, it uses a common column (here,matching_column) to establish this connection.
Multiple Tables JOIN Example
In this code:
- We select
movie_namefrom themoviestable,box_office_million_usdfrom themovie_detailstable, andcharacter_namefrom thecharacterstable. - First, we
INNER JOINthemoviesandmovie_detailstables onmovie_id. - Next, we
INNER JOINthecharacterstable onmovie_id.
The output is:
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.
In this code:
-
FROM movie_details: Start with themovie_detailstable. -
INNER JOIN movies ON movie_details.movie_id = movies.movie_id: Perform anINNER JOINbetweenmovie_detailsandmovieson themovie_idcolumn.- This operation will return only the rows where there are matching
movie_idvalues in bothmovie_detailsandmovies.
- This operation will return only the rows where there are matching
-
RIGHT JOIN characters ON movies.movie_id = characters.movie_id: Perform aRIGHT JOINwith thecharacterstable on themovie_idcolumn from result of the previousJOIN.- This operation ensures that all rows from the
characterstable are returned, along with the matching rows from the previous join (movies and movie_details). If there is no match,NULLwill be introduced for columns frommoviesandmovie_details.
- This operation ensures that all rows from the
The output is:
The combined result set:
- Includes all characters from the
characterstable. - Includes corresponding movie names and box office details whenever available.
- Fills in
NULLfor movies or details when a character exists without corresponding entries in themovie_detailsormoviestables.
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!
