Exploring INNER JOIN

Welcome back! In the previous lesson, we covered different types of SQL JOINs, including INNER JOIN, LEFT JOIN, RIGHT JOIN, and FULL JOIN, using the Marvel movies dataset. Today, we're going to dive deeper into INNER JOIN and learn how to link the movies table with the movie_details and characters table. This lesson will help you understand how to combine data from these tables effectively.

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.

An INNER JOIN returns only the rows that have matching values in both tables. It combines columns from each table based on a related column between them. In our case, the related column is movie_id.

Here's a simple explanation of the terms involved:

• Keys: Unique identifiers in a table. In this case, movie_id is the key.
• Columns: Fields available in the table.

Using INNER JOIN, we can match rows from the movies table with rows from the movie_details table where the movie_id values are equal.

Now, let's break down the query that will help us link the movies table with the movie_details table.

SELECT movies.movie_name, movie_details.imdb_rating
FROM movies 
INNER JOIN movie_details 
ON movies.movie_id = movie_details.movie_id;

Let's go through each part of this query step-by-step:

• SELECT clause: This part specifies the columns we want to retrieve. In this case, we are selecting movies.movie_name and movie_details.imdb_rating.

• FROM clause: This part specifies the primary table from which to retrieve data. Here, it is the movies table.

• INNER JOIN clause: This part tells SQL to combine the movie_details table with the movies table.

• ON clause: This defines the relationship between the two tables using the movie_id column.

By running this query, we get the following result:

             movie_name             | imdb_rating 
------------------------------------+-------------
 Iron Man                           |         7.9
 Captain America: The First Avenger |         6.9
 The Avengers                       |         8.0
(3 rows)

The movies table contains movie_ids 1, 2, 3, and 4. The movie_details table only contains movie_ids 1, 3, and 4. Using INNER JOIN, we can match rows from the movies table with rows from the movie_details table where the movie_id values are equal. The movie with ID 2 is not present in the movie_details table, so it is not included in the result of the INNER JOIN.

Another common task when working with INNER JOIN queries is to sort the results based on specific columns. This is where the ORDER BY clause comes into play.

By adding an ORDER BY clause to our INNER JOIN query, we can sort the combined data in ascending (ASC) or descending (DESC) order based on one or more columns.

Let's enhance our previous INNER JOIN query by adding an ORDER BY clause to sort the results based on the imdb_rating in descending order.

SELECT movies.movie_name, movie_details.imdb_rating
FROM movies
INNER JOIN movie_details
ON movies.movie_id = movie_details.movie_id
ORDER BY movie_details.imdb_rating DESC;

The output shows the result of the INNER JOIN based on the imdb_rating column in descending order, displaying the highest rated movie first.

             movie_name             | imdb_rating 
------------------------------------+-------------
 The Avengers                       |         8.0
 Iron Man                           |         7.9
 Captain America: The First Avenger |         6.9
(3 rows)

In this lesson, we explored INNER JOIN and learned how to link the movies table with the movie_details table. In addition, you learned how to combine the ORDER BY clause to sort the result of the inner join.

In the practice exercises, we will use an expanded dataset with more movies, characters, and movie details. Try out the practice exercises that follow this lesson to reinforce your learning. Keep practicing, and soon you'll be a pro at using SQL JOINs in PostgreSQL!