SQL: Full Outer Join - The Most Generic Join Statement
Context
There was an interesting question once asked during an interview for SQL skills which looks like the below:
"Which is the simplest type of join and which is the most generic type of join statement?"
Though its evident that the answer to the former one is CROSS JOIN you might have a doubt on the latter.
Reasoning
The reason which will prompt you to guess that the answer to the latter is FULL JOIN is due to the fact that using FULL JOIN you could simulate the functionalities of all the other types of the join statements. You can also simulate UNION and UNION ALL functionalities using FULL JOIN. This wiki article elaborates on how FULL JOIN can be used to simulate the functionalities of each of the join types as well as UNION and UNION ALL statements.
Illustration
For this illustration consider the below two table variables with the given data
declare @table1 table
(
id int,
col1 varchar(10),
col2 varchar(10)
)
insert @table1
values (1,'value 1','cat 1'),
(3,'value 3','cat 1'),
(5,'value 5','cat 2'),
(6,'value 6','cat 3'),
(7,'value 7','cat 1'),
(9,'value 9','cat 3')
declare @table2 table
(
id int,
col1 varchar(10),
col2 varchar(10)
)
insert @table2
values (1,'value 1','cat 1'),
(2,'value 2','cat 1'),
(4,'value 4','cat 2'),
(6,'value 6','cat 3'),
(7,'value 7','cat 1'),
(11,'value 11','cat 2')
Now lets see the illustration of each join types with example queries
Cross Join
Cross join is same as Cartesian Product ie it takes all possible combinations between both the tables involved. That is why its often referred to as simplest type of join. This can be diagrammatically represented as below
The illustration can be given as follows. I've also given original query for comparison
--original query
select t1.id,t2.id,t1.col1,t1.col2,t2.col1,t2.col2
from @table1 t1
cross join @table2 t2
--cross join simulation using full join
select t1.id,t2.id,t1.col1,t1.col2,t2.col1,t2.col2
from @table1 t1
full outer join @table2 t2
on 1 = 1
Inner Join
Inner Join is the type of join which looks for only exact matches and ignores the unmatched entities. Diagrammatically it can be illustrated as below
Check the below illustration showing simulation of Inner Join using Full Outer Join and comparison with actual statement
--original query
select t1.id,t2.id,t1.col1,t1.col2,t2.col1,t2.col2
from @table1 t1
inner join @table2 t2
on t2.id = t1.id
--inner join simulation using full join
select t1.id,t2.id,t1.col1,t1.col2,t2.col1,t2.col2
from @table1 t1
full outer join @table2 t2
on t2.id = t1.id
where t2.id is not null
and t1.id is not null
Left Join
Left Join is the type of join which returns the full result set from the left table regardless of a match. For the right table columns it will only return cases where there are matches.
Diagrammatically it can be represented as below
The corresponding functionality can be simulated using Full Join as per below
--original query
select t1.id,t2.id,t1.col1,t1.col2,t2.col1,t2.col2
from @table1 t1
left join @table2 t2
on t2.id = t1.id
--left join simulation using full join
select t1.id,t2.id,t1.col1,t1.col2,t2.col1,t2.col2
from @table1 t1
full outer join @table2 t2
on t2.id = t1.id
where t1.id is not null
Right Join
Right Join is the type of join which returns the full result set from the right table regardless of a match. For the left table columns it will only return cases where there are matches.
Diagrammatically it can be represented as below
Right Join can be implemented using Full Join as per below statement
--original query
select t1.id,t2.id,t1.col1,t1.col2,t2.col1,t2.col2
from @table1 t1
right join @table2 t2
on t2.id = t1.id
--right join simulation using full join
select t1.id,t2.id,t1.col1,t1.col2,t2.col1,t2.col2
from @table1 t1
full outer join @table2 t2
on t2.id = t1.id
where t2.id is not null
Full Join
The native functionality of Full Join is implemented as below
Diagrammatically it can be represented as below
select t1.id,t2.id,t1.col1,t1.col2,t2.col1,t2.col2
from @table1 t1
full outer join @table2 t2
on t2.id = t1.id
Union All
Union All statement helps in merging two similar result sets into a single result set. The Full Join can also be applied between two similar result sets to merge them vertically to give us a simulation of UNION ALL functionality.
See illustration below
--original query
select t1.id,t1.col1,t1.col2
from @table1 t1
union all
select t2.id,t2.col1,t2.col2
from @table2 t2
order by id
--union all simulation using full join
select coalesce(t1.id,t2.id) as id,coalesce(t1.col1,t2.col1) as col1,coalesce(t1.col2,t2.col2) as col2
from @table1 t1
full outer join @table2 t2
on 1 = 2
order by id
Union is similar to Union All except for the fact that it returns only unique combination of records. Illustration for Union can be obtained just tweaking the above queries as this
--original query
select t1.id,t1.col1,t1.col2
from @table1 t1
union
select t2.id,t2.col1,t2.col2
from @table2 t2
order by id
--union simulation using full join
select distinct coalesce(t1.id,t2.id) as id,coalesce(t1.col1,t2.col1) as col1,coalesce(t1.col2,t2.col2) as col2
from @table1 t1
full outer join @table2 t2
on 1 = 2
order by id
Summary
As shown by the above illustrations we can apply Full Join to simulate the functionalities of all the other join types as well as Union and Union all statements to do the vertical merging rows. For this reason it makes us perfect sense to often refer Full Join as the most generic type of join statement.
So the next time somebody asks you on the generic join statement you can confidently say that its Full Outer Join or simply Full Join citing the above illustrations.