Learn SQL FULL OUTER JOIN with Examples
By: Jim Evans | Updated: 2021-09-23 | Comments (1) | Related: More > JOIN Tables
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?
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.
|LEFT Table||RIGHT Table|
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:
|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
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.
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
View all my tips
Article Last Updated: 2021-09-23