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.

See Also