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
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
This post was originally published on blog.pagesd.info.
Cover image : Say NO to Venn Diagrams When Explaining JOINs.
Top comments (0)