Completing the Picture - FULL JOIN Mastery

Introduction to FULL JOIN

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.

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 11 Tony Stark/Iron Man 21 Pepper Potts 31 James Rhodes 41 Obadiah Stane 53 Steve Rogers/Captain America 63 Peggy Carter 73 Bucky Barnes/Winter Soldier 83 Johann Schmidt/Red Skull 93 Howard Stark 104 Steve Rogers/Captain America 114 Tony Stark/Iron Man 124 Thor 134 Natasha Romanoff/Black Widow 144 Bruce Banner/Hulk 154 Clint Barton/Hawkeye 164 Loki 175 T'challa

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

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

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

Join the 1M+ learners on CodeSignal

Be a part of our community of 1M+ users who develop and demonstrate their skills on CodeSignal

FULL JOIN Example

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

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

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:

text

             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)

             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.

Filtering and Ordering FULL JOIN

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

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;

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.

text

             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)

             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.

Introduction to FULL JOIN

Dataset Refresher

Join the 1M+ learners on CodeSignal

Understanding the FULL JOIN Syntax

FULL JOIN Example

Filtering and Ordering FULL JOIN

Summary and Next Steps