Another short focus on SQL joins

Posted last year.

Needless to say joins are a key feature in SQL as they considerably ease the processing of combining sets together. There already had been one article published on this blog about this topic and so far, developers keep asking what is the difference between a inner join and a left outer join, why isn’t there an inner left join etc.

INNER JOIN

An INNER join is saying "my set has a working condition".

SELECT A.a, B.b
FROM
  (VALUES (1),(2),(3),(4)) A (a)
  INNER JOIN (VALUES (3),(4),(5),(6)) B (b) ON A.a = B.b;

┌───┬───┐ │ a │ b │ ├───┼───┤ │ 33 │ │ 44 │ └───┴───┘ (2 rows)

The order in the condition is not important since every combination that verifies the condition is accepted:

SELECT A.a, B.b
FROM (VALUES (1),(2),(3),(4)) A (a)
  INNER JOIN (VALUES (3),(4),(5),(6)) B (b) ON A.a >= B.b;

┌───┬───┐ │ a │ b │ ├───┼───┤ │ 33 │ │ 43 │ │ 44 │ └───┴───┘ (3 rows)

OUTER JOINS

Unlike the inner join, there are several types of outer joins. An outer join is saying: "From a reference set, join a foreign set if the condition is true". There are two important words in the last sentence: "reference" and "if". Even if the condition is false, the resulting set has all the rows of the reference set. There must be a way to tell the interpreter which set is the reference, here are LEFT, RIGHT & FULL. LEFT means the reference table is the one the join is made on. RIGHT means the reference table is the joined table:

SELECT A.a, B.b
FROM (VALUES (1),(2),(3),(4)) A (a)
  LEFT OUTER JOIN (VALUES (3),(4),(5),(6)) B (b) ON A.a = B.b;
┌───┬───┐
│ a │ b │
├───┼───┤
│ 1 │ ø │
│ 2 │ ø │
│ 33 │
│ 44 │
└───┴───┘
(4 rows)

When no rows satisfy the condition in the foreign set, a NULL is returned to express the lack of value.

SELECT A.a, B.b
FROM (VALUES (1),(2),(3),(4)) A (a)
  RIGHT OUTER JOIN (VALUES (3),(4),(5),(6)) B (b) ON A.a = B.b;
┌───┬───┐
│ a │ b │
├───┼───┤
│ 33 │
│ 44 │
│ ø │ 5 │
│ ø │ 6 │
└───┴───┘
(4 rows)

The FULL join is considering both tables are treated equal (no reference):

SELECT A.a, B.b
FROM (VALUES (1),(2),(3),(4)) A (a)
  FULL OUTER JOIN (VALUES (3),(4),(5),(6)) B (b) ON A.a = B.b;
┌───┬───┐
│ a │ b │
├───┼───┤
│ 1 │ ø │
│ 2 │ ø │
│ 33 │
│ 44 │
│ ø │ 5 │
│ ø │ 6 │
└───┴───┘
(6 rows)

CROSS JOIN

This join cannot be express using Venn diagrams, it is a join with no condition, every element of each set is combined with each other:

SELECT A.a, B.b
FROM (VALUES (1),(2),(3),(4)) A (a)
  CROSS JOIN (VALUES (3),(4),(5),(6)) B (b)
;
┌───┬───┐
│ a │ b │
├───┼───┤
│ 13 │
│ 14 │
│ 15 │
│ 16 │
│ 23 │
│ 24 │
    …
│ 45 │
│ 46 │
└───┴───┘
(16 rows)

This join may seem useless at first glance but it is very fast (no condition) and can be used either to generate combinations or pass query parameters to sub queries:

WITH
  parameter AS (SELECT 2 as min_level)
SELECT A.a
FROM (VALUES (1),(2),(3),(4)) A (a)
  CROSS JOIN parameter
WHERE A.a > parameter.min_level
;
┌───┐
│ a │
├───┤
│ 3 │
│ 4 │
└───┘
(2 rows)

Comments on reddit.