Next Chapter »

SQL Tutorial - Right Join


'Right Join' is a type of join that displays all the rows from the right side table along with the matching data of the left side table.

The Right Join


The 'Right Join 'is a type of outer join. It is also known as RIGHT OUTER JOIN. This join retrieves all the records from the right table in conjunction with all the matching rows from the left table. Null values are returned for the left table values where there are no matching records.

SQL 'Right Join' Syntax


Select tableA.column1,tableA.column-2,tableA.column-3,...tableA.column-n,tableB.column1,
tableB.column2,.....tableB.columnn
from table A
RIGHT JOIN tableB on tableA.columnname= tableB.columnname

The Right Join Example


Here let us assume that the 'Employees' table is first table and placed on the left side.The 'Employees' table contains the following columns and their respective data as shown below.

Select EmployeeID,EmployeeName,DateOfBirth,DesignationID,DeptID,City,Salary
from Employees

EmployeeID EmployeeName DateOfBirth DesignationID DeptID City Salary
1 Richard Hughes 4/23/1945 12:00:00 AM 1 1 New Orleans 950.00
2 Taryn Sinclair 3/22/1980 12:00:00 AM 2 2 San Francisco 800.00
3 Ted Horowitz 3/31/1960 12:00:00 AM 3 4 New York 700.00
4 Jonathan Douglas 7/7/1971 12:00:00 AM 3 4 Salt Lake City 600.00
5 Miranda Leigh 7/19/1983 12:00:00 AM 7 3 New York 500.00
6 Jana Rae 9/23/1976 12:00:00 AM 8 2 Houston 400.00
7 Lita Rosanna 9/14/1982 12:00:00 AM 4 6 Long Island 650.00
8 Colin Flooks 12/29/1988 12:00:00 AM 4 2 Salt Lake City 600.00
9 Anthony Frank 2/22/1988 12:00:00 AM 9 3 San Francisco 750.00
10 Stephanie Lynn 7/30/1979 12:00:00 AM 8 2 Salt Lake City 450.00
11 Jack Martin 8/25/1985 12:00:00 AM NULL NULL Austin 500.00

The Department table now becomes the second table or goes on the right side. The 'Department' tables contains the following columns and their respective data as shown below.

Select DeptID, DeptName
from Department

DeptID DeptName
1 Finance
2 Development
3 Testing
4 Delivery
5 Human Resources
6 Administration

Select A.EmployeeID,A.EmployeeName,A.DateOfBirth,A.DesignationID,A.City,
A.Salary ,B.DeptID, B.DeptName
from Employees A
Right Join Department B on A.DeptID=B.DeptID

EmployeeID EmployeeName DateOfBirth DesignationID City Salary DeptID DeptName
1 Richard Hughes 4/23/1945 12:00:00 AM 1 New Orleans 950.00 1 Finance
2 Taryn Sinclair 3/22/1980 12:00:00 AM 2 San Francisco 800.00 2 Development
6 Jana Rae 9/23/1976 12:00:00 AM 8 Houston 400.00 2 Development
8 Colin Flooks 12/29/1988 12:00:00 AM 4 Salt Lake City 600.00 2 Development
10 Stephanie Lynn 7/30/1979 12:00:00 AM 8 Salt Lake City 450.00 2 Development
5 Miranda Leigh 7/19/1983 12:00:00 AM 7 New York 500.00 3 Testing
9 Anthony Frank 2/22/1988 12:00:00 AM 9 San Francisco 750.00 3 Testing
3 Ted Horowitz 3/31/1960 12:00:00 AM 3 New York 700.00 4 Delivery
4 Jonathan Douglas 7/7/1971 12:00:00 AM 3 Salt Lake City 600.00 4 Delivery
NULL NULL NULL NULL NULL NULL 5 Human Resources
7 Lita Rosanna 9/14/1982 12:00:00 AM 4 Long Island 650.00 6 Administration

Note: The Right Join is the opposite of the Left Outer Join. Here all the records of the 'Department' table are taken and matching records from left table records. Since there are no columns matching in the left table for 'Human Resources', it returns NULL values in the left table.


Next Chapter »