Learning Objective

The objective of this SQL Server tutorial is to teach you how use a RIGHT JOIN to return all records from the right table and matching records from the left table.

What is RIGHT JOIN in SQL Server?

A RIGHT JOIN (also called RIGHT OUTER JOIN) is one of the 5 types of JOINS and a type of OUTER JOIN among the 3 outer JOINS available in SQL to fetch and combine columns from different related tables. In a RIGHT JOIN between two tables the JOIN resultset consists of all right table rows but only matching left table which match the specified JOIN condition. The JOIN condition is specified on a common column (i.e. column holding the same data even though the column names might be different in the participating tables). Rows in the left table which do not match the right table on the JOIN condition show NULL values in their columns. A RIGHT JOIN can be pictorially represented as below.


A RIGHT JOIN can be used in all the query types i.e. SELECT, INSERT, UPDATE and DELETE. A SQL query can contain multiple RIGHT JOINS and a RIGHT JOIN can be combined with other types of JOINS like say INNER JOIN etc.

SQL Server RIGHT JOIN Syntax

The basic syntax of SQL Server RIGHT JOIN clause is as follows.

SELECT column_list
FROM table1
ON table1.columnX=table2.columnX;

In this syntax,

  • column_list – the list of columns from the participating tables in the SELECT statement.
  • table1 – the first or left table.
  • table2 – the second or right table.
  • RIGHT OUTER JOIN/RIGHT JOIN – SQL keyword combination to implement a RIGHT JOIN (where the resultset contains all records from the right table but only matching records from the left table).
  • columnX – column common to both tables on which the JOIN is made.

SQL Server RIGHT JOIN Example

Let us see a practical example of the RIGHT JOIN.

Suppose we have an electronics store and the store database has 2 tables – categories and products. The categories table contains some category names to which products belong and the products table contains the names of products belonging to one or another category. The tables are represented below. We will use them as our reference for the example.

Table – categories
10272Bose Noise Cancelling Headphone 7005/13/2019
10282Sennheiser HD 450BT2/4/2020
10292Sony WH-1000XM38/15/2018
10302SoundMagic ES181/1/2017
10211Apple iPhone 11 Pro9/20/2019
10221Samsung Galaxy Note 10 Plus8/23/2019
10353Samsung Galaxy Tab S610/11/2019
10363Microsoft Surface Pro6/15/2017
10373iPad Air3/18/2019
10383Lenovo Tab M88/8/2019
10393Dell Venue 71/4/2014
10403HP 7 VoiceTab10/23/2014
Table – Products

We will do a RIGHT JOIN on the above tables on the category_id column present in both tables. The below query does the same. p and c are table aliases for the products and categories tables.

FROM products p
RIGHT JOIN categories c
ON p.category_id = c.category_id;

The query will generate the following output. We can see that the last 3 rows from the right table categories do not have any corresponding values in the columns of the left table products. That is because there is no product with product_id 4, 5 or 6 in the products table (which from a practical point of view basically means that there are no products for those categories in the store at present.)

10211Apple iPhone 11 Pro9/20/20191Mobile
10221Samsung Galaxy Note 10 Plus8/23/20191Mobile
10272Bose Noise Cancelling Headphone 7005/13/20192Headphone
10282Sennheiser HD 450BT2/4/20202Headphone
10292Sony WH-1000XM38/15/20182Headphone
10302SoundMagic ES181/1/20172Headphone
10353Samsung Galaxy Tab S610/11/20193Tablet
10363Microsoft Surface Pro6/15/20173Tablet
10373iPad Air3/18/20193Tablet
10383Lenovo Tab M88/8/20193Tablet
10393Dell Venue 71/4/20143Tablet
10403HP 7 VoiceTab10/23/20143Tablet

We can limit the output and make it more pragmatic by specifying specific columns whose information is what we actually require. The following RIGHT JOIN query does the same. It specifies only product_name and category_name in the SELECT column list.

SELECT product_name, category_name
FROM products p
RIGHT JOIN categories c
ON p.category_id = c.category_id;

The query will generate the following output listing categories against products which belong to those categories.

Apple iPhone 11 ProMobile
Samsung Galaxy Note 10 PlusMobile
Bose Noise Cancelling Headphone 700Headphone
Sennheiser HD 450BTHeadphone
Sony WH-1000XM3Headphone
SoundMagic ES18Headphone
Samsung Galaxy Tab S6Tablet
Microsoft Surface ProTablet
iPad AirTablet
Lenovo Tab M8Tablet
Dell Venue 7Tablet
HP 7 VoiceTabTablet