Passer au contenu principal
Les opérateurs IN, NOT IN, GLOBAL IN et GLOBAL NOT IN sont traités séparément, car leurs fonctionnalités sont particulièrement riches. Le côté gauche de l’opérateur est soit une colonne unique, soit un Tuple. Exemples : 
SELECT UserID IN (123, 456) FROM ...
SELECT (CounterID, UserID) IN ((34, 123), (101500, 456)) FROM ...
Si le côté gauche est une colonne unique présente dans l’index et que le côté droit est un ensemble de constantes, le système utilise l’index pour traiter la requête. N’énumérez pas trop de valeurs explicitement (c’est-à-dire des millions). Si un jeu de données est volumineux, placez-le dans une table temporaire (par exemple, consultez la section Données externes pour le traitement des requêtes), puis utilisez une sous-requête. Le côté droit de l’opérateur peut être un ensemble d’expressions constantes, un ensemble de tuples avec des expressions constantes (comme indiqué dans les exemples ci-dessus), ou le nom d’une table de base de données ou une sous-requête SELECT entre crochets. Pour des raisons de compatibilité historique, lorsque le côté droit est une expression tuple unique, celle-ci peut être interprétée soit comme un ensemble de valeurs, soit comme une valeur de tuple, selon le côté gauche de l’opérateur IN. Si le côté gauche est une valeur scalaire, ClickHouse traite les éléments de cette expression tuple unique côté droit comme des valeurs IN distinctes :
Query
SELECT
    1 IN (tuple(1, 2)) AS one_in_tuple,
    2 IN (tuple(1, 2)) AS two_in_tuple,
    3 IN (tuple(1, 2)) AS three_in_tuple;
Response
┌─one_in_tuple─┬─two_in_tuple─┬─three_in_tuple─┐
│            1 │            1 │              0 │
└──────────────┴──────────────┴────────────────┘
Cela se comporte comme SELECT 1 IN (1, 2). Si le côté gauche est également un tuple, le côté droit est interprété comme un ensemble de valeurs de tuple :
Query
SELECT tuple(1, 2) IN (tuple(1, 2)) AS tuple_in_tuple;
Response
┌─tuple_in_tuple─┐
│              1 │
└────────────────┘
Ce traitement spécial s’applique uniquement lorsque le côté droit est une expression tuple unique. Un côté gauche scalaire ne peut pas être mis en correspondance avec un côté droit contenant plusieurs valeurs de tuple :
Query
SELECT 1 IN (tuple(1, 2), tuple(3, 4));
Response
Code: 43. DB::Exception: Unsupported types for IN. First argument type UInt8. Second argument type Tuple(Tuple(UInt8, UInt8), Tuple(UInt8, UInt8)). (ILLEGAL_TYPE_OF_ARGUMENT)
ClickHouse autorise des types différents dans les parties gauche et droite de la sous-requête IN. Dans ce cas, il convertit la valeur du côté droit vers le type du côté gauche, comme si la fonction accurateCastOrNull était appliquée au côté droit. Cela signifie que le type de données devient Nullable et, si la conversion ne peut pas être effectuée, renvoie NULL. Exemple
Query
SELECT '1' IN (SELECT 1);
Response
┌─in('1', _subquery49)─┐
│                    1 │
└──────────────────────┘
Si le côté droit de l’opérateur est le nom d’une table (par exemple, UserID IN users), cela est équivalent à la sous-requête UserID IN (SELECT * FROM users). Utilisez cette syntaxe lorsque vous travaillez avec des données externes envoyées avec la requête. Par exemple, la requête peut être envoyée avec un ensemble d’identifiants utilisateur chargés dans la table temporaire ‘users’, qui doit être filtrée. Si le côté droit de l’opérateur est un nom de table utilisant le moteur Set (un ensemble de données préparé toujours conservé en RAM), l’ensemble de données ne sera pas recréé à chaque requête. La sous-requête peut spécifier plusieurs colonnes pour filtrer les tuples. Exemple :
Query
SELECT (CounterID, UserID) IN (SELECT CounterID, UserID FROM ...) FROM ...
Les colonnes situées à gauche et à droite de l’opérateur IN doivent être du même type. L’opérateur IN et la sous-requête peuvent apparaître dans n’importe quelle partie de la requête, y compris dans les fonctions d’agrégation et les fonctions lambda. Exemple :
Query
SELECT
    EventDate,
    avg(UserID IN
    (
        SELECT UserID
        FROM test.hits
        WHERE EventDate = toDate('2014-03-17')
    )) AS ratio
