Clause JOIN - ClickHouse Documentation

La clause JOIN crée une nouvelle table en combinant des colonnes d’une ou de plusieurs tables à partir de valeurs communes. Il s’agit d’une opération courante dans les bases de données compatibles SQL, qui correspond à la jointure en algèbre relationnelle. Le cas particulier où une table est jointe à elle-même est souvent appelé « self-join ». Syntaxe

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

Les expressions de la clause ON et les colonnes de la clause USING sont appelées « clés de jointure ». Sauf indication contraire, un JOIN produit un produit cartésien à partir des lignes dont les « clés de jointure » correspondent, ce qui peut donner des résultats comportant bien plus de lignes que les tables sources.

Types de JOIN pris en charge

Tous les types standard de SQL JOIN sont pris en charge :

Type	Description
`INNER JOIN`	seules les lignes correspondantes sont renvoyées.
`LEFT OUTER JOIN`	les lignes non correspondantes de la table de gauche sont renvoyées en plus des lignes correspondantes.
`RIGHT OUTER JOIN`	les lignes non correspondantes de la table de droite sont renvoyées en plus des lignes correspondantes.
`FULL OUTER JOIN`	les lignes non correspondantes des deux tables sont renvoyées en plus des lignes correspondantes.
`CROSS JOIN`	produit le produit cartésien des tables entières ; les « clés de jointure » ne sont pas spécifiées.
`NATURAL JOIN`	effectue automatiquement la jointure sur toutes les colonnes portant le même nom dans les deux tables ; chaque colonne commune apparaît une seule fois dans le résultat. Prend en charge les variantes `INNER` (par défaut), `LEFT`, `RIGHT` et `FULL`. Équivalent à `JOIN ... USING (col1, col2, ...)`, où la liste des colonnes est déduite automatiquement.

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.

D’autres types de jointure disponibles dans ClickHouse sont :

Type	Description
`LEFT SEMI JOIN`, `RIGHT SEMI JOIN`	Une liste d’autorisation sur les « clés de jointure », sans produire de produit cartésien.
`LEFT ANTI JOIN`, `RIGHT ANTI JOIN`	Une liste d’exclusion sur les « clés de jointure », sans produire de produit cartésien.
`LEFT ANY JOIN`, `RIGHT ANY JOIN`, `INNER ANY JOIN`	Désactive partiellement (pour le côté opposé de `LEFT` et `RIGHT`) ou complètement (pour `INNER` et `FULL`) le produit cartésien pour les types de `JOIN` standard.
`ASOF JOIN`, `LEFT ASOF JOIN`	Joint des séquences avec une correspondance non exacte. L’utilisation de `ASOF JOIN` est décrite ci-dessous.
`PASTE JOIN`	Effectue une concaténation horizontale de deux tables.

Lorsque join_algorithm est défini sur partial_merge, RIGHT JOIN et FULL JOIN sont pris en charge uniquement avec la strictness ALL (SEMI, ANTI, ANY et ASOF ne sont pas pris en charge).

Paramètres

Le type de jointure par défaut peut être remplacé à l’aide du paramètre join_default_strictness. Le comportement du serveur ClickHouse pour les opérations ANY JOIN dépend du paramètre any_join_distinct_right_table_keys. Voir aussi

Utilisez le paramètre cross_to_inner_join_rewrite pour définir le comportement lorsque ClickHouse ne parvient pas à réécrire un CROSS JOIN en INNER JOIN. La valeur par défaut est 1, ce qui permet à la jointure de se poursuivre, mais plus lentement. Définissez cross_to_inner_join_rewrite sur 0 si vous voulez qu’une erreur soit générée, et sur 2 pour ne pas exécuter les jointures CROSS, mais forcer à la place la réécriture de toutes les jointures par virgule/CROSS. Si la réécriture échoue lorsque la valeur est 2, vous recevrez un message d’erreur indiquant “Please, try to simplify WHERE section”.

Conditions de la section ON

Une section ON peut contenir plusieurs conditions combinées à l’aide des opérateurs AND et OR. Les conditions qui spécifient des clés de jointure doivent :

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

Les autres conditions peuvent utiliser d’autres opérateurs logiques, mais elles doivent faire référence soit à la table de gauche, soit à la table de droite d’une requête. Les lignes sont jointes si l’ensemble de la condition complexe est rempli. Si les conditions ne sont pas remplies, des lignes peuvent tout de même être incluses dans le résultat selon le type de JOIN. Notez que si les mêmes conditions sont placées dans une section WHERE et qu’elles ne sont pas remplies, les lignes sont alors toujours exclues du résultat. L’opérateur OR à l’intérieur de la clause ON fonctionne à l’aide de l’algorithme de jointure par hachage : pour chaque argument OR avec des clés de jointure pour JOIN, une table de hachage distincte est créée, de sorte que la consommation de mémoire et le temps d’exécution de la requête augmentent linéairement avec le nombre d’expressions OR dans la clause ON.

