RIGHT JOIN in SQL: A Beginner's Tutorial

Learn RIGHT JOIN, a crucial command for any SQL specialist. Our article breaks down this essential technique, helping beginners effortlessly navigate and apply RIGHT JOIN in their database queries.

In SQL, RIGHT JOIN (also known as RIGHT OUTER JOIN) is crucial for handling data effectively. This article is a beginner-friendly guide to the SQL RIGHT JOIN, an essential technique for merging different data tables. We'll walk you through the specifics of RIGHT JOIN, explaining how it stands apart from other join types in managing data. Join us to discover how RIGHT JOIN in SQL can enhance your data management skills.

To solidify your understanding of joins, including RIGHT JOIN, the SQL JOINs course at LearnSQL.com is highly recommended. This interactive course is designed to equip beginners with essential SQL skills, ensuring they can handle real database scenarios effectively.

Understanding JOINS in SQL

In SQL, a join is a fundamental feature that combines data from two tables based on a matching column. It's essential for tasks where information is stored across different tables and needs to be viewed together. For instance, if you have one table with employee details and another with department data, a join can help you see which employee belongs to which department.

Among the various types of joins, RIGHT JOIN is a key technique. It might not be as commonly used as INNER JOIN, but it's vital for a comprehensive understanding of SQL.

Introducing Our Example Data

The Restaurants Table

This table lists various restaurants along with the year they opened. Notably, "The Mystic Pizzeria" is a newly opened restaurant with no ratings yet.

The Ratings Table

This table shows the ratings given by customers. The ratings are linked to the restaurants by restaurant ID.

INNER JOIN vs. RIGHT JOIN in SQL

In this section, we'll explore the key differences between RIGHT JOIN and INNER JOIN, two important types of joins in SQL.

INNER JOIN

INNER JOIN is the most common type of join. It is used to combine rows from two tables. It returns only the rows where there is a matching value in both tables. If a row in one table does not have a corresponding match in the other table, it won't appear in the result set.

Let's apply an INNER JOIN to our example dataset. We want to find out the ratings each restaurant has received.

SELECT 
  Restaurants.Name, 
  Restaurants.OpeningYear, 
  Ratings.Rating, 
  Ratings.Reviewer
FROM Restaurants
INNER JOIN Ratings 
ON Restaurants.RestaurantID = Ratings.RestaurantID;

This query joins the Restaurants table with the Ratings table based on their common RestaurantID column. In the FROM clause, we list the first table. Then we use the keyword INNER JOIN, followed by the ON clause and the join condition. The join condition determines how we combine information from the two tables: in this case, rows must have the same restaurant ID.

Here’s the result of the query:

In this result, each entry from the Restaurants table that has a corresponding rating in the Ratings table is displayed. Notably, "The Mystic Pizzeria" is absent from the result. Despite being listed in the Restaurants table, it has no matching entries in the Ratings table; it is excluded from the results. This exemplifies the selective nature of INNER JOIN, which focuses on intersecting data between two tables.

RIGHT JOIN (RIGHT OUTER JOIN)

RIGHT JOIN, also known as RIGHT OUTER JOIN, returns all rows from the right table (listed after JOIN), along with the matched rows from the left table (listed after FROM). If there is no match, the result set will still include that row from the right table. However, the missing information from the left table will be shown as NULL.

A RIGHT JOIN can return more rows than an INNER JOIN if there are rows in the right table that don't have matches in the left table.

RIGHT JOIN Syntax

The basic syntax for a RIGHT JOIN in SQL is as follows:

SELECT columns
FROM table1
RIGHT JOIN table2
ON table1.column_name = table2.column_name;

Here, table1 and table2 are the two tables you're joining. The ON clause specifies the condition on which the tables should be joined. Usually, it’s an equality between the two selected columns.

A Basic Example of RIGHT JOIN

To illustrate this, let's consider a scenario where we want to list all restaurants and any ratings they might have. We want to ensure that every restaurant is included in our query result, regardless of whether it has been rated or not. This is a perfect use case for RIGHT JOIN, given that our aim is to retain all records from the Restaurants table.

SELECT 
  Restaurants.Name, 
  Restaurants.OpeningYear, 
  Ratings.Rating, 
  Ratings.Reviewer
FROM Ratings
RIGHT JOIN Restaurants 
ON Ratings.RestaurantID = Restaurants.RestaurantID;

