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

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.

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:

SELECT columns
FROM left_table
LEFT JOIN right_table
ON 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.

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.

SELECT movies.movie_name, characters.character_name 
FROM movies 
LEFT JOIN characters 
ON 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:

             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
 The Incredible Hulk                | NULL
(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.

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:

SELECT columns
FROM left_table
RIGHT JOIN right_table
ON 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.

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

SELECT movies.movie_name, characters.character_name 
FROM movies
RIGHT JOIN characters
ON 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:

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

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:

SELECT movies.movie_name, movie_details.imdb_rating
FROM movies
LEFT JOIN movie_details
ON movies.movie_id = movie_details.movie_id
WHERE 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:

     movie_name      | imdb_rating 
---------------------+-------------
 Iron Man            | 7.9
 The Avengers        | 8.0
 The Incredible Hulk | NULL        
(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.

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!