FROM test.hits
GROUP BY EventDate
ORDER BY EventDate ASC
Response
┌──EventDate─┬────ratio─┐
│ 2014-03-17 │        1 │
│ 2014-03-18 │ 0.807696 │
│ 2014-03-19 │ 0.755406 │
│ 2014-03-20 │ 0.723218 │
│ 2014-03-21 │ 0.697021 │
│ 2014-03-22 │ 0.647851 │
│ 2014-03-23 │ 0.648416 │
└────────────┴──────────┘
Pour chaque jour après le 17 mars, calculez le pourcentage de consultations de pages effectuées par des utilisateurs ayant visité le site le 17 mars. Une sous-requête dans la clause IN est toujours exécutée une seule fois sur un seul serveur. Il n’existe pas de sous-requêtes dépendantes.

​
Traitement des NULL

Lors du traitement des requêtes, l’opérateur IN considère que le résultat d’une opération avec NULL est toujours égal à 0, que NULL se trouve à droite ou à gauche de l’opérateur. Les valeurs NULL ne sont incluses dans aucun ensemble de données, ne correspondent pas les unes aux autres et ne peuvent pas être comparées si transform_null_in = 0. Voici un exemple avec la table t_null : 
┌─x─┬────y─┐
│ 1 │ ᴺᵁᴸᴸ │
│ 2 │    3 │
└───┴──────┘
L’exécution de la requête SELECT x FROM t_null WHERE y IN (NULL,3) renvoie le résultat suivant : 
┌─x─┐
│ 2 │
└───┘
Vous pouvez constater que la ligne pour laquelle y = NULL est exclue du résultat de la requête. En effet, ClickHouse ne peut pas déterminer si NULL est inclus dans l’ensemble (NULL,3), renvoie donc 0 comme résultat de l’opération, et SELECT exclut cette ligne de la sortie finale. 
SELECT y IN (NULL, 3)
FROM t_null

┌─in(y, tuple(NULL, 3))─┐
│                     0 │
│                     1 │
└───────────────────────┘

​
Sous-requêtes distribuées

Il existe deux options pour les opérateurs IN avec des sous-requêtes (similaires aux opérateurs JOIN) : IN / JOIN classique et GLOBAL IN / GLOBAL JOIN. Elles se distinguent par leur mode d’exécution dans le cadre du traitement des requêtes distribuées.
N’oubliez pas que les algorithmes décrits ci-dessous peuvent se comporter différemment selon le paramètre distributed_product_mode des paramètres de session.
Lors de l’utilisation de l’opérateur IN standard, la requête est envoyée aux serveurs distants, et chacun d’eux exécute les sous-requêtes présentes dans la clause IN ou JOIN. Lors de l’utilisation de GLOBAL IN / GLOBAL JOIN, toutes les sous-requêtes sont d’abord exécutées pour GLOBAL IN / GLOBAL JOIN, et les résultats sont collectés dans des tables temporaires. Ces tables temporaires sont ensuite transmises à chaque serveur distant, où les requêtes sont exécutées à partir de ces données temporaires. Pour GLOBAL ... JOIN, le côté de la jointure calculé en tant que sous-requête dépend du type de jointure : pour les jointures LEFT et INNER, c’est la table de droite qui est calculée ; pour les jointures RIGHT, c’est la table de gauche qui est calculée à la place, car la table de droite est le côté préservé et doit être lue depuis les segments. Pour une requête non distribuée, utilisez les opérateurs IN / JOIN classiques. Soyez vigilant lors de l’utilisation de sous-requêtes dans les clauses IN / JOIN dans le cadre du traitement distribué des requêtes. Examinons quelques exemples. Supposons que chaque serveur du cluster dispose d’une table local_table normale. Chaque serveur possède également une table distributed_table de type Distributed, qui couvre l’ensemble des serveurs du cluster. Pour une requête adressée à la distributed_table, celle-ci sera envoyée à tous les serveurs distants et exécutée sur chacun d’eux via la local_table. Par exemple, la requête 
SELECT uniq(UserID) FROM distributed_table
sera envoyé à tous les serveurs distants en tant que 
SELECT uniq(UserID) FROM local_table
et s’exécutent sur chacun d’eux en parallèle, jusqu’à atteindre l’étape où les résultats intermédiaires peuvent être combinés. Ces résultats intermédiaires sont alors renvoyés au serveur demandeur et fusionnés sur celui-ci, puis le résultat final est transmis au client. Examinons maintenant une requête avec IN : 
SELECT uniq(UserID) FROM distributed_table WHERE CounterID = 101500 AND UserID IN (SELECT UserID FROM local_table WHERE CounterID = 34)
  • Calcul de l’intersection des audiences de deux sites.