Here are the results:

How RIGHT JOIN Works

Logically, a RIGHT JOIN works by first looking at all the rows in the right table, which in our example is the Restaurants table. For every row in this table, it tries to find a matching row in the left table (Ratings), based on the equality expression defined in the ON clause.

• Matching Rows: For each restaurant that has received at least one rating, the query combines the restaurant's details with its rating details. This includes restaurants like "Galactic Diner" and "Cove Café", which have matching entries in the Ratings
• Non-Matching Rows: "The Mystic Pizzeria" has not received any ratings yet, but the query still includes it in the result. However, because there are no matching ratings, the Rating and Reviewer columns for this restaurant are filled with NULL This means RIGHT JOIN is particularly valuable when the objective is to preserve a complete view of the records from one table – irrespective of the existence of matching records in the other table.

The Key Difference Between RIGHT JOIN and INNER JOIN

The main difference between these two lies in how they handle non-matching rows:

• INNER JOIN excludes rows that do not have a match in both tables.
• RIGHT JOIN includes all rows from the right table, regardless of whether there's a match in the left table. It fills any missing values with NULLs.

Understanding this distinction is crucial for deciding which type of join to use based on the data retrieval requirements of your query.

RIGHT JOIN vs. LEFT JOIN

The concept of RIGHT JOIN is easier to grasp when you understand its relationship with LEFT JOIN. Essentially, RIGHT JOIN and LEFT JOIN are two sides of the same coin. While LEFT JOIN returns all rows from the left table and the matched rows from the right table, RIGHT JOIN does the opposite. It returns all rows from the right table and the matched rows from the left table.

In SQL syntax, switching a query from a RIGHT JOIN to a LEFT JOIN (or vice versa) is as simple as reversing the order of the tables in the join and changing the join type. This dual nature makes them functionally similar, but with a focus on different tables in the join operation.

The query from the previous example could also be written with LEFT JOIN:

SELECT 
  Restaurants.Name, 
  Restaurants.OpeningYear, 
  Ratings.Rating, 
  Ratings.Reviewer
FROM Restaurants 
LEFT JOIN Ratings
ON Ratings.RestaurantID = Restaurants.RestaurantID;

We changed RIGHT JOIN to LEFT JOIN. We also changed the order of the tables. The query returns the same result as our RIGHT JOIN example.

Why LEFT JOIN Is More Common

In practice, LEFT JOIN is used more frequently than RIGHT JOIN. This preference is largely due to the way people naturally think about and structure their SQL queries. Typically, SQL users start with a primary table (the left table in a LEFT JOIN) and then bring in additional data from other tables as needed.

Since the left-to-right reading order aligns with how queries are often conceptualized and written, LEFT JOIN feels more intuitive. This doesn't make RIGHT JOIN any less important; it's just less commonly used due to the natural flow of SQL query development.

When to Use RIGHT JOIN

Although less common, RIGHT JOIN has its place. It's particularly useful in scenarios where your starting point is the secondary data (the right table), and you're looking to include or investigate its relationship with the primary data (the left table). Understanding both LEFT and RIGHT JOINs adds flexibility to your SQL querying skills, allowing you to approach data problems from different angles.

RIGHT JOIN Examples and Explanations

Using the Restaurants and Ratings datasets, let's switch the tables to provide examples that explain the output of RIGHT JOIN – focusing on including all restaurants, whether they have ratings or not.

Example 1: Restaurants with an Average Rating over 4.5

We want to list all restaurants that have an average rating over 4.5. We want to include their ratings (if available).

SELECT 
  Restaurants.Name, 
  AVG(Ratings.Rating) AS AverageRating
FROM Restaurants
RIGHT JOIN Ratings 
ON Restaurants.RestaurantID = Ratings.RestaurantID
GROUP BY Restaurants.Name
HAVING AVG(Ratings.Rating) > 4.5;

How It Works

• This query associates the Ratings table with the Restaurants table using a RIGHT JOIN.
• It calculates the average rating for each restaurant using the AVG()
• It filters to only include restaurants with an average rating greater than 4.5. It uses the HAVING clause for this, which is applied to aggregated results.

Result

Explanation

"Galactic Diner" is the only restaurant averaging over 4.5 in ratings. The second-highest-reviewed restaurant is “The Enchanted Fork”; because its average rating is 4.25, it is not shown in the final output.

Example 2: Restaurants Without Ratings

