Right Join in SQL Server

In this tutorial, you will learn how to use the SQL Server RIGHT JOIN clause to query data from two tables.

Introduction to the SQL Server RIGHT JOIN clause

The RIGHT JOIN is a clause of the SELECT statement. The RIGHT JOIN clause combines data from two or more tables.

The RIGHT JOIN clause starts selecting data from the right table and matching it with the rows from the left table. The RIGHT JOIN returns a result set that includes all rows in the right table, whether or not they have matching rows from the left table.

If a row in the right table does not have any matching rows from the left table, the column of the left table in the result set will have nulls.

The following shows the syntax of the RIGHT JOIN clause:

SELECT 
    select_list
FROM 
    T1
RIGHT JOIN T2 ON join_predicate;

In this syntax, T1 is the left table and T2 is the right table.

Note that RIGHT JOIN and RIGHT OUTER JOIN is the same. The OUTER keyword is optional.

SQL Server RIGHT JOIN example

We will use the sales.order_items and production.products table from the sample database for the demonstration.

The following statement returns all order_id from the sales.order_items and product name from the production.products table:

SELECT
    product_name,
    order_id
FROM
    sales.order_items o
    RIGHT JOIN production.products p 
        ON o.product_id = p.product_id
ORDER BY
    order_id;

The query returned all rows from the production.products table (right table) and rows from sales.order_items table (left table). If a product does not have any sales, the order_id column will have a null.

To get the products that do not have any sales, you add a WHERE clause to the above query to filter out the products that have sales:

SELECT
    product_name,
    order_id
FROM
    sales.order_items o
    RIGHT JOIN production.products p 
        ON o.product_id = p.product_id
WHERE 
    order_id IS NULL
ORDER BY
    product_name;