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.

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

Movies Table

Movie Details Table

Characters Table

The common key linking all these tables is movie_id.

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 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.

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:

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

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

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:

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.

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!