DEV Community

Cover image for SQL Join Memento
Michel
Michel

Posted on • Originally published at blog.pagesd.info

SQL Join Memento

As I'm a bit slow on JavaScript and also I need to revive my SQL knowledge, I take this opportunity to revisit and translate some of my old posts: Mémento des jointures en SQL.

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
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

Implicit syntax

SELECT T1.ID, T1.Libelle, T2.Caption
FROM   Table1 T1,
       Table2 T2
WHERE  T2.ID = T1.ID
Enter fullscreen mode Exit fullscreen mode

"Natural" syntax

SELECT ID, T1.Libelle, T2.Caption
FROM   Table1 T1
NATURAL JOIN Table2 T2
Enter fullscreen mode Exit fullscreen mode

Result

ID  Libelle  Caption
--  -------  -------
1   Un       One
2   Deux     Two
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

Implicit syntax

SELECT T1.ID, T1.Libelle, T2.Caption
FROM   Table1 T1,
       Table2 T2
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

Implicit syntax

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

Result

ID  Libelle  Caption
--  -------  -------
1   Un       One
2   Deux     Two
3   Trois
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

Implicit syntax

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

Result

ID  Libelle  Caption
--  -------  -------
1   Un       One
2   Deux     Two
             Four
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

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(+)
Enter fullscreen mode Exit fullscreen mode

Result

ID  Libelle  Caption
--  -------  -------
1   Un       One
2   Deux     Two
             Four
3   Trois
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

This post was originally published on blog.pagesd.info.
Cover image : Say NO to Venn Diagrams When Explaining JOINs.

Top comments (0)