SQL RIGHT JOIN

SQL RIGHT JOIN or RIGHT OUTER JOIN returns all rows from the right table and the matching rows from the left table.

If a row in the right table does not have any matching row in the left table then columns of the left table will have nulls value.

Syntax

SELECT COLUMN(S),... 
FROM TABLE T1 RIGHT OUTER JOIN TABLE T2  
ON T1.COLUMN = T2. COLUMN  
WHERE CONDITIONS..

Lets look at an example of RIGHT OUTER JOIN  in SQL Server.

CREATE TABLE dbo.DEPARTMENT
(DEPT_ID INT PRIMARY KEY,
DEPT_NAME VARCHAR(50),
DEPT_CODE VARCHAR(15))

INSERT INTO dbo.DEPARTMENT
(DEPT_ID, DEPT_NAME, DEPT_CODE)
VALUES(501, 'IT', '001'),
(502, 'FINANCE', '002'),
(503, 'HR', '003'),
(504, 'MIS', '004'),
(505, 'CALL CENTER', '005'),
(506, 'SALES','006')

CREATE TABLE dbo.EMPLOYEE_INFO 
(EMP_ID INT PRIMARY KEY, 
EMP_NAME VARCHAR(50), 
EMP_CODE VARCHAR(15),
DEPT_ID INT REFERENCES dbo.DEPARTMENT(DEPT_ID)
)  

INSERT INTO dbo.EMPLOYEE_INFO (EMP_ID, EMP_NAME, EMP_CODE, DEPT_ID )
VALUES (1, 'MANISH SINGH', 'EN-0034', 501),
(2, 'MANOJ RAWAT', 'EN-0035', 501),
(3, 'RAJESH KUMAWAT', 'EN-0036', 501),
(4, 'SURAJ K', 'EN-0037', 502),
(5, 'RUPESH SINGH', 'EN-0038', 502),
(6, 'ARJUN PAL', 'EN-0039', 503),
(7, 'GURPREET KAUR', 'EN-0040', 504),
(8, 'TRILOK KUMAR', 'EN-0041', 504),
(9, 'RANUK KHAN', 'EN-0041', null),
(10, 'MANDEEP GREWAL', 'EN-0041', null) 

 

 

BASIC USE OF RIGHT OUTER JOIN

Suppose, you want to see the total number of employees in each department that you have in your DEPARTMENT table.

So, to get the department wise employee count, you can use RIGHT OUTER JOIN that will return all the records from RIGHT table (DEPARTMENT) for corresponding matching value with LEFT table (EMPLOYEE_INTFO) and returns all the department details along with employee counts in each department.

It returns null if department does not have any employee.

Generally, all the records are returned from the table that will be on RIGHT side of RIGHT OUTER JOIN clause, and null values are returned for left side table for non matching rows.

SELECT DEPT.DEPT_ID, DEPT.DEPT_NAME, DEPT.DEPT_CODE, 
COUNT(EMP.DEPT_ID) AS EMP_COUNT
FROM dbo.EMPLOYEE_INFO EMP RIGHT OUTER JOIN dbo.DEPARTMENT DEPT
ON EMP.DEPT_ID = DEPT.DEPT_ID
GROUP BY DEPT.DEPT_ID, DEPT.DEPT_NAME, DEPT.DEPT_CODE

 

As you can see, employee count in DEPARTMENT ‘CALL CENTER’ and ‘SALES’ is 0 as there is no matching rows for DEPT_ID column in EMPLOYEE_INFO table.

So, it returns all department details in the result set from DEPARTMENT table (Right table) with the total number of employees count for matching rows in the left table (EMPLOYEE) for DEPT_ID column in both tables, and returns null for non matching  rows in the right table.

If you to want see employees details then you can simply remove aggregation from above statement as given below.

SELECT DEPT.DEPT_ID, DEPT.DEPT_NAME, DEPT.DEPT_CODE, 
EMP.EMP_NAME, EMP_CODE 
FROM dbo.EMPLOYEE_INFO EMP RIGHT OUTER JOIN 
dbo.DEPARTMENT DEPT
ON EMP.DEPT_ID = DEPT.DEPT_ID

 

Also Read..

JOINS OVERVIEW

INNER JOIN

LEFT JOIN

FULL JOIN