SQL Join Memento

Recently (i.e. March 2012), I needed to make a rather complicated comparison between two tables, to highlight all the errors differences between them, including data that only appear in one of the two tables. Given my level in SQL joins, it's impossible to remember how to do this from memory.

Fortunately, a visit to the Join (SQL) page on Wikipedia made me (re?)discover the FULL OUTER JOIN, which I completed with Jeff Atwood's post A Visual Explanation of SQL Joins.

So I took this opportunity to review the different types of joints, to learn the two types of syntax (explicit and implicit) and also to discover some tricks that I didn't know at all:

• the natural join: convention over configuration
• the cross join: I didn't know there was a dedicated keyword to do that willingly

Creation of 2 tables to test (for Oracle)

1st table

CREATE TABLE Table1 AS
SELECT 1 AS ID, 'Un' AS Libelle FROM DUAL
UNION
SELECT 2 AS ID, 'Deux' AS Libelle FROM DUAL
UNION
SELECT 3 AS ID, 'Trois' AS Libelle FROM DUAL

SELECT * FROM Table1
=>
ID  Libelle
--  -------
1   Un
2   Deux
3   Trois

2nd table

CREATE TABLE Table2 AS
SELECT 1 AS ID, 'One' AS Caption FROM DUAL
UNION
SELECT 2 AS ID, 'Two' AS Caption FROM DUAL
UNION
SELECT 4 AS ID, 'Four' AS Caption FROM DUAL

SELECT * FROM Table2
=>
id  caption
--  -------
1   One
2   Two
4   Four

INNER JOIN

We only take what exists on both sides.

Explicit syntax

SELECT T1.ID, T1.Libelle, T2.Caption
FROM   Table1 T1
INNER JOIN Table2 T2 ON T1.ID = T2.ID

Implicit syntax

SELECT T1.ID, T1.Libelle, T2.Caption
FROM   Table1 T1,
       Table2 T2
WHERE  T2.ID = T1.ID

"Natural" syntax

SELECT ID, T1.Libelle, T2.Caption
FROM   Table1 T1
NATURAL JOIN Table2 T2

Result

ID  Libelle  Caption
--  -------  -------
1   Un       One
2   Deux     Two

CROSS JOIN

We take everything from both sides without making a match <=> Cartesian product.

Explicit syntax

SELECT T1.ID, T1.Libelle, T2.Caption
FROM   Table1 T1
CROSS JOIN Table2 T2

Implicit syntax

SELECT T1.ID, T1.Libelle, T2.Caption
FROM   Table1 T1,
       Table2 T2

Result

ID  Libelle  Caption
--  -------  -------
1   Un       One
1   Un       Two
1   Un       Four
2   Deux     One
2   Deux     Two
2   Deux     Four
3   Trois    One
3   Trois    Two
3   Trois    Four

LEFT OUTER JOIN

We take everything on the left (i.e. the first table) and the other one follows.

Explicit syntax

SELECT T1.ID, T1.Libelle, T2.Caption
FROM   Table1 T1
LEFT OUTER JOIN Table2 T2 ON T1.ID = T2.ID

Implicit syntax

SELECT T1.ID, T1.Libelle, T2.Caption
FROM   Table1 T1,
       Table2 T2
WHERE  T2.ID(+) = T1.ID

Result

ID  Libelle  Caption
--  -------  -------
1   Un       One
2   Deux     Two
3   Trois

RIGHT OUTER JOIN

We take everything on the right (i.e. the second table) and the other one follows.

Explicit syntax

SELECT T1.ID, T1.Libelle, T2.Caption
FROM   Table1 T1
RIGHT OUTER JOIN Table2 T2 ON T1.ID = T2.ID

Implicit syntax

SELECT T1.ID, T1.Libelle, T2.Caption
FROM   Table1 T1,
       Table2 T2
WHERE  T2.ID = T1.ID(+)

Result

ID  Libelle  Caption
--  -------  -------
1   Un       One
2   Deux     Two
             Four

FULL OUTER JOIN

We take what exists on both sides.

Explicit syntax

SELECT T1.ID, T1.Libelle, T2.Caption
FROM   Table1 T1
FULL OUTER JOIN Table2 T2 ON T1.ID = T2.ID

Implicit syntax (or how to do without knowledge)

SELECT T1.ID, T1.Libelle, T2.Caption
FROM   Table1 T1,
       Table2 T2
WHERE  T2.ID(+) = T1.ID
UNION
SELECT T1.ID, T1.Libelle, T2.Caption
FROM   Table1 T1,
       Table2 T2
WHERE  T2.ID = T1.ID(+)

Result

ID  Libelle  Caption
--  -------  -------
1   Un       One
2   Deux     Two
             Four
3   Trois

Improved version to get ID

SELECT NVL(T1.ID, T2.ID) AS ID, T1.Libelle, T2.Caption
FROM   Table1 T1
FULL OUTER JOIN Table2 T2 ON T1.ID = T2.ID

=>

ID  Libelle  Caption
--  -------  -------
1   Un       One
2   Deux     Two
4            Four
3   Trois