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
Table – categories
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
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.
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.