Si une condition fait référence à des colonnes provenant de tables différentes, alors seul l’opérateur d’égalité (=) est actuellement pris en charge.

Exemple Considérons table_1 et table_2 :

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

Requête avec une condition sur la clé de jointure et une condition supplémentaire sur table_2 :

Query

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

Notez que le résultat contient la ligne nommée C ainsi que la colonne de texte vide. Elle est incluse dans le résultat, car une jointure de type OUTER est utilisée.

Response

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

Requête avec une jointure de type INNER et plusieurs conditions :

Query

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');

Response

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

Requête avec une jointure de type INNER et une condition avec OR :

Query

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;

Response

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

Requête avec une jointure de type INNER et des conditions avec OR et AND :

Par défaut, les conditions d’inégalité sont prises en charge tant qu’elles utilisent des colonnes de la même table. Par exemple, t1.a = t2.key AND t1.b > 0 AND t2.b > t2.c, car t1.b > 0 n’utilise que des colonnes de t1 et t2.b > t2.c n’utilise que des colonnes de t2. Cependant, vous pouvez essayer la prise en charge expérimentale de conditions telles que t1.a = t2.key AND t1.b > t2.key ; consultez la section ci-dessous pour plus de détails.

Query

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

Response

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

ClickHouse prend actuellement en charge ALL/ANY/SEMI/ANTI INNER/LEFT/RIGHT/FULL JOIN avec des conditions d’inégalité, en plus des conditions d’égalité. Les conditions d’inégalité sont prises en charge uniquement par les algorithmes de jointure hash et grace_hash. Elles ne sont pas prises en charge avec join_use_nulls. Exemple Table t1:

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

Table t2

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

Valeurs NULL dans les clés de jointure

NULL n’est égal à aucune valeur, pas même à lui-même. Cela signifie que si une clé de JOIN contient une valeur NULL dans une table, elle ne correspondra pas à une valeur NULL dans l’autre table. Exemple Table A :

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

Table B :

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

Notez que la ligne avec Charlie de la table A et la ligne avec le score 88 de la table B n’apparaissent pas dans le résultat en raison de la valeur NULL dans la clé de JOIN. Si vous souhaitez faire correspondre des valeurs NULL, utilisez la fonction isNotDistinctFrom pour comparer les clés de JOIN.

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

ASOF JOIN est utile lorsque vous devez joindre des enregistrements qui n’ont pas de correspondance exacte. Cet algorithme de JOIN nécessite une colonne spéciale dans les tables. Cette colonne :

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.

Syntaxe ASOF JOIN ... ON :

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

Vous pouvez utiliser autant de conditions d’égalité que nécessaire et exactement une condition de correspondance la plus proche. Par exemple, SELECT count() FROM table_1 ASOF LEFT JOIN table_2 ON table_1.a == table_2.b AND table_2.t <= table_1.t. Conditions prises en charge pour la correspondance la plus proche : >, >=, <, <=. Syntaxe ASOF JOIN ... USING :

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

ASOF JOIN utilise equi_columnX pour les correspondances exactes et asof_column pour la correspondance la plus proche avec la condition table_1.asof_column >= table_2.asof_column. La colonne asof_column est toujours la dernière de la clause USING. Par exemple, considérons les tables suivantes :

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

ASOF JOIN peut prendre le timestamp d’un événement utilisateur de table_1 et trouver un événement dans table_2 dont le timestamp est le plus proche de celui de l’événement de table_1, selon la condition de correspondance la plus proche. Des valeurs de timestamp identiques sont considérées comme les plus proches lorsqu’elles sont disponibles. Ici, la colonne user_id peut être utilisée pour une jointure par égalité, et la colonne ev_time pour une jointure sur la correspondance la plus proche. Dans notre exemple, event_1_1 peut être joint à event_2_1 et event_1_2 à event_2_3, mais event_2_2 ne peut pas être joint.

ASOF JOIN est pris en charge uniquement par les algorithmes de jointure hash et full_sorting_merge. Il n’est pas pris en charge par le moteur de table Join.

Utilisation de PASTE JOIN

Le résultat de PASTE JOIN est une table qui contient toutes les colonnes de la sous-requête de gauche, suivies de toutes les colonnes de la sous-requête de droite. Les lignes sont appariées en fonction de leur position dans les tables d’origine (l’ordre des lignes doit être défini). Si les sous-requêtes renvoient un nombre différent de lignes, les lignes supplémentaires seront tronquées. Exemple :

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

