Welcome to the lesson on SQL JOINs with PostgreSQL! So far, you’ve learned how to retrieve data using basic SQL queries. Now, we’ll explore how to combine data from multiple tables, which is crucial for complex database querying and analysis.

In this lesson, we’ll use a simplified version of the Marvel movies dataset. Here’s a look at the simplified tables.

Movies Table

Movie Details Table

Characters Table

In order to link these tables together, we will use the movie_id key which is present in all 3 tables. Let’s dive in and explore how to use different types of JOINs to combine these tables effectively.

SQL JOINs help us combine rows from two or more tables based on related columns among them. There are 4 main categories of joins:

INNER JOIN

• Returns only the rows where there is a match in both tables. It’s useful when retrieving data that exists in both tables.

LEFT JOIN

• Returns all rows from the left table and the matched rows from the right table. If there is no match, the result is NULL on the side of the right table.

RIGHT JOIN

• Returns all rows from the right table and the matched rows from the left table. If there is no match, the result is NULL on the side of the left table.

FULL JOIN

• Returns all rows when there is a match in either table. Rows without a match in one of the tables will appear with NULLs in the places where there is no matching row.

Here is a great way to visualize how these different types of joins. In the diagram below, A and B are the tables we are joining and the green areas are the outputs of the different JOINs.

The basic syntax for a join query follows this structure:

• SELECT column1, column2, ...: Specifies the columns you want to retrieve from one or multiple tables.
• FROM table1: Indicates the primary table from which to start the query.
• JOIN_TYPE table2: Indicates the type of join (INNER JOIN, LEFT JOIN, RIGHT JOIN, FULL JOIN) and the second table to join.
• ON table1.column_name = table2.column_name: Specifies the join condition, indicating how the tables are related to each other by comparing columns.

We will cover the details of these queries in more detail later in the course. For now, just focus on how different types of joins affect the output.

An INNER JOIN returns only the rows where there is a match in both tables. Suppose, we want to retrieve the movie names and their corresponding IMDb ratings. The movies and movie_details tables both have a movie_id column. This query returns all rows where the movie_id values match.

In this query:

• The movies table is one side of the join.
• The movie_details table is the other side of the join.
• The ON clause specifies the condition to join the tables, which is movies.movie_id = movie_details.movie_id.

The output is:

The result of this query includes only the movies that have matching entries in the movie_details table. Even though "The Incredible Hulk" is found in the movies table, it is not in the movie_details table, so it does not appear in the output.

Next, let’s use a LEFT JOIN to list movies and their characters, even if a movie doesn’t have a character listed. In a LEFT JOIN, all rows from the left table are returned, along with matching rows from the right table where available.

In this query:

• The movies table is the left table.
• The characters table is the right table.
• The ON clause specifies the condition to join the tables, which is movies.movie_id = characters.movie_id.

The output is:

This result shows all movies, including:

• Movies with multiple characters, which appear multiple times in the result, each time with a different character.
• Movies without any characters listed, which appear with NULL in the character_name column. For example, "The Incredible Hulk" appears in the result even though it does not have any character listed in the characters table.

Now, let’s use a RIGHT JOIN to ensure we list all characters, even if they don’t have a corresponding movie entry. In a RIGHT JOIN, all rows from the right table are returned, along with matching rows from the left table where available.

In this query:

• The movies table is the left table.
• The characters table is the right table.
• The ON clause specifies the condition to join the tables, which is characters.movie_id = movies.movie_id.

The output is:

The result of this query includes:

• All rows from the characters table.
• The corresponding movie names from the movies table for characters where there is a match on movie_id.
• If no matching movies are found, the movie_name column will be NULL for that character. For example, "T'challa" appears in the result even though it does not have any movie listed in the movies table.

Lastly, let’s use a FULL JOIN to retrieve all movies and their characters, even if one of the tables doesn’t have a matching entry for a movie_id. In a FULL JOIN, all rows from both tables are returned, along with matching rows where available. If there is no match, NULL values are used for the missing parts.

In this query:

• The movies table is one side of the join.
• The characters table is the other side of the join.
• The ON clause specifies the condition to join the tables, which is movies.movie_id = characters.movie_id.

The output is:

The result of this query includes:

• All rows from the movies table.
• All rows from the characters table.
• The corresponding characters from the characters table for movies where there is a match on movie_id.
• The corresponding movie names from the movies table for characters where there is a match on movie_id.
• If no matching characters are found, the character_name column will be NULL for that movie.
• If no matching movies are found, the movie_name column will be NULL for that character.

In this lesson, you learned about different types of SQL JOINs and how to use them to combine data from multiple tables. You've practiced using INNER JOIN, LEFT JOIN, RIGHT JOIN, and FULL JOIN.

Now, it's time to practice! In the exercises, we will use an expanded dataset with more movies, characters, and movie details. Keep practicing to solidify your understanding!