Full outer join in sql server

Introduction to SQL Server full outer join

The FULL OUTER JOIN returns a result set that includes rows from both left and right tables. When no matching rows exist for the row in the left table, the columns of the right table will have nulls. Similarly, when no matching rows exist for the row in the right table, the column of the left table will have nulls.
The following shows the syntax of the FULL OUTER JOIN when joining two tables T1 and T2:
The OUTER keyword is optional so you can skip it as shown in the following query:
In this syntax:
  • First, specify the left table T1 in the FROM clause.
  • Second, specify the right table T2 and a join predicate.
The following Venn diagram illustrates the FULL OUTER JOIN of two result sets:
SQL Server Full Outer Join illustration

SQL Server full outer join example

Let’s set up some sample table to demonstrate the full outer join.
First, create a new schema named pm which stands for project managements.
Next, create new tables named projects and members in the pm schema:
Suppose, each member only can participate in one project and each project has zero or more members. If a project is in the idea phase, hence there is no member assigned.
Then, insert some rows into the projects and members tables:
After that, query data from the projects and members tables:
SQL Server full outer join - projects table
SQL Server full outer join - members table
Finally, use the FULL OUTER JOIN to query data from projects and members tables:
Here is the output:
SQL Server full outer join example
In this example, the query returned members who participate in projects, members who do not participate in any projects, and projects which do not have any members.
To find the members who do not participate in any project and projects which do not have any members, you add a WHERE clause to the above query:
The following picture shows the output:
SQL Server full outer join with a WHERE clause example
As clearly shown in the output, Jack Daniel does not participate in any project and Develop Mobile Sales Platform does not have any members.
In this tutorial, you have learned how to use SQL Server full outer join to query data from two or more tables.

Post a Comment

0 Comments