Click here to Skip to main content
15,434,430 members
Articles / Database Development
Technical Blog
Posted 6 Mar 2022

Tagged as

Stats

2.2K views
3 bookmarked

SQL FULL Join Tutorial

Rate me:
Please Sign up or sign in to vote.
0.00/5 (No votes)
6 Mar 2022CPOL4 min read
In this article we look at SQL FULL JOIN examples and make a few notes of things to look for.

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.

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

SQL
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:

SQL
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?

SQL
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

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:

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

This article was originally posted at https://www.essentialsql.com/sql-full-join

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


Written By
Easy Computer Academy, LLC
United States United States
Hello my name is Kris. I’m here because I am passionate about helping non-techie people to overcome their fear of learning SQL.

I know what it is like to not know where to start or whether the time spent learning is worth the effort. That is why I am here to help you to:
- Get started in an easy to follow step-by-step manner.
- Use your time wisely so you focus on what is important to learn to get the most value from your time.
- Answer your questions. Really! Just post a comment and I’ll respond. I’m here to help.

It wasn’t long ago that I was helping a colleague with some reporting. She didn’t know where to start and soon got overwhelmed and lost as she didn’t know SQL.

I felt really bad, as she was under pressure to get some summary information to her boss, the built-in reports were falling short, and to make them better would require her to know SQL. At that time that seemed impossible! It in dawned on me, it doesn’t have to be that way.

Then I discovered a way for anyone with the desire to easily learn SQL. I worked with my co-worker, started to teach her what I learned and soon she was able to write reports and answer her boss’ questions without getting stressed or ploughing hours into manipulating data in Excel.

It hasn’t always been easy. Sometimes the information seems abstract or too conceptual. In this case I’ve found out that a visual explanation is best. I really like to use diagrams or videos to explain hard-to-grasp ideas.

Having video, pictures, and text really help to reinforce the point and enable learning.

And now I want to help you get the same results.

The first step is simple, click here http://www.essentialsql.com/get-started-with-sql-server/

Comments and Discussions

 
Questioncan this not also be achieved with a left join ? Pin
Delphi.7.Solutions6-Mar-22 23:41
MemberDelphi.7.Solutions6-Mar-22 23:41 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.