Difference Between Right Join and Right Outer Join
Joins in a Database (SQL) are mostly used for combining data or the rows of two or more table records that are based on the same or common attribute. There are various types of Joins like Right Join, Left Join, Full Join, etc. Each join has its own syntax and data-returning capability. In this article, we will see the information about Right Join and Right Outer Join along with the example, also we will see their syntax and lastly, we will understand their differences with some unique parameters.
Right Join
Right Join in SQL is used to return all the records from the rightmost table and the matching records from the leftmost table. In some scenarios, there may be a situation where there are no marches, then it will still include the rows from the right table but it will show the NULL values for the columns that are associated with the left table.
In the context of the query, below is the syntax of the Left Join.
Syntax
SELECT columns
FROM left_table
RIGHT JOIN right_table ON
join_condition;
Now, let’s see the example of Right Join:
Example
Employee_Data Table
employee_id | employee_name | department_id |
---|---|---|
1 | Aditi | 101 |
2 | Bhakti | 102 |
3 | Vaishnavi | 101 |
Department_Data Table
department_id | department_name |
---|---|
101department_name | DBMS |
102 | Computer Network |
103 | Linux |
Query for Right Join
SELECT Employee_Data.employee_id, Employee_Data.employee_name, Department_Data.department_name
FROM Employee_Data
RIGHT JOIN Department_Data ON
Employee_Data.department_id = Department_Data.department_id;
Result
employee_id | employee_name | department_name |
---|---|---|
1 | Aditi | DBMS |
3 | Vaishnavi | DBMS |
NULL | NULL | Linux |
2 | Bhakti | Computer Network |
Explanation
In the above example, Right Join includes all the rows from the right table (Department_Data) and matched them with the corresponding rows of the left table (Employee_Data). If there is no match, then the NULL value for the column is been displayed. From the above example, the ‘Linux‘ department in the Department_Data table has no associated employee in the Employee_Table, so the NULL value is been displayed.
Right Outer Join
The Right Outer Join is mostly similar to the Right Join, and both these joins are interchangeably used. The keyword used here is “Outer“, which is also optional and doesn’t have any severe impact on the result set of the query.
In the context of the query, below is the syntax of the Right Outer Join.
Syntax
SELECT columns
FROM left_table
RIGHT OUTER JOIN right_table ON
join_condition;
Example:
Let’s consider the same tables used in the above Right Join Example:
Query for Right Outer Join
SELECT Employee_Data.employee_id, Employee_Data.employee_name, Department_Data.department_name
FROM Employee_Data
RIGHT OUTER JOIN Department_Data ON
Employee_Data.department_id = Department_Data.department_id;
Result
employee_id | employee_name | department_name |
---|---|---|
1 | Aditi | DBMS |
NULL | NULL | Linux |
2 | Bhakti | Computer Network |
3 | Vaishnavi | DBMS |
Explanation
In the above example, all the records from the right table (Department_Data) are preserved, and the matching records or data from the right-most table (Employee_Data) are included. If there is no match, then the NULL values are displayed for the columns of the left table. The department ‘DBMS‘ in the Department_Data table has the 2 employees (Aditi, Vaishnavi) associated with it, so their information is included in the result. The ‘Linux‘ department has no employees, so the NULL values are displayed in the resultant table.
Right Join V/S Right Outer Join
Parameter | Right Join | Right Outer Join |
---|---|---|
Preserved Records | All the records from the right-most table in Database are preserved using Right Join. | All the records from the right-most table in Database are preserved using Right Outer Join. |
Non-Matching Records | Non-Matching Records from the left table are excluded if the query consists of Right Join. | Non-Matching records from the left table are included, showing the NULL values for left table columns if the query consists of Right Outer Join. |
Join Keyword | The keyword used here is “RIGHT JOIN” | The keyword used here is “Right Outer Join” |
Result Focus | Right Join mainly focuses on the right table’s data and its matching records. | Right Outer Join mainly focuses on combining the right table’s data with the matching records from the left table. |
Syntax | SELECT columns FROM left_table RIGHT JOIN right_table ON join_condition; |
SELECT columns FROM left_table RIGHT OUTER JOIN right_table ON join_condition; |
FAQs: Right Join vs Right Outer Join
1. When should we use Right Join in Database?
We can use Right Join when we want to return all the records or data from the right table and its matching records from the left table in the database.
2. Specify the advantages of using Right Outer Join?
Right Outer Join consists of benefit like combining the data from both the table, which includes non-matching records from the left table, this is mainly useful in the case where we want to return all the data from the right table.
3. What actually happens to non-matching records in Right Outer Join?
Non-matching records from the left table are included in the resultant table with displaying or showing the NULL values for the left table columns.
4. Can we use Right Join for updating or deleting data in the tables?
No, Right Join is only and mainly used for returning the data from tables, it is not used for performing updation and deletion of data in tables.
Contact Us