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
- 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
Not every sale in the AdventureWorks database list a currency, nor is every currency used in a sale. Given this, let us write a query to show all the sales and currency combinations made so far, and which ones have not matched.
SELECT SOH.AccountNumber, SOH.OrderDate, CR.ToCurrencyCode, CR.AverageRate FROM sales.SalesOrderHeader SOH FULL JOIN sales.CurrencyRate CR ON CR.CurrencyRateID = SOH.CurrencyRateID
Look at the results and notice there are NULL’s for Currency Codes. This is because there are no corresponding entries in the SalesOrderHeader.
Look further down in the results to see NULL AccountNumbers. These represent currencies not used in the database.
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.
Additional SQL Join Resources
To learn more about these joins, check out these useful resources: