SQL RIGHT JOIN
- Join operation in SQL is used to combine multiple tables together into a single table.
- If we use the right join to combine two different tables, then we will get all the records from the right table. But we will get only those records from the left table, which have the corresponding key in the right table. Rest other records in the left table for which the common column value doesn't match with the common column value of the right table; displayed as NULL.
- Let us take a look at the syntax of writing a query to perform the right join operation in SQL.
-
SELECT TableName1.columnName1, TableName2.columnName2 FROM TableName1
RIGHT JOIN TableName2 ON TableName1.ColumnName = TableName2.ColumnName;
Now let us see take a deeper dive into the right join in SQL with the help of examples. All the queries in the examples will be written using the MySQL database.
Consider we have the following tables with the given data:
Table 1: employee
| EmployeeID | Employee_Name | Employee_Salary |
|---|---|---|
| 1 | Arun Tiwari | 50000 |
| 2 | Sachin Rathi | 64000 |
| 3 | Harshal Pathak | 48000 |
| 4 | Arjun Kuwar | 46000 |
| 5 | Sarthak Gada | 62000 |
| 6 | Saurabh Sheik | 53000 |
| 7 | Shubham Singh | 29000 |
| 8 | Shivam Dixit | 54000 |
| 9 | Vicky Gujral | 39000 |
| 10 | Vijay Bose | 28000 |
Table 2: department
| DepartmentID | Department_Name | Employee_ID |
|---|---|---|
| 1 | Production | 1 |
| 2 | Sales | 3 |
| 3 | Marketing | 4 |
| 4 | Accounts | 5 |
| 5 | Development | 7 |
| 6 | HR | 9 |
| 7 | Sales | 10 |
Table 3: Loan
| LoanID | Branch | Amount |
|---|---|---|
| 1 | B1 | 15000 |
| 2 | B2 | 10000 |
| 3 | B3 | 20000 |
| 4 | B4 | 100000 |
| 5 | B5 | 150000 |
| 6 | B6 | 50000 |
| 7 | B7 | 35000 |
| 8 | B8 | 85000 |
Table 4: Borrower
| CustID | CustName | LoanID |
|---|---|---|
| 1 | Sonakshi Dixit | 1 |
| 2 | Shital Garg | 4 |
| 3 | Swara Joshi | 5 |
| 4 | Isha Deshmukh | 2 |
| 5 | Swati Bose | 7 |
| 6 | Asha Kapoor | 10 |
| 7 | Nandini Shah | 9 |
Table 5: customer
| Customer_ID | Name | Age | Salary |
|---|---|---|---|
| 1 | Aryan Jain | 51 | 56000 |
| 2 | Arohi Dixit | 21 | 25000 |
| 3 | Vineet Garg | 24 | 31000 |
| 4 | Ajeet Singh | 23 | 32000 |
| 5 | Ravi Rathod | 23 | 42000 |
| 6 | Paras Aggrawal | 22 | 50000 |
| 7 | Sonakshi Kapadiya | 24 | 28000 |
| 8 | Sonali Kapoor | 28 | 82000 |
Table 6: orders
| Order_ID | Order_Date | Cutomer_ID | Amount |
|---|---|---|---|
| 1 | 2012-01-20 | 2 | 3000 |
| 2 | 2012-05-18 | 2 | 2000 |
| 3 | 2012-06-28 | 3 | 4000 |
| 4 | 2012-04-11 | 4 | 5000 |
| 5 | 2012-05-04 | 8 | 8000 |
Example 1:
Write a query to perform a right join operation considering the employee table as the left table and the department table as the right table.
Query:
We have used the SELECT command to retrieve EmployeeID, Employee_Name, Employee_Salary, DepartmentID, Department_Name present in the employee and department table. Then we have used the RIGHT JOIN keyword to perform the right join operation on the employee and department table where 'e' and 'd' are aliases. These two tables are joined on the column EmployeeID which is present in both the tables.
You will get the following output:
| EmployeeID | Employee_Name | Employee_Salary | DepartmentID | Department_Name |
|---|---|---|---|---|
| 1 | Arun Tiwari | 50000 | 1 | Production |
| 3 | Harshal Pathak | 48000 | 2 | Sales |
| 4 | Arjun Kuwar | 46000 | 3 | Marketing |
| 5 | Sarthak Gada | 62000 | 4 | Accounts |
| 7 | Shubham Singh | 29000 | 5 | Development |
| 9 | Vicky Gujral | 39000 | 6 | HR |
| 10 | Vijay Bose | 28000 | 7 | Sales |
Example 2:
Write a query to perform the right join operation considering the loan table as the left table and the borrower table as the right table.
Query:
We have used the SELECT command to retrieve LoanID, Branch, Amount, CustID, CustName present in the loan and borrower table. Then we have used the RIGHT JOIN keyword to perform the right join operation on the loan and borrower table where 'l' and 'b' are aliases. These two tables are joined on the column LoanID which is present in both the tables.
You will get the following output:
| LoanID | Branch | Amount | CustID | CustName |
|---|---|---|---|---|
| 1 | B1 | 15000 | 1 | Sonakshi Dixit |
| 4 | B4 | 100000 | 2 | Shital Garg |
| 5 | B5 | 150000 | 3 | Swara Joshi |
| 2 | B2 | 10000 | 4 | Isha Deshmukh |
| 7 | B7 | 35000 | 5 | Swati Bose |
| NULL | NULL | NULL | 6 | Asha Kapoor |
| NULL | NULL | NULL | 7 | Nandini Shah |
LoanID, Branch, Amount, CustID, CustName are retrieved from loan and borrower tables. All the records from the borrower table are retrieved. Only those records that have a corresponding LoanID in the borrower table are retrieved from the loan table. Rest other records in the loan table for which a LoanID doesn't match with the LoanID of the borrower table, are displayed as NULL.
Example 3:
Write a query to perform a right join operation considering the customer table as the left table and the orders table as the right table.
Query:
We have used the SELECT command to retrieve Customer_ID, Name, Age, Salary, Order_ID, Order_Date, Amount present in customer and orders table. Then we have used the RIGHT JOIN keyword to perform the right join operation on the customer and orders table where 'c' and 'o' are aliases. These two tables are joined on the column Customer_ID which is present in both the tables.
You will get the following output:
| Customer_
ID |
Name | Age | Salary | Order_ID | Order_Date | Amount |
|---|---|---|---|---|---|---|
| 2 | Arohi Dixit | 21 | 25000 | 1 | 2012-01-20 | 3000 |
| 2 | Arohi Dixit | 21 | 25000 | 2 | 2012-05-18 | 2000 |
| 3 | Vineet Garg | 24 | 31000 | 3 | 2012-06-28 | 4000 |
| 4 | Ajeet Singh | 23 | 32000 | 4 | 2012-04-11 | 5000 |
| 8 | Sonali Kapoor | 28 | 82000 | 5 | 2012-05-04 | 8000 |
Customer_ID, Name, Age, Salary, Order_ID, Order_Date, Amount are retrieved from customer and orders tables. All the records from the orders table are retrieved. From the customer table, only those records which have a corresponding Customer_ID in the orders table are retrieved.
