Join Types in ClickHouse

ClickHouse 支持 6 种类型的连接（Join）^[1]：

• INNER JOIN
• OUTER JOIN
• CROSS JOIN
• SEMI JOIN
• ANTI JOIN
• ANY JOIN
• ASOF JOIN

INNER JOIN

OUTER JOIN

分为 LEFT、RIGHT 和 FULL OUTER JOIN 三种。

CROSS JOIN

分为 LEFT、RIGHT CROSS JOIN 两种。

也就是笛卡尔积。

SEMI JOIN

分为 LEFT、RIGHT SEMI JOIN 两种。

用于去重的场景。

ANTI JOIN

分为 LEFT、RIGHT ANTI JOIN 两种。

用于 NOT IN 的场景。

ANY JOIN

分为 LEFT、RIGHT 和 INNER ANY JOIN 三种。

LEFT ANY JOIN

WITH
    left_table AS (SELECT * FROM VALUES('c UInt32', 1, 2, 3)),
    right_table AS (SELECT * FROM VALUES('c UInt32', 2, 2, 3, 3, 4))
SELECT
    l.c AS l_c,
    r.c AS r_c
FROM left_table AS l
LEFT ANY JOIN right_table AS r ON l.c = r.c;

┌─l_c─┬─r_c─┐
│   1 │   0 │
│   2 │   2 │
│   3 │   3 │
└─────┴─────┘

RIGHT ANY JOIN

WITH
    left_table AS (SELECT * FROM VALUES('c UInt32', 1, 2, 3)),
    right_table AS (SELECT * FROM VALUES('c UInt32', 2, 2, 3, 3, 4))
SELECT
    l.c AS l_c,
    r.c AS r_c
FROM left_table AS l
RIGHT ANY JOIN right_table AS r ON l.c = r.c;

┌─l_c─┬─r_c─┐
│   2 │   2 │
│   2 │   2 │
│   3 │   3 │
│   3 │   3 │
│   0 │   4 │
└─────┴─────┘

INNER ANY JOIN

WITH
    left_table AS (SELECT * FROM VALUES('c UInt32', 1, 2, 3)),
    right_table AS (SELECT * FROM VALUES('c UInt32', 2, 2, 3, 3, 4))
SELECT
    l.c AS l_c,
    r.c AS r_c
FROM left_table AS l
INNER ANY JOIN right_table AS r ON l.c = r.c;

┌─l_c─┬─r_c─┐
│   2 │   2 │
│   3 │   3 │
└─────┴─────┘

ASOF JOIN

常用于时序分析，JOIN KEY 为时间，寻找尽可能相似的匹配。