JOIN

SELECT <expr_list>
FROM <left_table>
[GLOBAL] [INNER|LEFT|RIGHT|FULL|CROSS] [OUTER|SEMI|ANTI|ANY|ALL|ASOF] JOIN <right_table>
(ON <expr_list>)|(USING <column_list>) ...

​

Supported types of JOIN

• JOIN without a type specified implies INNER.
• The keyword OUTER can be omitted.
• An alternative syntax for CROSS JOIN is specifying multiple tables in the FROM clause separated by commas.
• If there are no matching columns for a NATURAL JOIN, it functions like a CROSS JOIN.

​

Rewriting CROSS JOIN as INNER JOIN

• 0: The rewrite is disabled.
• 1: A comma-separated list of tables or a CROSS JOIN is rewritten when the WHERE clause contains an equality condition between the tables. Otherwise, the JOIN is executed as a CROSS JOIN.
• 2: As with 1, but a comma-separated list of tables that cannot be rewritten makes the query fail with the INCORRECT_QUERY error if the query has a WHERE clause. A JOIN written with the CROSS JOIN keywords that cannot be rewritten is executed as a CROSS JOIN.

​

ON section conditions

• reference both left and right tables
• use the equality operator

┌─Id─┬─name─┐     ┌─Id─┬─text───────────┬─scores─┐
│  1 │ A    │     │  1 │ Text A         │     10 │
│  2 │ B    │     │  1 │ Another text A │     12 │
│  3 │ C    │     │  2 │ Text B         │     15 │
└────┴──────┘     └────┴────────────────┴────────┘

SELECT name, text FROM table_1 LEFT OUTER JOIN table_2
    ON table_1.Id = table_2.Id AND startsWith(table_2.text, 'Text');

┌─name─┬─text───┐
│ A    │ Text A │
│ B    │ Text B │
│ C    │        │
└──────┴────────┘

SELECT name, text, scores FROM table_1 INNER JOIN table_2
    ON table_1.Id = table_2.Id AND table_2.scores > 10 AND startsWith(table_2.text, 'Text');

┌─name─┬─text───┬─scores─┐
│ B    │ Text B │     15 │
└──────┴────────┴────────┘

CREATE TABLE t1 (`a` Int64, `b` Int64) ENGINE = MergeTree() ORDER BY a;

CREATE TABLE t2 (`key` Int32, `val` Int64) ENGINE = MergeTree() ORDER BY key;

INSERT INTO t1 SELECT number as a, -a as b from numbers(5);

INSERT INTO t2 SELECT if(number % 2 == 0, toInt64(number), -number) as key, number as val from numbers(5);

SELECT a, b, val FROM t1 INNER JOIN t2 ON t1.a = t2.key OR t1.b = t2.key;

┌─a─┬──b─┬─val─┐
│ 0 │  0 │   0 │
│ 1 │ -1 │   1 │
│ 2 │ -2 │   2 │
│ 3 │ -3 │   3 │
│ 4 │ -4 │   4 │
└───┴────┴─────┘

SELECT a, b, val FROM t1 INNER JOIN t2 ON t1.a = t2.key OR t1.b = t2.key AND t2.val > 3;

┌─a─┬──b─┬─val─┐
│ 0 │  0 │   0 │
│ 2 │ -2 │   2 │
│ 4 │ -4 │   4 │
└───┴────┴─────┘

​

JOIN with inequality conditions for columns from different tables

┌─key──┬─attr─┬─a─┬─b─┬─c─┐
│ key1 │ a    │ 1 │ 1 │ 2 │
│ key1 │ b    │ 2 │ 3 │ 2 │
│ key1 │ c    │ 3 │ 2 │ 1 │
│ key1 │ d    │ 4 │ 7 │ 2 │
│ key1 │ e    │ 5 │ 5 │ 5 │
│ key2 │ a2   │ 1 │ 1 │ 1 │
│ key4 │ f    │ 2 │ 3 │ 4 │
└──────┴──────┴───┴───┴───┘