Cette requête sera envoyée à tous les serveurs distants en tant que 
SELECT uniq(UserID) FROM local_table WHERE CounterID = 101500 AND UserID IN (SELECT UserID FROM local_table WHERE CounterID = 34)
En d’autres termes, le jeu de données de la clause IN sera collecté sur chaque serveur de manière indépendante, uniquement à partir des données stockées localement sur chacun des serveurs. Cela fonctionnera correctement et de manière optimale si vous avez anticipé ce cas et réparti les données sur les serveurs du cluster de façon à ce que les données d’un même UserID résident entièrement sur un seul serveur. Dans ce cas, toutes les données nécessaires seront disponibles localement sur chaque serveur. Dans le cas contraire, le résultat sera inexact. Nous désignons cette variante de la requête par “local IN”. Pour corriger le comportement de la requête lorsque les données sont réparties aléatoirement sur les serveurs du cluster, vous pouvez spécifier distributed_table dans une sous-requête. La requête se présenterait alors comme suit : 
SELECT uniq(UserID) FROM distributed_table WHERE CounterID = 101500 AND UserID IN (SELECT UserID FROM distributed_table WHERE CounterID = 34)
Cette requête sera envoyée à tous les serveurs distants en tant que 
SELECT uniq(UserID) FROM local_table WHERE CounterID = 101500 AND UserID IN (SELECT UserID FROM distributed_table WHERE CounterID = 34)
La sous-requête commencera à s’exécuter sur chaque serveur distant. Étant donné que la sous-requête utilise une table distribuée, la sous-requête présente sur chaque serveur distant sera renvoyée à tous les serveurs distants sous la forme suivante : 
SELECT UserID FROM local_table WHERE CounterID = 34
Par exemple, si vous disposez d’un cluster de 100 serveurs, l’exécution de la requête complète nécessitera 10 000 requêtes élémentaires, ce qui est généralement jugé inacceptable. Dans de tels cas, vous devez toujours utiliser GLOBAL IN plutôt que IN. Voyons comment cela fonctionne pour la requête : 
SELECT uniq(UserID) FROM distributed_table WHERE CounterID = 101500 AND UserID GLOBAL IN (SELECT UserID FROM distributed_table WHERE CounterID = 34)
Le serveur demandeur exécutera la sous-requête : 
SELECT UserID FROM distributed_table WHERE CounterID = 34
et le résultat sera placé dans une table temporaire en RAM. La requête sera ensuite envoyée à chaque serveur distant sous la forme : 
SELECT uniq(UserID) FROM local_table WHERE CounterID = 101500 AND UserID GLOBAL IN _data1
La table temporaire _data1 sera envoyée à chaque serveur distant avec la requête (le nom de la table temporaire dépend de l’implémentation). C’est plus efficace que d’utiliser un IN normal. Cependant, gardez les points suivants à l’esprit :
  1. Lors de la création d’une table temporaire, les données ne sont pas dédupliquées. Pour réduire le volume de données transmis sur le réseau, indiquez DISTINCT dans la sous-requête. (Vous n’avez pas besoin de le faire avec un IN normal.)
  2. La table temporaire sera envoyée à tous les serveurs distants. La transmission ne tient pas compte de la topologie du réseau. Par exemple, si 10 serveurs distants se trouvent dans un centre de données très éloigné du serveur à l’origine de la requête, les données seront envoyées 10 fois sur la liaison vers ce centre de données distant. Essayez d’éviter les jeux de données volumineux lorsque vous utilisez GLOBAL IN.
  3. Lors de la transmission des données aux serveurs distants, les limitations de bande passante réseau ne sont pas configurables. Vous risquez de surcharger le réseau.
  4. Essayez de répartir les données entre les serveurs afin de ne pas avoir à utiliser GLOBAL IN régulièrement.
  5. Si vous devez souvent utiliser GLOBAL IN, planifiez l’emplacement du cluster ClickHouse de sorte qu’un même groupe de répliques ne soit pas réparti sur plusieurs centres de données, et qu’il dispose d’un réseau rapide entre eux, afin qu’une requête puisse être traitée entièrement dans un seul centre de données.
