Right Join in SQL - Scaler Topics

The RIGHT JOIN in SQL returns all the records from the table to the right of the JOIN keyword. The resultant table also includes the matched records from the left table, but if no records match in both tables, it returns NULL.

For example, you have two tables, Table X and Table Y. If the RIGHT JOIN in SQL is applied to the two tables, the resultant table includes all the records from Table Y (because it is on the right side of JOIN) and also the matched records from Table X and the rows without a match returns NULL. The Venn diagram below shows how the RIGHT JOIN works on Table X and Table Y.

diagram-representation-of-right-join-in-sql

Syntax

Let's see the syntax of the RIGHT JOIN in SQL.

SQL RIGHT Join Example

Consider the two tables, Employee and Departments table, using which the RIGHT JOIN is explained in the below example.

Employee:

ID	name	city	date_of_joining	salary	dept_id
101	Kunal	Rajasthan	2022 - 02 - 26	40000	1
104	Savio	Jaipur	2022 - 04 - 19	50000	4
109	Ronak	Mumbai	2022 - 01 - 30	60000	2
112	Mitali	Jaipur	2022 - 02 - 15	30000	3
143	Kavish	Delhi	2022 - 05 - 23	55000	1
132	Yaksh	Ahmedabad	2022 - 06 - 11	35000	5

Department:

dept_id	dept_name	dept_strength
1	Finance	10
2	Marketing	3
3	IT	20
4	HR	4
5	Sales	5
6	Product	2

Example - 1

Now, you want to fetch the records of all the departments in the company and the details of the employees currently working in the departments.

To achieve this, you can use RIGHT JOIN in SQL. The below SQL query is used to retrieve all the departments available in the company, along with the employee details.

Syntax:

Output:

ID	name	city	date_of_joining	salary	dept_id	dept_name
101	Kunal	Rajasthan	2022 - 02 - 26	40000	1	Finance
143	Kavish	Delhi	2022 - 05 - 23	55000	1	Finance
109	Ronak	Mumbai	2022 - 01 - 30	60000	2	Marketing
112	Mitali	Jaipur	2022 - 02 - 15	30000	3	IT
104	Savio	Jaipur	2022 - 04 - 19	50000	4	HR
132	Yaksh	Ahmedabad	2022 - 06 - 11	35000	5	Sales
NULL	NULL	NULL	NULL	NULL	6	Product

As shown in the above example, the RIGHT JOIN in SQL includes all the records from the table on the right side of the JOIN, i.e. Department table. Notice that there is no match of the dept_id number 6 from the Employee table. Still, the department of Product is included in the final result.

Example - 2

Now, let’s take another example in which you’re provided with the Products table and Suppliers table. The SQL query fetches the details of all the suppliers supplying multiple products to the vendor, and the Product's price value is greater than 100.

Products:

product_id	product_name	supplier_id	price
1	Blueberry Sauce	101	300
2	Jumbo Spice Bread	501	200
3	Apple Cider Vinegar	101	500
4	Apple Pie	401	330
5	Cheese Cake	301	500
6	Strawberry Milkshake	101	250
7	Fresh Watermelon juice	501	400

Suppliers:

supplier_id	supplier_name	city	phone
101	Foodex	Mumbai	9112 838 727
501	Food Fiery	Delhi	9132 232 124
401	Feed Bear	Pune	9575 124 252
330	IndustryBea	Chennai	9657 673 562
601	Feed Dusk	Ahmedabad	9456 213 685
701	Foodonus	Delhi	9346 828 239

Syntax:

Output:

product_id	product_name	supplier_id	price	supplier_name	city	phone
1	Blueberry Sauce	101	300	Foodex	Mumbai	9112 838 727
3	Apple Cider Vinegar	101	500	Foodex	Mumbai	9112 838 727
6	Strawberry Milkshake	101	250	Foodex	Mumbai	9112 838 727
2	Jumbo Spice Bread	501	200	Food Fiery	Delhi	9132 232 124
7	Fresh Watermelon juice	501	400	Food Fiery	Delhi	9132 232 124
4	Apple Pie	401	330	Feed Bear	Pune	9575 124 252
NULL	NULL	330	NULL	IndustryBea	Chennai	9657 673 562
NULL	NULL	601	NULL	Feed Dusk	Ahmedabad	9456 213 685
NULL	NULL	701	NULL	Foodonus	Delhi	9346 828 239

In the above example, the details of all the suppliers are fetched as the products that they deliver to the vendor. Note that a single supplier delivers multiple products; hence, all the matching records from the Suppliers table with the Products table are retrieved.

Conclusion

The RIGHT JOIN combines two or more tables in SQL.
The RIGHT JOIN in SQL returns all the records from the right table, i.e. table A, and matching records from the left table, i.e. table B.
If a row in the right table does not match the left table, then the resultant record of the left table displays NULL.
RIGHT JOIN and RIGHT OUTER JOIN in SQL are the same. The OUTER keyword is optional.
In a JOIN query, the table that appears to the left of JOIN or that appears leftmost in the JOIN clause is the left table, and the right table is the one that appears to the right of the JOIN.
The RIGHT JOIN is opposite to that of LEFT JOIN. It returns all the rows from the right table and rows of the left table for which the JOIN condition is satisfied.