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.
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.
Here we have two tables named as Department and Emoloyee_Info.
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 coressponding matching value with LEFT table (EMPLOYEE_INTFO) and returns all the department details wise employee count and 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 ,in case of non match 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 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
Recommended for you