┌─key──┬─attr─┬─a─┬─b─┬─c─┐
│ key1 │ A    │ 1 │ 2 │ 1 │
│ key1 │ B    │ 2 │ 1 │ 2 │
│ key1 │ C    │ 3 │ 4 │ 5 │
│ key1 │ D    │ 4 │ 1 │ 6 │
│ key3 │ a3   │ 1 │ 1 │ 1 │
│ key4 │ F    │ 1 │ 1 │ 1 │
└──────┴──────┴───┴───┴───┘

SELECT t1.*, t2.* FROM t1 LEFT JOIN t2 ON t1.key = t2.key AND (t1.a < t2.a) ORDER BY (t1.key, t1.attr, t2.key, t2.attr);

key1    a    1    1    2    key1    B    2    1    2
key1    a    1    1    2    key1    C    3    4    5
key1    a    1    1    2    key1    D    4    1    6
key1    b    2    3    2    key1    C    3    4    5
key1    b    2    3    2    key1    D    4    1    6
key1    c    3    2    1    key1    D    4    1    6
key1    d    4    7    2            0    0    \N
key1    e    5    5    5            0    0    \N
key2    a2    1    1    1            0    0    \N
key4    f    2    3    4            0    0    \N

​

JOIN with an arbitrary ON condition

CREATE TABLE orders (id UInt32, amount UInt32) ENGINE = Memory;
CREATE TABLE discounts (min_amount UInt32, max_amount UInt32, pct UInt32) ENGINE = Memory;

INSERT INTO orders VALUES (1, 50), (2, 150), (3, 300);
INSERT INTO discounts VALUES (100, 199, 5), (200, 1000, 10);

SELECT o.id, o.amount, d.pct
FROM orders AS o
LEFT JOIN discounts AS d ON o.amount BETWEEN d.min_amount AND d.max_amount
ORDER BY o.id;

┌─id─┬─amount─┬─pct─┐
│  1 │     50 │   0 │
│  2 │    150 │   5 │
│  3 │    300 │  10 │
└────┴────────┴─────┘

​

NULL and NaN values in JOIN keys

┌───id─┬─name────┐
│    1 │ Alice   │
│    2 │ Bob     │
│ ᴺᵁᴸᴸ │ Charlie │
└──────┴─────────┘

┌───id─┬─score─┐
│    1 │    90 │
│    3 │    85 │
│ ᴺᵁᴸᴸ │    88 │
└──────┴───────┘

SELECT A.name, B.score FROM A LEFT JOIN B ON A.id = B.id

┌─name────┬─score─┐
│ Alice   │    90 │
│ Bob     │     0 │
│ Charlie │     0 │
└─────────┴───────┘

SELECT A.name, B.score FROM A LEFT JOIN B ON isNotDistinctFrom(A.id, B.id)

┌─name────┬─score─┐
│ Alice   │    90 │
│ Bob     │     0 │
│ Charlie │    88 │
└─────────┴───────┘

​

Implicit type conversion

• With ON, the conversion applies only to the comparison. The key columns keep their original types in the result.
• With USING, the merged column has the common type in the result. When semi_join_include_columns_from_both_sides or anti_join_include_columns_from_both_sides is disabled, the merged column of a SEMI JOIN or an ANTI JOIN keeps the type of the preserved table.

┌─a─┬─b─┬─toTypeName(a)─┬─toTypeName(b)─┐
│ 1 │ 1 │ UInt16        │ UInt8         │
│ 2 │ 2 │ UInt16        │ UInt8         │
└───┴───┴───────────────┴───────────────┘

┌──a─┬────b─┬─toTypeName(a)─┬─toTypeName(b)───┐
│ -1 │    1 │ Int16         │ Nullable(Int64) │
│  1 │   -1 │ Int16         │ Nullable(Int64) │
│  1 │    1 │ Int16         │ Nullable(Int64) │
└────┴──────┴───────────────┴─────────────────┘

SELECT a, b, toTypeName(a), toTypeName(b) FROM t_1 FULL JOIN t_2 USING (a, b) ORDER BY ALL;

