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 join
s works.
If you want to learn more about these or other join
s, 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
, 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
.
Company
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 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 [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 join
s. 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
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:
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.
- An
Inner Join
on the join
condition - A
Right Join
on the join
condition - A Left Join on the
join
condition