# 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.