SQL FULL OUTER JOIN

SQL FULL JOIN or  FULL OUTER JOIN returns rows from both left and right tables, with the matching rows from both tables where available. If there is no match, then missing side will have null values.

Full join is a combination of both LEFT JOIN and RIGHT JOIN .

Syntax

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

 

Lets look at an example of FULL OUTER JOIN  in SQL.

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) 

Using FULL OUTER JOIN

Suppose, you want to see all the records from DEPARTMENT and EMPLOYEE_INFO tables whether they matching DEPT_ID values or not. In this case you can use FULL OUTER JOIN.

FULL OUTER JOIN returns records as follows.

• Records are returned from both tables for matching values in the both tables.
• Null values are returned for records in Right table , if rows in the Right table are not matched with the rows in the Left table.
• Null values are returned for records in Left table, if rows in the Left table are not matched with the rows in the Right table.

Following statement uses FULL OUTER JOIN, and returns records for matching rows in the both tables for DEPT_ID columns, and gives null for non matching rows from both tables.

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

As you can see, last two records having null values for employee details (EMP_ID, EMP_NAME, and EMP_CODE) because DEP_ID of ‘CALL CENTER’ and ‘SALES’ do not have matching DEPT_ID in EMPLOYEE_INFO table, that’s why only the DEPARTMENT details is returned from DEPARTMENT table and null values are given to EMPOLOYEE_INFO table’s columns -EMP_ID, EMP_NAME, and EMP_CODE.

Similarly, for EMP_ID 9 and 10 DEPARTMENT details (DEPT_NAME and DEPT_CODE) having null values because DEPT_ID for these two records do have matching value in DEPARTMENT table, that’s why only EMPLOYEES details is returned from EMPLOYEE_INFO table and null values are given to DEPARTMENT table’s columns – DEPT_NAME and DEPT_CODE.

Also Read..

JOINS OVERVIEW

INNER JOIN

LEFT JOIN

RIGHT JOIN