SQL - RIGHT JOIN Query
The RIGHT JOIN is the reverse of LEFT JOIN. The RIGHT JOIN query returns all the records from the right table and matching records from the left table.
Here, the right side table is a table that comes to the right side or after the "RIGHT JOIN" phrase in the query, and the left table is a table that comes at the left side or before the "RIGHT JOIN" phrase.
The RIGHT JOIN returns NULL for all non-matching records from the left table. In some databases, it is called RIGHT OUTER JOIN.
Syntax:
SELECT column_name(s)
FROM table1
RIGHT JOIN table2
ON table1.column_name = table2.column_name;
In the above syntax, table2
is the right table and table1
is the left table.
For the demo purpose, we will use the following Employee
and Department
tables in all examples.
EmpId | FirstName | LastName | Salary | DeptId | |
---|---|---|---|---|---|
1 | 'John' | 'King' | '[email protected]' | 33000 | 1 |
2 | 'James' | 'Bond' | |||
3 | 'Neena' | 'Kochhar' | '[email protected]' | 17000 | 2 |
4 | 'Lex' | 'De Haan' | '[email protected]' | 15000 | 1 |
5 | 'Amit' | 'Patel' | 18000 | 4 | |
6 | 'Abdul' | 'Kalam' | '[email protected]' | 25000 | 2 |
DeptId | Name |
---|---|
1 | 'Finance' |
2 | 'HR' |
3 | 'Sales' |
Now, look at the following RIGHT JOIN query.
SELECT dept.DeptId, dept.Name, emp.empid, emp.FirstName
FROM Employee emp
RIGHT JOIN Department dept
ON emp.DeptId = dept.DeptId;
The above RIGHT JOIN query joins the Employee
table and Department
table where Employee
is the left table and Department
is the right table.
The above query will display the following result.
DeptId | Name | EmpId | FirstName |
---|---|---|---|
1 | 'Finance' | 1 | 'John' |
1 | 'Finance' | 4 | 'Lex' |
2 | 'HR' | 3 | 'Neena' |
2 | 'HR' | 6 | 'Abdul' |
3 | 'Sales' | NULL | NULL |
As you can see, it fetches all the records from the Department
table and only matching records from the Employee
table where emp.DeptId = dept.DeptId
. It contains the 3, 'Sales'
record from the Department
table and NULL for Employee
columns because there are no matching records in the Employee
table whose DeptId
is 3.
Now, let's change the right table and see how the result will be changed.
SELECT emp.empid, emp.FirstName, dept.DeptId, dept.Name
FROM Department dept
RIGHT JOIN Employee emp
ON dept.DeptId = emp.DeptId;
Above, the left table is Department
and the right table is the Employee
. This query will display the following result.
EmpId | FirstName | DeptId | Name |
---|---|---|---|
1 | 'John' | 1 | 'Finance' |
2 | 'James' | NULL | NULL |
3 | 'Neena' | 2 | 'HR' |
4 | 'Lex' | 1 | 'Finance' |
5 | 'Amit' | NULL | NULL |
6 | 'Abdul' | 2 | 'HR' |
As you can see, it dispays all the records from the right table Employee
and matching rows from the left table Department
.
It returns NULL for all non-matching records from the Employee
table.