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 

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
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.

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.

Additional SQL Join Resources

To learn more about these joins, check out these useful resources:

{"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.

__CONFIG_colors_palette__{"active_palette":0,"config":{"colors":{"b6728":{"name":"Main Accent","parent":-1},"03296":{"name":"Accent Low Opacity","parent":"b6728"}},"gradients":[]},"palettes":[{"name":"Default","value":{"colors":{"b6728":{"val":"var(--tcb-skin-color-0)"},"03296":{"val":"rgba(17, 72, 95, 0.5)","hsl_parent_dependency":{"h":198,"l":0.22,"s":0.7}}},"gradients":[]},"original":{"colors":{"b6728":{"val":"rgb(47, 138, 229)","hsl":{"h":210,"s":0.77,"l":0.54,"a":1}},"03296":{"val":"rgba(47, 138, 229, 0.5)","hsl_parent_dependency":{"h":210,"s":0.77,"l":0.54,"a":0.5}}},"gradients":[]}}]}__CONFIG_colors_palette__
Name*
Email*
__CONFIG_colors_palette__{"active_palette":0,"config":{"colors":{"dffbe":{"name":"Main Accent","parent":-1}},"gradients":[]},"palettes":[{"name":"Default Palette","value":{"colors":{"dffbe":{"val":"var(--tcb-color-4)"}},"gradients":[]},"original":{"colors":{"dffbe":{"val":"rgb(19, 114, 211)","hsl":{"h":210,"s":0.83,"l":0.45}}},"gradients":[]}}]}__CONFIG_colors_palette__
Sign Up
>