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:
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:
Suppose we want to know all the currencies we can place orders in and which orders were placed in those currencies. How would we write this in SQL as a FULL JOIN?
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.
Further down in the results you see currencies with no matching sales. This reflects the fact that no sales were made in those currencies.
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 SQL concepts such as using a FULL JOIN to join 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.
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.
SQL FULL JOINS and 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.