Note : dans ce cas, le résultat peut être non déterministe si la lecture s’effectue en parallèle. Par exemple :

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é

Il existe deux façons d’exécuter un JOIN impliquant des tables distribuées :

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.

Soyez prudent lorsque vous utilisez GLOBAL. Pour plus d’informations, consultez la section Sous-requêtes distribuées.

Conversion implicite de type

Les requêtes INNER JOIN, LEFT JOIN, RIGHT JOIN et FULL JOIN prennent en charge la conversion implicite de type pour les clés de jointure. Toutefois, la requête ne peut pas être exécutée si les clés de jointure des tables de gauche et de droite ne peuvent pas être converties vers un même type (par exemple, il n’existe aucun type de données pouvant contenir toutes les valeurs de UInt64 et Int64, ou de String et Int32). Exemple Considérez la table t_1 :

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

et la table t_2 :

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

La requête

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

renvoie l’ensemble suivant :

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

Lors d’une jointure entre tables, des cellules vides peuvent apparaître. Le paramètre join_use_nulls définit comment ClickHouse remplit ces cellules. Si les clés de JOIN sont des champs Nullable, les lignes pour lesquelles au moins une des clés a la valeur NULL ne sont pas prises en compte dans la jointure.

Syntaxe

Les colonnes spécifiées dans USING doivent avoir les mêmes noms dans les deux sous-requêtes, et les autres colonnes doivent porter des noms différents. Vous pouvez utiliser des alias pour renommer les colonnes dans les sous-requêtes. La clause USING spécifie une ou plusieurs colonnes de jointure, ce qui établit l’égalité entre ces colonnes. La liste des colonnes s’écrit sans parenthèses. Les conditions de jointure plus complexes ne sont pas prises en charge.

Limites de la syntaxe

Pour plusieurs clauses JOIN dans une même requête SELECT :

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.

Pour les clauses ON, WHERE et GROUP BY :

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

Lors de l’exécution d’un JOIN, l’ordre d’exécution n’est pas optimisé par rapport aux autres étapes de la requête. La jointure (recherche dans la table de droite) est exécutée avant le filtrage dans WHERE et avant l’agrégation. Chaque fois qu’une requête est exécutée avec le même JOIN, la sous-requête est relancée, car le résultat n’est pas mis en cache. Pour éviter cela, utilisez le moteur de table spécial Join, qui est une structure préparée pour les jointures et qui réside toujours en RAM. Dans certains cas, il est plus efficace d’utiliser IN plutôt que JOIN. Si vous avez besoin d’un JOIN avec des tables de dimension (des tables relativement petites qui contiennent des propriétés de dimension, comme des noms de campagnes publicitaires), un JOIN peut ne pas être très pratique, car la table de droite est de nouveau consultée pour chaque requête. Dans ce type de cas, il existe la fonctionnalité « dictionnaires », à utiliser à la place de JOIN. Pour plus d’informations, consultez la section Dictionnaires.

Limitations de mémoire

Par défaut, ClickHouse utilise l’algorithme de jointure par hachage. ClickHouse prend la table de droite et crée une table de hachage en RAM pour celle-ci. Si join_algorithm = 'auto' est activé, alors au-delà d’un certain seuil de consommation de mémoire, ClickHouse bascule vers l’algorithme de jointure par fusion. Pour une description des algorithmes de jointure, consultez le paramètre join_algorithm. Si vous devez limiter la consommation de mémoire de l’opération JOIN, utilisez les paramètres suivants :

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.

Lorsque l’une de ces limites est atteinte, ClickHouse se comporte conformément aux instructions du paramètre join_overflow_mode.

Exemples

Exemple :

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

​Types de JOIN pris en charge

​Paramètres

​Conditions de la section ON

​JOIN avec des conditions d’inégalité sur des colonnes de tables différentes

​Valeurs NULL dans les clés de jointure

​Utilisation de ASOF JOIN

​Utilisation de PASTE JOIN

​JOIN distribué

​Conversion implicite de type

​Conseils d’utilisation

​Traitement des cellules vides ou NULL

​Syntaxe

​Limites de la syntaxe

​Performance

​Limitations de mémoire

​Exemples

​Contenu connexe

Types de JOIN pris en charge

Paramètres

Conditions de la section ON

JOIN avec des conditions d’inégalité sur des colonnes de tables différentes

Valeurs NULL dans les clés de jointure

Utilisation de ASOF JOIN

Utilisation de PASTE JOIN

JOIN distribué

Conversion implicite de type

Conseils d’utilisation

Traitement des cellules vides ou NULL

Syntaxe

Limites de la syntaxe

Performance

Limitations de mémoire

Exemples

Contenu connexe