Click here to Skip to main content
15,850,359 members
Articles / Database Development / SQL Server

SQL Full Outer Join

Rate me:
Please Sign up or sign in to vote.
5.00/5 (1 vote)
29 Dec 2020MIT2 min read 7.1K   2   4
Understanding SQL full outer join and its reconstruction through other join clauses
The post explores SQL full outer join and its reconstruction methods, enhancing comprehension of SQL join operations through practical examples.

In this post, we’re going to look at the SQL full outer join and see how we can reconstruct it using other join clauses. By doing so, we'll get a better understanding of how it and the other joins works.

If you want to learn more about these or other joins, I would encourage you to check out my online tutorial, where you can explore and practice SQL.

What is an SQL Full Outer Join?

A full outer join combines the characteristics of an inner join, left join, and right join into a single operation. Before we get too far, here is how you would write the statement:

SQL
SELECT t1.column1, t1.column2, …, t2.column1, t2.column2,…
FROM t2
FULL OUTER JOIN t2 on t1.column1 = t2.column1

Given the join condition, t1.column1=t2.column1, the SQL full outer join matches every row between the two tables t1 and t2, and then combines that result, with those rows from the left table (t1) that didn’t match any rows from the right (t2), and then combines those rows with any tables from the right table, which didn’t match with the left.

Let’s illustrate using a simple example. We’ll set up two tables, Company and Product.

SQL Full Outer Join Example Company Table

Company

SQL Full Outer Join Example Product Table

Product

You notice that there are some companies, such as Oracle that have no products, and some products, such as Quick Books and Turbo Tax, that don’t have companies.

Here is the FULL OUTER JOIN query diagrammed to show how it relates to the matching results:

SQL FULL OUTER JOIN Example

SQL FULL JOIN Example

You can try this example here:

SQL
DECLARE @Company TABLE     (CompanyID int, CompanyName varchar(20))
insert into @Company values(1            , 'Microsoft')
insert into @Company values(2            , 'Adobe')
insert into @Company values(3            , 'Oracle')
insert into @Company values(4            , 'TechSmith')

DECLARE @Product TABLE   (ProductID int, CompanyID int, ProductName varchar(20))
insert into @Product values(1			,1            , 'Excel')
insert into @Product values(2			,1            , 'SQL Server')
insert into @Product values(3			,1            , 'Access')
insert into @Product values(4			,2            , 'Acrobat')
insert into @Product values(5			,2            , 'Photo Shop')
insert into @Product values(6			,4            , 'Snagit')
insert into @Product values(7			,4            , 'Camtasia')
insert into @Product values(8			,5            , 'Quick Books')
insert into @Product values(9			,5            , 'Turbo Tax')

SELECT c.CompanyID, c.CompanyName, p.ProductID, p.ProductName
FROM @Company c
FULL OUTER JOIN @Product p ON c.CompanyID = p.CompanyID
ORDER BY c.CompanyName, p.ProductName [answer]

What is Equivalent to an OUTER JOIN Statement?

As you get to know more about SQL, you discover there is more than one way to write a query. For instance, you may have discovered inner joins and subqueries are used to solve the same problems.

Similarly, you can use other statements, or more precisely, combinations of other statements to recreate a full outer join’s results.

To do this, we will use an INNER JOIN combined with a LEFT JOIN, and RIGHT JOIN and a UNION operator.

Referring to the diagram we have from above, we can create the FULL OUTER join by stitching together results from the three joins. Here is the final statement:

SQL
DECLARE @Company TABLE     (CompanyID int, CompanyName varchar(20))
insert into @Company values(1            , 'Microsoft')
insert into @Company values(2            , 'Adobe')
insert into @Company values(3            , 'Oracle')
insert into @Company values(4            , 'TechSmith')

DECLARE @Product TABLE   (ProductID int, CompanyID int, ProductName varchar(20))
insert into @Product values(1			,1            , 'Excel')
insert into @Product values(2			,1            , 'SQL Server')
insert into @Product values(3			,1            , 'Access')
insert into @Product values(4			,2            , 'Acrobat')
insert into @Product values(5			,2            , 'Photo Shop')
insert into @Product values(6			,4            , 'Snagit')
insert into @Product values(7			,4            , 'Camtasia')
insert into @Product values(8			,5            , 'Quick Books')
insert into @Product values(9			,5            , 'Turbo Tax')

