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.
Operation
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 RIGHT JOIN table2 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.
| category_id | category_name |
| 1 | Mobile |
| 2 | Headphone |
| 3 | Tablet |
| 4 | Laptop |
| 5 | Notebook |
| 6 | Phablet |
| product_id | category_id | product_name | release_date |
| 1027 | 2 | Bose Noise Cancelling Headphone 700 | 5/13/2019 |
| 1028 | 2 | Sennheiser HD 450BT | 2/4/2020 |
| 1029 | 2 | Sony WH-1000XM3 | 8/15/2018 |
| 1030 | 2 | SoundMagic ES18 | 1/1/2017 |
| 1021 | 1 | Apple iPhone 11 Pro | 9/20/2019 |
| 1022 | 1 | Samsung Galaxy Note 10 Plus | 8/23/2019 |
| 1035 | 3 | Samsung Galaxy Tab S6 | 10/11/2019 |
| 1036 | 3 | Microsoft Surface Pro | 6/15/2017 |
| 1037 | 3 | iPad Air | 3/18/2019 |
| 1038 | 3 | Lenovo Tab M8 | 8/8/2019 |
| 1039 | 3 | Dell Venue 7 | 1/4/2014 |
| 1040 | 3 | HP 7 VoiceTab | 10/23/2014 |
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.
SELECT * 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.)
| product_id | category_id | product_name | release_date | category_id | category_name |
| 1021 | 1 | Apple iPhone 11 Pro | 9/20/2019 | 1 | Mobile |
| 1022 | 1 | Samsung Galaxy Note 10 Plus | 8/23/2019 | 1 | Mobile |
| 1027 | 2 | Bose Noise Cancelling Headphone 700 | 5/13/2019 | 2 | Headphone |
| 1028 | 2 | Sennheiser HD 450BT | 2/4/2020 | 2 | Headphone |
| 1029 | 2 | Sony WH-1000XM3 | 8/15/2018 | 2 | Headphone |
| 1030 | 2 | SoundMagic ES18 | 1/1/2017 | 2 | Headphone |
| 1035 | 3 | Samsung Galaxy Tab S6 | 10/11/2019 | 3 | Tablet |
| 1036 | 3 | Microsoft Surface Pro | 6/15/2017 | 3 | Tablet |
| 1037 | 3 | iPad Air | 3/18/2019 | 3 | Tablet |
| 1038 | 3 | Lenovo Tab M8 | 8/8/2019 | 3 | Tablet |
| 1039 | 3 | Dell Venue 7 | 1/4/2014 | 3 | Tablet |
| 1040 | 3 | HP 7 VoiceTab | 10/23/2014 | 3 | Tablet |
| NULL | NULL | NULL | NULL | 4 | Laptop |
| NULL | NULL | NULL | NULL | 5 | Notebook |
| NULL | NULL | NULL | NULL | 6 | Phablet |
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.
| product_name | category_name |
| Apple iPhone 11 Pro | Mobile |
| Samsung Galaxy Note 10 Plus | Mobile |
| Bose Noise Cancelling Headphone 700 | Headphone |
| Sennheiser HD 450BT | Headphone |
| Sony WH-1000XM3 | Headphone |
| SoundMagic ES18 | Headphone |
| Samsung Galaxy Tab S6 | Tablet |
| Microsoft Surface Pro | Tablet |
| iPad Air | Tablet |
| Lenovo Tab M8 | Tablet |
| Dell Venue 7 | Tablet |
| HP 7 VoiceTab | Tablet |
| NULL | Laptop |
| NULL | Notebook |
| NULL | Phablet |