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:

SQL
Copy to clipboard
1SELECT column1, column2, ...
2FROM table1
3FULL 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:

SQL
Copy to clipboard
1SELECT movies.movie_name, characters.character_name
2FROM movies
3FULL 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:

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

SQL
Copy to clipboard
1SELECT movies.movie_name, characters.character_name
2FROM movies
3FULL JOIN characters ON movies.movie_id = characters.movie_id
4WHERE characters.character_name LIKE '%Stark%'
5ORDER 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.

Plain text
Copy to clipboard
1             movie_name             |   character_name    
2------------------------------------+---------------------
3 Captain America: The First Avenger | Howard Stark
4 Iron Man                           | Tony Stark/Iron Man
5 The Avengers                       | Tony Stark/Iron Man
6(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.