Learn SQL FULL OUTER JOIN with Examples


By:   |   Updated: 2021-09-23   |   Comments   |   Related: More > 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.

Diagram, venn diagram Description automatically generated

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.

LEFT Table RIGHT Table
100  
101  
102 102
103 103
  104
  105

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:

ProductID Name
707 Sport-100 Helmet, Red
712 AWC Logo Cap
----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.

Table Description automatically generated
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.






get scripts

next tip button



About the author
MSSQLTips author Jim Evans Jim Evans is an IT Manager currently with Harsco who has managed DBAs, Application and BI Developers and Data Management teams for over 20 years.

View all my tips


Article Last Updated: 2021-09-23

Comments For This Article





download














get free sql tips
agree to terms