Right Join
Learn via video course
FreeWhat is a Right Join in SQL?
As you already know, the term JOIN combines the records of two or more tables. In this section, you will learn about one of the 3 forms of OUTER JOINs, i.e. "RIGHT JOIN in SQL".
The RIGHT JOIN in SQL returns all the records from the table that is 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 below Venn diagram shows how the RIGHT JOIN works on Table X and Table Y.
Let's see the syntax of the RIGHT JOIN in SQL.
The RIGHT JOIN in SQL is also called as RIGHT OUTER JOIN.
Why Use the Right Join in SQL?
The RIGHT JOIN in SQL is used when you want to JOIN the records of the tables. When you want to return all the rows from one table and only the matched rows from the other tables, then also RIGHT JOIN is used.
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.
Let's take an example to understand why and where to use the right join in SQL.
Customers:
| cust_id | name | city | |
|---|---|---|---|
| 101 | Surbhi Shah | surbhi@hello | Ahmedabad |
| 104 | Meet Velani | meet@google | Nagpur |
| 108 | Raj Singh | rajsingh@yahoo | Mumbai |
| 112 | Shreya Vaghela | shreya@google | Bangalore |
| 115 | Ritik Sharma | ritik@yahoo | Pune |
Orders:
| order_id | items | amount | customer |
|---|---|---|---|
| 1 | 100 | 10000 | 104 |
| 2 | 50 | 2000 | 105 |
| 3 | 60 | 4000 | 108 |
| 4 | 55 | 6600 | 115 |
| 5 | 40 | 4000 | 107 |
SQL:
Output:
| cust_id | name | city | order_id | items | amount |
|---|---|---|---|---|---|
| 104 | Meet Velani | Nagpur | 1 | 100 | 10000 |
| 108 | Raj Singh | Mumbai | 3 | 60 | 4000 |
| 115 | Ritik Sharma | Pune | 4 | 55 | 6600 |
| NULL | NULL | NULL | 2 | 50 | 2000 |
| NULL | NULL | NULL | 5 | 40 | 4000 |
As shown in the output, the RIGHT JOIN in SQL will return the result set of those rows where there is a match between cust_id of the Customers table and customer of the Orders table along with all the remaining rows from the table which is present at the right of the JOIN i.e. Orders table.
Thus to retrieve such results, you can use the RIGHT JOIN in SQL. This is also possible when you swap the tables, and instead of RIGHT JOIN, use the LEFT JOIN to get the same results.
Example of Right Join in SQL
Now, let's take some examples to understand the working of RIGHT JOIN in SQL.
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 that are available in the company, along with the employee details.
SQL:
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 that is present 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.
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 |
SQL:
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, and hence all the matching records from the Suppliers table with the Products table are retrieved.
Learn More About Join in SQL
- To learn more about JOIN in SQL and various types of JOINs available in SQL, refer to Scaler Topics - JOINS in SQL
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 with 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.