When working with relational databases, the RIGHT JOIN (or RIGHT OUTER JOIN) is another powerful tool for combining data from multiple tables. While the LEFT JOIN retrieves all rows from the left table, the RIGHT JOIN focuses on the right table, including all its records, whether or not they have matches in the left table.

This guide explains how the RIGHT JOIN works using the same example datasets we've explored earlier.

What is a RIGHT JOIN?

A RIGHT JOIN retrieves all rows from the "right" table (the second table in the query) and only the matching rows from the "left" table (the first table in the query). Rows from the right table without matches in the left table are still included, but the columns from the left table return NULL.

Syntax of RIGHT JOIN

The general syntax for a RIGHT JOIN is:

sql code

SELECT column_name(s) FROM table1 RIGHT JOIN table2 ON table1.common_column = table2.common_column;

• table1: The left table, whose data is included only if there's a match in the right table.
• table2: The right table, from which all rows are included.
• common_column: The column used to establish the relationship between the two tables.

Dataset

Let’s revisit the Customers and Orders tables:

Customers Table

Orders Table

Scenario

We want to find all orders, along with the corresponding customer details. If an order doesn’t have a matching customer, the customer details should show as NULL.

SQL Query

Here’s how to use a RIGHT JOIN to achieve this:

sql code

SELECT Orders.OrderID, Orders.CustomerID, Orders.OrderAmount, Customers.CustomerName, Customers.Country

FROM Customers

RIGHT JOIN Orders ON Customers.CustomerID = Orders.CustomerID;

Result

Explanation

• Order 101 and Order 102: These orders have matching customers in the Customers table, so the customer details are included.
• Order 103: This order doesn’t have a corresponding CustomerID in the Customers table. As a result, the CustomerName and Country columns return NULL.

Key Points About RIGHT JOIN

1. All Rows from the Right Table: Every row from the right table (Orders) is included, even if there’s no match in the left table (Customers).
2. NULL for Missing Matches: If no corresponding row exists in the left table, columns from the left table return NULL.
3. Complementary to LEFT JOIN: A RIGHT JOIN gives similar results to a LEFT JOIN but with the roles of the tables reversed.

Conclusion

The RIGHT JOIN is a versatile SQL tool for combining tables while prioritizing data from the right table. It is especially useful for identifying gaps or mismatches in relational datasets. Understanding how and when to use RIGHT JOIN is crucial for effective data analysis and reporting.