Welcome back! By now, you are familiar with different types of SQL joins, such as INNER JOIN, LEFT JOIN, and RIGHT JOIN. Today, we will dive into another powerful join operation known as FULL JOIN.

A FULL JOIN combines the results of both LEFT JOIN and RIGHT JOIN. This means it returns all rows when there is a match in one of the tables, as well as rows that do not have a match in either table. It fills in NULL values for columns from the table lacking corresponding rows. Essentially, a FULL JOIN gives you a complete picture of data from two tables.

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

Movies Table

Movie Details Table

Characters Table

Let's start by examining the basic syntax of a FULL JOIN in SQL:

SELECT column1, column2, ...
FROM table1
FULL JOIN table2 ON table1.common_column = table2.common_column;

This syntax queries two tables (table1 and table2) and returns rows where there is either a match in one of the tables or rows that do not match, filling in any missing values with NULL.

Let's work through an example using our Marvel movies dataset. Suppose we want to join the movies table and characters tables. We want to include all rows from both tables.

The SQL query is:

SELECT movies.movie_name, characters.character_name
FROM movies
FULL JOIN characters ON movies.movie_id = characters.movie_id;

In this code:

• SELECT movies.movie_name, characters.character_name: We are selecting movie_name from the movies table and character_name from the characters table.
• FROM movies: This specifies the first table in our FULL JOIN.
• FULL JOIN characters ON movies.movie_id = characters.movie_id: This joins the movies table with the characters table using movie_id as the common column.

The output is:

             movie_name             |        character_name        
------------------------------------+------------------------------
 Iron Man                           | Tony Stark/Iron Man
 Iron Man                           | Pepper Potts
 Iron Man                           | James Rhodes
 Iron Man                           | Obadiah Stane
 Captain America: The First Avenger | Steve Rogers/Captain America
 Captain America: The First Avenger | Peggy Carter
 Captain America: The First Avenger | Bucky Barnes/Winter Soldier
 Captain America: The First Avenger | Johann Schmidt/Red Skull
 Captain America: The First Avenger | Howard Stark
 The Avengers                       | Steve Rogers/Captain America
 The Avengers                       | Tony Stark/Iron Man
 The Avengers                       | Thor
 The Avengers                       | Natasha Romanoff/Black Widow
 The Avengers                       | Bruce Banner/Hulk
 The Avengers                       | Clint Barton/Hawkeye
 The Avengers                       | Loki
 NULL                               | T'challa
 The Incredible Hulk                | NULL
(18 rows)

• The result set includes movie_name from the movies table and character_name from the characters table wherever there is a match on movie_id.
• If a movie_id exists in the movies table but not in the characters table, the character_name will be NULL.
• Conversely, if a movie_id exists in the characters table but not in the movies table, the movie_name will be NULL.

Let's break down the output for each row:

• For "Iron Man", we get multiple rows, each showing its characters like "Tony Stark/Iron Man", "Pepper Potts", etc.
• Similarly, "Captain America: The First Avenger" and "The Avengers" have multiple entries showing their respective characters.
• "The Incredible Hulk" has a movie_name but no matching movie_id in the characters table; hence, its character_name is NULL.
• The character "T'challa" does not have a corresponding movie entry; hence, its movie_name is NULL.

So, the final result set includes all movies and all characters, with NULL values filled in wherever there is no match for a movie_id.

Once you understand how FULL JOIN works, you may want to filter and order the results to gain better insights into your data. This can be done using the WHERE clause for filtering and the ORDER BY clause for ordering. For example, if you want to see only the movies that have a member of the Stark family with the movies listed in alphabetical order, you can use the following query:

SELECT movies.movie_name, characters.character_name
FROM movies
FULL JOIN characters ON movies.movie_id = characters.movie_id
WHERE characters.character_name LIKE '%Stark%'
ORDER BY movies.movie_name;

The key lines of this code are:

• WHERE characters.character_name LIKE '%Stark%': This condition filters the results to include only those rows where the character_name contains "Stark". The % wildcard allows for any characters to appear before or after "Stark".
• ORDER BY movies.movie_name: This sorts the filtered results by movie_name in ascending order.

This result set below shows all movies that include at least one character whose name contains "Stark," ordered by movie_name.

             movie_name             |   character_name    
------------------------------------+---------------------
 Captain America: The First Avenger | Howard Stark
 Iron Man                           | Tony Stark/Iron Man
 The Avengers                       | Tony Stark/Iron Man
(3 rows)

By using filtering and ordering, you can refine and arrange your FULL JOIN results to better serve your analysis needs.

In this lesson, we explored the concept of FULL JOIN and its application using the Marvel movies dataset. We saw how FULL JOINs can provide a comprehensive view of data by including rows with no matches in one of the tables and handling NULL values. In addition, we saw how to filter and order the entries of the table, introducing the LIKE % syntax.

Now, it's your turn to practice! Head over to the practice exercises to apply what you've learned. Keep experimenting with the FULL JOIN to get comfortable with its behavior.