┌──a─┬────b─┬─toTypeName(a)─┬─toTypeName(b)───┐
│ -1 │    1 │ Int32         │ Nullable(Int64) │
│  1 │   -1 │ Int32         │ Nullable(Int64) │
│  1 │    1 │ Int32         │ Nullable(Int64) │
│  2 │    2 │ Int32         │ Nullable(Int64) │
└────┴──────┴───────────────┴─────────────────┘

​

ASOF JOIN usage

• Must have a type with a defined order.
• With the hash algorithm, can be of the following types: Int, UInt, Float, Decimal, Date, Date32, DateTime, DateTime64, Time, Enum, Bool, and Nullable of these types. Other types, such as String and Time64, are rejected with the BAD_TYPE_OF_FIELD error.
• With the full_sorting_merge algorithm, can also be of other comparable types, such as String.
• With the hash algorithm, cannot be the only column in the JOIN clause. At least one equality condition is required. The full_sorting_merge algorithm does not have this requirement.

SELECT expressions_list
FROM table_1
ASOF LEFT JOIN table_2
ON equi_cond AND closest_match_cond

SELECT expressions_list
FROM table_1
ASOF JOIN table_2
USING (equi_column1, ... equi_columnN, asof_column)

         table_1                           table_2
      event   | ev_time | user_id       event   | ev_time | user_id
    ----------|---------|----------   ----------|---------|----------
                  ...                               ...
    event_1_1 |  12:00  |  42         event_2_1 |  11:59  |   42
                  ...                 event_2_2 |  12:30  |   42
    event_1_2 |  13:00  |  42         event_2_3 |  13:00  |   42
                  ...                               ...

​

PASTE JOIN usage

SELECT *
FROM
(
    SELECT number AS a
    FROM numbers(2)
) AS t1
PASTE JOIN
(
    SELECT number AS a
    FROM numbers(2)
    ORDER BY a DESC
) AS t2

┌─a─┬─t2.a─┐
│ 0 │    1 │
│ 1 │    0 │
└───┴──────┘

SELECT *
FROM
(
    SELECT number AS a
    FROM numbers_mt(5)
) AS t1
PASTE JOIN
(
    SELECT number AS a
    FROM numbers(10)
    ORDER BY a DESC
) AS t2
SETTINGS max_block_size = 2;

┌─a─┬─t2.a─┐
│ 2 │    9 │
│ 3 │    8 │
└───┴──────┘
┌─a─┬─t2.a─┐
│ 0 │    7 │
│ 1 │    6 │
└───┴──────┘
┌─a─┬─t2.a─┐
│ 4 │    5 │
└───┴──────┘

​

LATERAL JOIN

SELECT ...
FROM <left_table>
[INNER|LEFT] JOIN LATERAL (SELECT ... WHERE <expr referencing left_table>) AS <alias> ON true

• INNER JOIN LATERAL and LEFT JOIN LATERAL only; RIGHT, FULL, PASTE and NATURAL joins are not supported, and LATERAL cannot be combined with a CROSS or comma join at all.
• The default ALL strictness only; ANY, SEMI, ANTI and ASOF are not supported.
• No join predicate other than ON true (ON 1 is also accepted); USING is not supported, and the predicate cannot be omitted. Put the filters that relate the two sides into the WHERE clause of the lateral subquery.
• The GLOBAL and LOCAL join modifiers are not supported.
• The lateral subquery must reference at least one column of the left side. Use a regular join for a non-correlated subquery.
• Because the lateral subquery is decorrelated rather than evaluated separately for each left value, it must not contain functions that are non-deterministic within a query, such as rand or generateUUIDv4, or table functions that generate random rows, such as generateRandom. Functions that are constant within a query, such as now, are allowed.
• Only a subquery is supported as the lateral table expression. The PostgreSQL table-source forms LATERAL unnest(...) and CROSS JOIN UNNEST(...) are not supported. Use the ARRAY JOIN clause instead.
• The GROUP BY and ORDER BY of the lateral subquery run once for all left values together, so the max_rows_to_group_by, max_rows_to_sort and max_bytes_to_sort limits count the rows for all left values, not for one. They are only supported with the throw overflow mode; any and break are rejected.
• The result of the decorrelated subquery is matched to the left rows by a single join. As for any hash join, max_rows_in_join and max_bytes_in_join limit the side of this join that is kept in memory, and the planner chooses that side: when the in-memory buffer described above is actually used, it is the left side of JOIN LATERAL, because the left rows must be fully read before the lateral subquery is evaluated; otherwise it can be the result of the decorrelated subquery. The limits are always enforced as if join_overflow_mode were throw: with break, the join would silently drop unrelated left rows.

