Learn SQL FULL OUTER JOIN with Examples

By: Jim Evans   |   Updated: 2021-09-23   |   Comments (1)   |   Related: > JOIN Tables

Problem

What is the SQL Full Join? When would you use a SQL Full Join in a SQL Server relational database?  Can you provide some examples as a SQL tutorial?  What is the syntax?

Solution

In T-SQL a SQL Full Join is one of the many types of Outer Joins used to Join multiple tables. In this tutorial I will demonstrate the use of a SQL Full Outer Join and provide a scenario of when to use it.

What Is SQL FULL JOIN

The FULL OUTER Join is like a combination of a Right and Left Join. The Full join can return data in 3 quadrants: The rows that match, the rows that are only in the Left table and the rows that are only in the Right table.

When to Use FULL JOIN

Full Join shows row results from all joined tables showing matches and no matches. Full Joins are often used when analyzing or evaluating data. An example of analysis with a Full Join might be if you have two lists of customers in two separate tables that you want to compare. With a Full Join between the 2 tables, you get every row from both tables, and you can see the matching rows, what is only in the left table and what is only in the Right table.

See the examples below.

FULL OUTER JOIN Example: Customer that purchased Products

I will build 2 lists of customers base on 2 specific products that they purchased and compare the 2 list with a Full Outer Join to see which customer purchased either product or which customers purchased both! I will use Common Table Expressions to split up my 2 lists. You may use a temp table as an alternative.

For this example I will use the AdventureWorks sample database and include queries against the [Sales].[SalesOrderHeader], [Sales].[SalesOrderDetail] and [Production].[Product] tables.

These are the 2 products that I am targeting:

----Full Join Example in the following query:
USE AdventureWorks;
GO

--1) Setup the Common Table Expresions:
with cte707 as (
SELECT  CustomerID, sd.[ProductID], p.Name 
FROM [Sales].[SalesOrderHeader] sh
   INNER JOIN [Sales].[SalesOrderDetail] sd ON sd.SalesOrderID = sh.SalesOrderID
   inner join [Production].[Product] p ON p.ProductID = sd.ProductID
WHERE sd.[ProductID] = 707 
), cte712 as (
 
SELECT  CustomerID, sd.[ProductID], p.Name 
FROM [Sales].[SalesOrderHeader] sh
   INNER JOIN [Sales].[SalesOrderDetail] sd ON sd.SalesOrderID = sh.SalesOrderID
   inner join [Production].[Product] p ON p.ProductID = sd.ProductID
WHERE sd.[ProductID] = 712
)

--2) See the Results:
SELECT a.CustomerID as 'Cust w/ Sport-100 Helmet', 
   b.CustomerID as 'Custs w/ AWC Logo Cap',
   CASE  WHEN b.CustomerID is null THEN 'Only Sport-100 Helmet'
        WHEN a.CustomerID is null THEN 'Only AWC Logo Cap'
        ELSE 'Both - Sport-100 Helmet & AWC Logo Cap'
   END as 'Results'
FROM cte707 a
   FULL OUTER JOIN cte712 b ON a.CustomerID = b.CustomerID;
GO

Example Results

In the results you can clearly see what Customer purchased just the Sport-100 Helmet, what customers purchased just the AWC Logo Cap, and which customers purchased both products. The results shown are a small subset of the actual results.

Next Steps

To wrap up, the FULL OUTER JOIN clause in SQL is used to compare lists or data sets. The results will return NULL values in the columns where the lists do not match. The FULL JOIN is used less often than other types of joins, but you can see how it can be used to analyze and comparison of lists.

• Read more about the SQL SELECT Statement: SQL Server SELECT Examples
• Getting Started with SQL INNER JOIN
• Read more about the LEFT JOIN Condition: SQL LEFT JOIN Examples
• Read more about the RIGHT JOIN Condition: SQL RIGHT JOIN Examples
• Read about other SQL Server Join Example
• Read more about SQL UNION: UNION vs. UNION ALL in SQL Server
• Read more about INSERT INTO: SQL INSERT INTO SELECT Examples
• Visit Microsoft to find out how to download sample databases

About the author

Jim Evans is an IT Manager currently for Crowe who has managed DBA, Developer, BI and Data Management teams for over 20 years.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips