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.

Now, look at the following FULL 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.

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.

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.

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.