SELECT u.id, o.total
FROM users AS u
LEFT JOIN LATERAL
(
    SELECT total
    FROM orders
    WHERE orders.user_id = u.id
    ORDER BY total DESC
    LIMIT 1
) AS o ON true
SETTINGS enable_lateral_join = 1;

​

Distributed JOIN

• When using a normal JOIN, the query is sent to remote servers. Subqueries are run on each of them in order to make the right table, and the join is performed with this table. In other words, the right table is formed on each server separately.
• When using GLOBAL ... JOIN, first the requestor server runs a subquery to calculate one side of the join and collects the result into a temporary table. This temporary table is then passed to each remote server, and queries are run on them using the temporary data that was transmitted. For LEFT and INNER joins, the right table is calculated as the subquery.

​

Processing of empty or NULL cells

• 0: The cells are filled with the default value of the column type.
• 1: The cells are filled with NULL, and the types of these columns are converted to Nullable. A column of a type that cannot be inside Nullable, such as Array, keeps its type and is filled with the default value. In a FULL JOIN, the merged USING column is also converted to Nullable.

​

USING clause

• Each column must exist under the same name in both the left and the right table expression. Aliases in subqueries can give columns the same name.
• The USING clause accepts only column names. Expressions are not supported.
• The column list can be written with or without parentheses.
• Each column of the USING clause appears once in the result. Its type is described in Implicit type conversion.
• Other columns can have the same name in both tables. SELECT * qualifies the column of the right table with the table name or alias, and an unqualified name resolves to the column of the left table when single_join_prefer_left_table is enabled.
• When analyzer_compatibility_join_using_top_level_identifier is enabled, a column name in USING is resolved from an alias of the same name in the query, such as an alias in the SELECT list.

​

Examples

SELECT
    CounterID,
    hits,
    visits
FROM
(
    SELECT
        CounterID,
        count() AS hits
    FROM VALUES('CounterID UInt32', 1, 1, 1, 2, 2, 3)
    GROUP BY CounterID
) AS h
ANY LEFT JOIN
(
    SELECT
        CounterID,
        sum(Sign) AS visits
    FROM VALUES('CounterID UInt32, Sign Int8', (1, 1), (1, 1), (1, -1), (2, 1), (4, 1))
    GROUP BY CounterID
) AS v USING (CounterID)
ORDER BY hits DESC;

┌─CounterID─┬─hits─┬─visits─┐
│         1 │    3 │      1 │
│         2 │    2 │      1 │
│         3 │    1 │      0 │
└───────────┴──────┴────────┘

​

Settings

​

Related content

• Using JOINs in ClickHouse — JOIN types and strictness, with diagrams
• How ClickHouse executes JOINs — query planning
• JOIN algorithms — how each algorithm runs, and which JOIN types it supports
• JOIN memory management and spilling — memory limits and spilling to disk
• JOIN Settings — the session settings that affect JOINs
• Minimize and optimize JOINs — best practices, with benchmarked examples
• IN operator — tests whether a value is in the result of a subquery
• Dictionaries — an alternative to joining a small dimension table
• Join table engine — keeps a prepared right-hand side in memory
• Blog: ClickHouse: A Blazingly Fast DBMS with Full SQL Join Support - Part 1
• Blog: ClickHouse: A Blazingly Fast DBMS with Full SQL Join Support - Under the Hood - Part 2
• Blog: ClickHouse: A Blazingly Fast DBMS with Full SQL Join Support - Under the Hood - Part 3
• Blog: ClickHouse: A Blazingly Fast DBMS with Full SQL Join Support - Under the Hood - Part 4