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>) ...

​

Types de JOIN pris en charge

• JOIN sans type spécifié implique INNER.
• Le mot-clé OUTER peut être omis sans risque.
• Une syntaxe alternative pour CROSS JOIN consiste à spécifier plusieurs tables dans la clause FROM, séparées par des virgules.
• S’il n’y a pas de colonnes correspondantes pour un NATURAL JOIN, il fonctionne comme un CROSS JOIN.

​

LATERAL JOIN

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

• Uniquement INNER JOIN LATERAL et LEFT JOIN LATERAL ; les jointures RIGHT, FULL, PASTE et NATURAL ne sont pas prises en charge, et LATERAL ne peut en aucun cas être combiné avec une jointure CROSS ou une jointure par virgule.
• Uniquement la strictness ALL par défaut ; ANY, SEMI, ANTI et ASOF ne sont pas pris en charge.
• Aucun prédicat de jointure autre que ON true (ON 1 est également accepté) ; USING n’est pas pris en charge, et le prédicat ne peut pas être omis. Placez les filtres qui relient les deux côtés dans la clause WHERE de la sous-requête latérale.
• Les modificateurs de jointure GLOBAL et LOCAL ne sont pas pris en charge.
• La sous-requête latérale doit référencer au moins une colonne du côté gauche. Pour une sous-requête non corrélée, utilisez une jointure classique.
• La sous-requête latérale est évaluée une fois par valeur distincte des colonnes du côté gauche qu’elle référence, et non une fois par ligne de gauche ; elle ne doit donc pas contenir de fonctions non déterministes au sein d’une requête, telles que rand ou generateUUIDv4, ni de fonctions de table générant des lignes aléatoires, telles que generateRandom. Les fonctions constantes au sein d’une requête, telles que now, sont autorisées.
• Seule une sous-requête est prise en charge comme expression de table latérale. Les formes de source de table PostgreSQL LATERAL unnest(...) et CROSS JOIN UNNEST(...) ne sont pas prises en charge : utilisez plutôt la clause ARRAY JOIN.
• Les clauses GROUP BY et ORDER BY de la sous-requête latérale s’exécutent une seule fois sur l’ensemble des évaluations ; les limites max_rows_to_group_by, max_rows_to_sort et max_bytes_to_sort comptabilisent donc les lignes de toutes les évaluations, et non d’une seule. Elles ne sont prises en charge qu’avec le mode de dépassement de capacité throw ; any et break sont rejetés.
• Les lignes de toutes les évaluations sont mises en correspondance avec les lignes de gauche par une seule jointure. Comme pour toute jointure de hachage, max_rows_in_join et max_bytes_in_join limitent le côté de cette jointure conservé en mémoire, et c’est le planificateur qui choisit ce côté : avec les paramètres par défaut (correlated_subqueries_use_in_memory_buffer = 1), il s’agit du côté gauche de JOIN LATERAL, car les lignes de gauche doivent être entièrement lues avant l’évaluation de la sous-requête latérale ; sinon, il peut s’agir des résultats de l’ensemble des évaluations. Les limites sont toujours appliquées comme si join_overflow_mode valait throw : avec break, la jointure supprimerait silencieusement des lignes de gauche sans rapport.

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 allow_experimental_lateral_join = 1;

​

Paramètres

• join_algorithm
• join_any_take_last_row
• join_use_nulls
• partial_merge_join_rows_in_right_blocks
• join_on_disk_max_files_to_merge
• any_join_distinct_right_table_keys

​

Conditions de la section ON

• faire référence à la fois aux tables de gauche et de droite
• utiliser l’opérateur d’égalité

┌─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 avec des conditions d’inégalité sur des colonnes de tables différentes

┌─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 avec une condition ON arbitraire

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 │
└────┴────────┴─────┘

​

Valeurs NULL et NaN dans les clés de jointure

┌───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 │
└─────────┴───────┘

​

Utilisation de ASOF JOIN

• Doit contenir une séquence ordonnée.
• Peut être de l’un des types suivants : Int, UInt, Float, Date, DateTime, Decimal.
• Pour l’algorithme de JOIN hash, elle ne peut pas être la seule colonne de la clause JOIN.

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
                  ...                               ...

​

Utilisation de PASTE JOIN

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 │
└───┴──────┘

​

JOIN distribué

• Lorsqu’on utilise un JOIN normal, la requête est envoyée aux serveurs distants. Les sous-requêtes sont exécutées sur chacun d’eux afin de constituer la table de droite, puis le JOIN est effectué avec cette table. Autrement dit, la table de droite est constituée séparément sur chaque serveur.
• Lorsqu’on utilise GLOBAL ... JOIN, le serveur à l’origine de la requête exécute d’abord une sous-requête pour calculer l’un des côtés du JOIN et rassemble le résultat dans une table temporaire. Cette table temporaire est ensuite transmise à chaque serveur distant, et les requêtes y sont exécutées à l’aide des données temporaires transmises. Pour les JOIN LEFT et INNER, la table de droite est calculée par la sous-requête. Pour les JOIN RIGHT, c’est la table de gauche qui est calculée, puisque la table de droite est celle qui est conservée et doit être lue depuis les shards.

​

Conversion implicite de type

┌─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);

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

​

Conseils d’utilisation

​

Traitement des cellules vides ou NULL

​

Syntaxe

​

Limites de la syntaxe

• La sélection de toutes les colonnes via * n’est possible que lorsque des tables sont jointes, et non des sous-requêtes.
• La clause PREWHERE n’est pas disponible.
• La clause USING n’est pas disponible.

• Les expressions arbitraires ne peuvent pas être utilisées dans les clauses ON, WHERE et GROUP BY, mais vous pouvez définir une expression dans une clause SELECT, puis l’utiliser dans ces clauses via un alias.

​

Performance

​

Limitations de mémoire

• max_rows_in_join — Limite le nombre de lignes dans la table de hachage.
• max_bytes_in_join — Limite la taille de la table de hachage.

• max_bytes_before_external_join — Seuil de déversement absolu.
• max_bytes_ratio_before_external_join — Seuil de déversement exprimé en ratio de la mémoire disponible.

​

Exemples

SELECT
    CounterID,
    hits,
    visits
FROM
(
    SELECT
        CounterID,
        count() AS hits
    FROM test.hits
    GROUP BY CounterID
) ANY LEFT JOIN
(
    SELECT
        CounterID,
        sum(Sign) AS visits
    FROM test.visits
    GROUP BY CounterID
) USING CounterID
ORDER BY hits DESC
LIMIT 10

┌─CounterID─┬───hits─┬─visits─┐
│   1143050 │ 523264 │  13665 │
│    731962 │ 475698 │ 102716 │
│    722545 │ 337212 │ 108187 │
│    722889 │ 252197 │  10547 │
│   2237260 │ 196036 │   9522 │
│  23057320 │ 147211 │   7689 │
│    722818 │  90109 │  17847 │
│     48221 │  85379 │   4652 │
│  19762435 │  77807 │   7026 │
│    722884 │  77492 │  11056 │
└───────────┴────────┴────────┘

​

Contenu connexe

• Blog : ClickHouse : un SGBD ultra-rapide avec une prise en charge complète des jointures SQL - Partie 1
• Blog : ClickHouse : un SGBD ultra-rapide avec une prise en charge complète des jointures SQL - Dans les coulisses - Partie 2
• Blog : ClickHouse : un SGBD ultra-rapide avec une prise en charge complète des jointures SQL - Dans les coulisses - Partie 3
• Blog : ClickHouse : un SGBD ultra-rapide avec une prise en charge complète des jointures SQL - Dans les coulisses - Partie 4