Diving into LEFT JOIN and RIGHT JOIN

Lesson 3

Introduction to LEFT JOIN and RIGHT JOIN

Welcome back! In our previous lessons, we covered the essentials of SQL JOINs, including INNER JOIN and how to use them to link tables in PostgreSQL. This time, we'll dive deeper into two specific types of JOINs: LEFT JOIN and RIGHT JOIN. Understanding these will help you retrieve comprehensive data from multiple tables, even when some records do not have matching entries in the joined table.

LEFT JOIN and RIGHT JOIN are powerful tools for data querying, providing flexibility in how you retrieve and relate data. Let's jump in and see how these JOINs work and why they're so essential.

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.

LEFT JOIN: Theory and Syntax

A LEFT JOIN, also known as LEFT OUTER JOIN, retrieves all records from the left table (the first one listed in the SQL query) and the matched records from the right table. If there is no match, the result is NULL on the side of the right table. This JOIN is useful when you want to include all records from one table, regardless of whether they have related records in the joined table.

Here's the basic syntax for a LEFT JOIN:

SQL
1SELECT columns
2FROM left_table
3LEFT JOIN right_table
4ON left_table.common_column = right_table.common_column;

left_table: The primary table from which you want all the records.
right_table: The secondary table from which you are joining records.
common_column: The column that the tables have in common to use for the join operation.

LEFT JOIN Example

Let's see a LEFT JOIN in action with our movies and characters tables. We want to retrieve all movies, including those that don't have any associated characters.

SQL
1SELECT movies.movie_name, characters.character_name 
2FROM movies 
3LEFT JOIN characters 
4ON movies.movie_id = characters.movie_id;

In this query:

SELECT movies.movie_name, characters.character_name: We're selecting the movie_name from the movies table and the character_name from the characters table.
FROM movies: We're using the movies table as our primary (left) table.
LEFT JOIN characters: We're joining the characters table as our secondary (right) table.
ON movies.movie_id = characters.movie_id;: We're using the movie_id column, present in both tables, as the key to join them.

If the movies table contains a movie with movie_id that doesn't exist in the characters table, that movie will still appear in our results with NULL in the character_name column.

The output is:

Plain text
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 The Incredible Hulk                | NULL
20(17 rows)

This query ensures all movies are displayed, and characters are included when available. The output shows "The Incredible Hulk" even though there are no associated characters for that movie.

RIGHT JOIN: Theory and Syntax

A RIGHT JOIN, also known as RIGHT OUTER JOIN, is similar to a LEFT JOIN, but it retrieves all records from the right table (the second one listed in the SQL query) and the matched records from the left table. If there is no match, the result is NULL on the side of the left table.

Here's the basic syntax for a RIGHT JOIN:

SQL
1SELECT columns
2FROM left_table
3RIGHT JOIN right_table
4ON left_table.common_column = right_table.common_column;

left_table: The primary table from which you want records if they match.
right_table: The secondary table from which you want all records, regardless of matching.
common_column: The column that the tables have in common to use for the join operation.

RIGHT JOIN Example

Now, let’s use a RIGHT JOIN to retrieve all characters, including those that don't have an associated movie.

SQL
1SELECT movies.movie_name, characters.character_name 
2FROM movies
3RIGHT JOIN characters
4ON characters.movie_id = movies.movie_id;

In this query:

SELECT movies.movie_name, characters.character_name : We're selecting the movie_name from the movies table and the character_name from the characters table.
FROM movies: We're using the movies table as our left table.
RIGHT JOIN characters: We're joining the characters table as our right table.
ON characters.movie_id = movies.movie_id: We're using the movie_id column, present in both tables, as the key to join them.

If the characters table contains characters with a movie_id that doesn't exist in the movies table, those characters will still appear in our results with NULL in the movie_name column.

The output is:

Plain text
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(17 rows)

This query ensures all characters are displayed, and movies are included when available. "The Incredible Hulk," which has no characters listed does not appear in the output. The character T'challa appears with the movie name as NULL.

Combining LEFT JOIN with the WHERE Clause

Using a LEFT JOIN in combination with the WHERE clause allows you to filter the results further.

Let's filter our previous LEFT JOIN example to display only movies with a rating of 7.0 or higher. If a movie does not have an IMDb rating, we still want to include it in the output. Let's take a look:

SQL
1SELECT movies.movie_name, movie_details.imdb_rating
2FROM movies
3LEFT JOIN movie_details
4ON movies.movie_id = movie_details.movie_id
5WHERE movie_details.imdb_rating >= 7.0 OR movie_details.imdb_rating IS NULL;

Let's inspect the WHERE clause. This condition filters the results to include movies with an IMDb rating of 7.0 or higher or movies where the imdb_rating is NULL.

The output is:

Plain text
1     movie_name      | imdb_rating 
2---------------------+-------------
3 Iron Man            | 7.9
4 The Avengers        | 8.0
5 The Incredible Hulk | NULL        
6(3 rows)

In this output, "Iron Man" and "The Avengers" meets the rating criteria, and "The Incredible Hulk" is included because its IMDb rating is NULL.

Summary and Practice Preparation

In this lesson, we delved into the workings of LEFT JOIN and RIGHT JOIN. We learned:

How a LEFT JOIN retrieves all records from the left table and matched records from the right table.
How a RIGHT JOIN retrieves all records from the right table and matched records from the left table.
How to combine a WHERE clause to filter results of a JOIN.

As you head into the practice exercises, remember:

Use LEFT JOIN when you need all records from the first (left) table.
Use RIGHT JOIN when you need all records from the second (right) table.

Happy querying!

Enjoy this lesson? Now it's time to practice with Cosmo!

Practice is how you turn knowledge into actual skills.