November 8, 2021

The SQL FULL JOIN combines results from a left and right outer join into one result.  Or in other words, it is an inner join including unmatched rows from both the left and right tables. 

This is the key difference between a SQL FULL JOIN and inner join.  Where an inner join returns rows matching the join condition, a FULL outer join guarantees all table rows are included in the result. 

We illustrate this below: 

SQL FULL JOIN example

As expected, it includes rows for Mixer and Chopper.  They match both tables, including Blender and Fred Or.  These are rows from the unmatched tables.  If you were to look at our example on LEFT and RIGHT joins, you would see one or the other included, but not both.   Being a SQL FULL JOIN, both are included.

A FULL join is commonly called a FULL OUTER JOIN
The FULL JOIN general form is:

SELECT table1.column1, table2.column2, … 
FROM table1 
FULL JOIN table2 ON table1.commonColumn = table2.commonColumn 

Notes: 

  • In this example table2 is the FULL table and table1 the left. 
  • If there is no match between the commonColumn values, table1.column1 returns NULL 
  • If there is no match between the commonColumn values, table2.column2 returns NULL 

SQL FULL JOIN Example

A full outer join is the combination of results from a left and right outer join.  The results returned from this type of join include all rows from both tables.  Where matches occur, values are related.  Where matched from either table don’t, then NULL are returned instead.

The basic syntax for a full outer join is:

SELECT columnlist
FROM   table
FULL OUTER JOIN othertable ON join condition

Let’s take a look at a different portion of the AdventureWork2012 database.  This time, we are going to focus on the relationships between SalesOrderHeader and CurrencyRate tables.

The model is shown below:

Full Outer Join Model

Suppose we want to know all the currencies we can place orders in and which orders were placed in those currencies?

SELECT sales.SalesOrderHeader.AccountNumber,
       sales.SalesOrderHeader.OrderDate,
       sales.CurrencyRate.ToCurrencyCode,
       sales.CurrencyRate.AverageRate
FROM   sales.SalesOrderHeader
FULL OUTER JOIN
       sales.CurrencyRate
       ON sales.CurrencyRate.CurrencyRateID = 
          sales.SalesOrderHeader.CurrencyRateID

Here is a portion of the results showing where some sales have match to a currency and some that haven’t.  The reason there are sales that don’t match is that these are sales in USD.

Full Outer Join Results 1

Further down in the results you see currencies with no matching sales.  This reflects the fact that no sales were made in those currencies.

Full Outerjoins 2

Note:  I was surprised to see USD listed, see row 42463, since I would think a majority of the sales would be in this currency.  My thought is that rather than reverence the currency rate for these transaction, the SalesOrderHeader vale for CurrencyRateID was set to null for all USD transactions.  I think this is inconsistent, and isn’t the way I would do it, but it isn’t my database…

Advanced Example

So far we’ve looked at the three types of outer joins but haven’t explored some more advanced concepts such as joining multiple table and using more than one condition in our join clauses.

We covered these concepts when we explored inner joins, so what I’ll be showing you, shouldn’t be too new, but I think it still makes sense to review, since in some cases mixing full joins with inner joins may produce unexpected or unintended results.

Let’s turn our focus to the production schema and explore products and categories.  Let’s produce a list of all product categories and the product models contained within.

Production Schema

Product has a one to many relationship with ProductModel and ProductSubcategory.  Since it lies between these two tables, there is an implicit many to many relationship between ProductModel and ProductSubcategory.   Because of this, it is a good candidate for outer joins as there is may be product models with no assigned products and ProductSubcategory entries with no product.

Product Category Datamodel

To overcome this situation we will do an outer join to both the ProductModel and ProductCategory table.

Here is the SQL

SELECT   PC.Name AS Category,
         PSC.Name AS Subcategory,
         PM.Name AS Model,
         P.Name AS Product
FROM     Production.Product AS P
FULL OUTER JOIN
         Production.ProductModel AS PM
         ON PM.ProductModelID = P.ProductModelID
FULL OUTER JOIN
         Production.ProductSubcategory AS PSC
         ON PSC.ProductSubcategoryID = P.ProductSubcategoryID
INNER JOIN
         Production.ProductCategory AS PC
         ON PC.ProductCategoryID = PSC.ProductCategoryID
ORDER BY PC.Name, PSC.Name

There are several items to note:

  • I used table aliases to make the SQL more readable.
  • There is more than one full outer join clause.
  • The ProductCategory table is also part of an outer join

Originally when I wrote the SQL for this query I had an inner join between ProductSubcategory and ProductCategory, but I wasn’t seeing NULL values for unmatched records I would expect.

Once I changed the join to a full outer join I saw the results I expected.  The reason this occurs is subtle.

Watch Out For NULL!

After checking the data I confirmed that all categories are assigned subcategories.  Given this you would think an inner join would work; however, consider that as the entire statement is executed and rows are returned, the ProductSubcategoryID value is NULL whenever a product fails to match a product subcategory.

Null values, by definition, aren’t equal to one another, so the inner join fails.  Given this, when these values are then matched to ProductCategory they aren’t included in the result unless the join to ProductCategory is an outer join.

In fact, the join doesn’t have to be a full outer join, a left join works just as well:

SELECT   PC.Name AS Category,
PSC.Name AS Subcategory,
PM.Name AS Model,
P.Name AS Product
FROM     Production.Product AS P
FULL OUTER JOIN
Production.ProductModel AS PM
ON PM.ProductModelID = P.ProductModelID
FULL OUTER JOIN
Production.ProductSubcategory AS PSC
ON PSC.ProductSubcategoryID = P.ProductSubcategoryID
LEFT OUTER JOIN
         Production.ProductCategory AS PC
         ON PC.ProductCategoryID = PSC.ProductCategoryID
ORDER BY PC.Name, PSC.Name

Important Points:

When working with FULL JOINS keep in mind your match from one table to another may match multiple rows.  Meaning, your result may have more rows in the result that you have in either table.

When columns do not match, keep in mind NULL is replaced for values.

About the author 

Kris Wenzel

Kris Wenzel has been working with databases over the past 30 years as a developer, analyst, and DBA. He has a BSE in Computer Engineering from the University of Michigan and a MBA from the University of Notre Dame. Kris has written hundreds of blog articles and many online courses. He loves helping others learn SQL.

{"email":"Email address invalid","url":"Website address invalid","required":"Required field missing"}

Are YOU READY TO START LEARNING SQL SERVER, BUT DON'T KNOW WHERE TO START?

Nothing is worse than, being excited to learn a new tool but not knowing where to start, wasting time learning the wrong features, and being overwhelmed .

But it doesn't have to be this way.

I'm Putting together a free email course to help you get started learning SQL Server.

Name*
Email*
>