Il peut également être judicieux de spécifier une table locale dans la clause GLOBAL IN, si cette table locale n’est disponible que sur le serveur à l’origine de la requête et que vous souhaitez utiliser ses données sur des serveurs distants.

​
Sous-requêtes distribuées et max_rows_in_set

Vous pouvez utiliser max_rows_in_set et max_bytes_in_set pour contrôler la quantité de données transférées lors de requêtes distribuées. C’est particulièrement important si la requête GLOBAL IN renvoie une grande quantité de données. Prenons l’instruction SQL suivante : 
SELECT * FROM table1 WHERE col1 GLOBAL IN (SELECT col1 FROM table2 WHERE <some_predicate>)
Si some_predicate n’est pas assez sélectif, il renverra un volume important de données, ce qui entraînera des problèmes de performances. Dans ce cas, il est judicieux de limiter le volume de données transférées via le réseau. Notez également que set_overflow_mode est défini sur throw (par défaut), ce qui signifie qu’une exception est levée lorsque ces seuils sont atteints.

​
Sous-requêtes distribuées et max_parallel_replicas

Lorsque max_parallel_replicas est supérieur à 1, les requêtes distribuées subissent des transformations supplémentaires. Par exemple : 
SELECT CounterID, count() FROM distributed_table_1 WHERE UserID IN (SELECT UserID FROM local_table_2 WHERE CounterID < 100)
SETTINGS max_parallel_replicas=3
est transformé sur chaque serveur en : 
SELECT CounterID, count() FROM local_table_1 WHERE UserID IN (SELECT UserID FROM local_table_2 WHERE CounterID < 100)
SETTINGS parallel_replicas_count=3, parallel_replicas_offset=M
où M est compris entre 1 et 3, selon la réplique sur laquelle la requête locale s’exécute. Ces paramètres affectent chaque table de la famille MergeTree dans la requête et ont le même effet que l’application de SAMPLE 1/3 OFFSET (M-1)/3 à chaque table. Par conséquent, l’ajout du paramètre max_parallel_replicas ne produira des résultats corrects que si les deux tables utilisent le même schéma de réplication et si UserID, ou l’une de ses sous-clés, sert de clé d’échantillonnage. En particulier, si local_table_2 n’a pas de clé d’échantillonnage, les résultats seront incorrects. La même règle s’applique à JOIN. Une solution de contournement, si local_table_2 ne remplit pas ces conditions, consiste à utiliser GLOBAL IN ou GLOBAL JOIN. Si une table n’a pas de clé d’échantillonnage, des options plus souples pour parallel_replicas_custom_key peuvent être utilisées, ce qui peut produire un comportement différent et plus optimal.
Dernière modification le 30 juin 2026