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:

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

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:

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:

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

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:

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:

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:

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!