SQL Server - FULL JOIN Query

The FULL JOIN returns all the records all the specified tables. It includes NULL for any non-matching records.

In some databases, FULL JOIN is called FULL OUTER JOIN. It can return a very large result set because it returns all the rows from all the tables.

Syntax:

SELECT column_name(s)
FROM table1
FULL OUTER JOIN table2
ON table1.column_name = table2.column_name;

For the demo purpose, we will use the following Employee and Department tables in all examples.

Employee Table
EmpId FirstName LastName Email 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
Department Table
DeptId Name
1 'Finance'
2 'HR'
3 'Sales'

Now, look at the following FULL JOIN query.

SQL Server: RIGHT JOIN Query
SELECT emp.EmpId, emp.FirstName, dept.DeptId, dept.Name 
FROM Employee emp
FULL JOIN Department dept
ON emp.DeptId = dept.DeptId;

The above query will display the following result.

DeptId Name EmpId FirstName
1 'John' 1 'Finance'
2 'James' NULL NULL
3 'Neena' 2 'HR'
4 'Lex' 1 'Finance'
5 'Amit' NULL NULL
6 'Abdul' 2 'HR'
NULL NULL 3 'Sales'

As you can see, it fetches all the records from both tables. First, it fetches all the records from the left table Employee and then all the records from the right table Department. It includes NULL for all non-matching records from both tables.

Now, let's swap the tables and see how the result will be changed.

SQL Server: RIGHT Join Query
SELECT emp.EmpId, emp.FirstName, dept.DeptId, dept.Name 
FROM Department dept
FULL JOIN Employee emp
ON emp.DeptId = dept.DeptId;

The above query will display the following result.

EmpId FirstName DeptId Name
1 'John' 1 'Finance'
4 'Lex' 1 'Finance'
3 'Neena' 2 'HR'
6 'Abdul' 2 'HR'
NULL NULL 3 'Sales'
2 'James' NULL NULL
5 'Amit' NULL NULL

As you can see, first it displays all the records from the right table Department and then records from the left table Employee. It returns NULL for all non-matching records in both tables.

Want to check how much you know SQL Server?