Today 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 well get a better understand of how it and the other joins works.
If you want to learn more about theses 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:
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 (inner join), and then combines that result, with those rows from the table t1 that didn’t match any rows from the t2 (left join) , and then combines those rows with any tables from the t2, which didn’t match with the t1 (right join).
Let’s illustrate using a simple example. We’ll set up two tables Company and Product.
CompanyProduct
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 diagramed to show how it relates to the matching results:
SQL FULL JOIN Example
You can try this example here:
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
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 a Close Equivalent to an OUTER JOIN Statement?
We don’t need to include the Inner join as the both the LEFT JOIN and RIGHT JOIN include the same rows the INNER JOIN WOULD.
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:
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
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
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
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 they are the same. If 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 the our composite query, we should expect to get zero rows.
We can test this here:
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
)
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
)
Duplicate Record Issues
The above is a close equivalent to an outer join as the UNION will removes any duplicate records. You may think you can solve the problem by using UNION ALL, but that introduces duplicates.
Use FULL OUTER join if you can. If not, then when constructing you own equivalent, consider how set operators, such as UNION, affect your results.
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.
Kris Wenzel has been working with databases over the past 28 years as a developer, analyst, and DBA.
He has a BSE in Computer Engineering from the University of Michigan and a MBA from the University of Notre Dame.
Kris has written hundreds of blog articles and many online courses. He loves helping others learn SQL.
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 – (??) from the right table, which didn’t match with the left.
Hi,
I reworded the paragraph to make it more understandable. The gist is the a full outer join is a inner join, left join, and right join rolled into one clause.
Kris
{"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.
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 – (??) from the right table, which didn’t match with the left.
Hi,
I reworded the paragraph to make it more understandable. The gist is the a full outer join is a inner join, left join, and right join rolled into one clause.
Kris