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.