Objective: To list all restaurants that have not received any ratings.

SQL Query

SELECT 
  Restaurants.Name, 
  Restaurants.OpeningYear
FROM Ratings
RIGHT JOIN Restaurants 
ON Ratings.RestaurantID = Restaurants.RestaurantID
WHERE Ratings.Rating IS NULL;

How It Works

• This query uses a RIGHT JOIN to ensure all restaurants are included, even those without matching entries in the Ratings
• It filters for restaurants without any ratings by checking for NULL in the Ratings

Result

Explanation

"The Mystic Pizzeria'' appears alone here, as it is the only one that has not been rated. Maybe this is a new restaurant that doesn’t have any reviews yet. This example shows the effectiveness of RIGHT JOIN in including restaurants irrespective of their ratings.

Example 3: The Restaurant With the Most Ratings

Objective: Identify the restaurant(s) with the highest number of ratings as an indicator of popularity.

SQL Query

SELECT 
  Restaurants.Name, 
  COUNT(Ratings.RatingID) AS NumberOfRatings
FROM Ratings
RIGHT JOIN Restaurants 
ON Ratings.RestaurantID = Restaurants.RestaurantID
GROUP BY Restaurants.Name
ORDER BY NumberOfRatings DESC
LIMIT 1;

How It Works

• By performing a RIGHT JOIN, the query counts ratings for each restaurant, ensuring all restaurants are considered.
• It orders the results to highlight the restaurant with the most ratings.

Result

Explanation

"Galactic Diner" emerges as the most reviewed restaurant, indicating its popularity. This scenario uses RIGHT JOIN to return all restaurants, then leverages aggregate functions to deduce the most reviewed one.

Practical Tips for Effective RIGHT JOINs

Finally, we'll share practical tips to help you effectively use RIGHT JOINs in SQL.

Understanding the Order of Tables

• Right Table Focus: In a RIGHT JOIN, the focus is on the right table. The right table is the one that will be fully represented in the results. Ensure that the table whose data you want to retain entirely, regardless of any matching in the other table, is placed on the right side of the join.
• Visualizing the Data: Before writing your query, visualize or sketch out the tables and their relationships. This can help you clearly identify which table should be on the right.

Handling NULL Values

• Expect NULLs: In a RIGHT JOIN, rows from the right table with no matching rows in the left table will result in NULL values in the columns from the left table. Be prepared to handle these NULL values in your query, either by filtering them out or by using them in your analysis.

Combining RIGHT JOIN with Other Clauses

• Using Aggregates and GROUP BY: Aggregation functions and GROUP BY can be effectively used with RIGHT JOINs to summarize data – e.g. finding restaurants with an average rating of 4.5 or more, as shown in Example 1.
• Filter Carefully: When using WHERE clauses with RIGHT JOINs, remember that filters on the left table can exclude rows, reducing the effectiveness of the RIGHT JOIN. Filters on the right table will exclude rows from the right table; they might also impact the matching rows from the left table. This has been illustrated in Example 2, the query that shows restaurants without ratings.

Avoiding Common Mistakes with RIGHT JOIN

• Don’t Confuse It with LEFT JOIN: Be mindful not to confuse the logic of LEFT JOIN with RIGHT JOIN. While they are similar, their application and results can be significantly different due to the order of the tables.
• Check for Unintended Cartesian Products: Be cautious of unintentional Cartesian products, where you end up with a much larger result set than intended. This can happen if the join condition is incorrectly specified or omitted.

Going Further with RIGHT JOINs

The best way to get comfortable with RIGHT JOIN is through practice. Start by going over our interactive SQL JOINs course. It contains over 90 hands-on exercises to practice all the important types of SQL JOINs that are widely used in SQL, including RIGHT JOIN.

You can also download our SQL JOINs cheat sheet. This is a handy reference guide to common SQL commands, including RIGHT JOIN. It's useful when you need to quickly recall syntax and best practices, and it can significantly aid in using SQL JOINs when writing queries.

Read our articles on how to practice SQL JOINs and SQL Joins: 12 Practice Questions with Detailed Answers.

If you really want to build your SQL skills, try our All Forever plan. It gives you access to all our current and future SQL courses. Each month we publish a new SQL practice course. We have courses at all levels of SQL proficiency and in the four most popular SQL dialects.

Keep learning and exploring to unlock the full potential of SQL in your data analysis endeavors!