SELECT c.CompanyID, c.CompanyName, p.ProductID, p.ProductName
FROM @Company c
INNER JOIN @Product p on c.CompanyID = p.CompanyID
UNION
SELECT c.CompanyID, c.CompanyName, p.ProductID, p.ProductName
FROM @Company c
LEFT OUTER JOIN @Product p on c.CompanyID = p.CompanyID
UNION
SELECT c.CompanyID, c.CompanyName, p.ProductID, p.ProductName
FROM @Company c
RIGHT OUTER JOIN @Product p on c.CompanyID = p.CompanyID
ORDER BY c.CompanyName, p.ProductName

Run this and compare it to the results above to see that they are the same. If you want, you can also use the EXCEPT operator prove it out. The EXCEPT operator returns all rows from one table that aren’t in another, so if we use EXCEPT between the results from the FULL OUTER JOIN and our composite query, we should expect to get zero rows.

We can test this here:

SQL
DECLARE @Company TABLE     (CompanyID int, CompanyName varchar(20))
insert into @Company values(1            , 'Microsoft')
insert into @Company values(2            , 'Adobe')
insert into @Company values(3            , 'Oracle')
insert into @Company values(4            , 'TechSmith')

DECLARE @Product TABLE   (ProductID int, CompanyID int, ProductName varchar(20))
insert into @Product values(1			,1            , 'Excel')
insert into @Product values(2			,1            , 'SQL Server')
insert into @Product values(3			,1            , 'Access')
insert into @Product values(4			,2            , 'Acrobat')
insert into @Product values(5			,2            , 'Photo Shop')
insert into @Product values(6			,4            , 'Snagit')
insert into @Product values(7			,4            , 'Camtasia')
insert into @Product values(8			,5            , 'Quick Books')
insert into @Product values(9			,5            , 'Turbo Tax')

SELECT c.CompanyID, c.CompanyName, p.ProductID, p.ProductName
FROM @Company c
FULL OUTER JOIN @Product p ON c.CompanyID = p.CompanyID
EXCEPT
(
	SELECT c.CompanyID, c.CompanyName, p.ProductID, p.ProductName
	FROM @Company c
	INNER JOIN @Product p on c.CompanyID = p.CompanyID
	UNION
	SELECT c.CompanyID, c.CompanyName, p.ProductID, p.ProductName
	FROM @Company c
	LEFT OUTER JOIN @Product p on c.CompanyID = p.CompanyID
	UNION
	SELECT c.CompanyID, c.CompanyName, p.ProductID, p.ProductName
	FROM @Company c
	RIGHT OUTER JOIN @Product p on c.CompanyID = p.CompanyID
)

Conclusion

So as you can see from what I taught, a Full join can be recreated using inner and outer joins. A full join matched rows from two tables, the results returns are a combination of what would be returned if the following three queries were run and combined with a union.

  1. An Inner Join on the join condition
  2. A Right Join on the join condition
  3. A Left Join on the join condition
This article was originally posted at https://www.essentialsql.com/sql-full-outer-join

License

This article, along with any associated source code and files, is licensed under The MIT License


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

 
BugThey're not (always) the same Pin
David On Life4-Jan-21 7:01
David On Life4-Jan-21 7:01 
GeneralRe: They're not (always) the same Pin
essentialSQL5-Jan-21 7:02
essentialSQL5-Jan-21 7:02 
Hi,

Thanks for pointing out the issue with the post, I've updated my original blog post. This posting here is via a RSS feed.

It is an instructional post, so I don't want to mislead. The duplicate record observation is a good catch.

I did some research and see many blog miss that.
It is a catch 22 as UNION will remove duplicates, but UNION ALL introduces them.

Kris.
Kris - www.essentialsql.com

QuestionMissing ) or expression Pin
EoRaptor31-Dec-20 10:46
professionalEoRaptor31-Dec-20 10:46 
AnswerRe: Missing ) or expression Pin
essentialSQL2-Jan-21 6:37
essentialSQL2-Jan-21 6:37 

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.