> ## Documentation Index > Fetch the complete documentation index at: https://private-7c7dfe99-mintlify-55d9d317.mintlify.site/llms.txt > Use this file to discover all available pages before exploring further. > Documentation pour les opérateurs IN, à l'exclusion des opérateurs NOT IN, GLOBAL IN et GLOBAL NOT IN qui sont traités séparément # Opérateurs IN 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 : ```sql theme={null} 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](/fr/reference/engines/table-engines/special/external-data)), 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 : ```sql title="Query" theme={null} 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; ``` ```text title="Response" theme={null} ┌─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 : ```sql title="Query" theme={null} SELECT tuple(1, 2) IN (tuple(1, 2)) AS tuple_in_tuple; ``` ```text title="Response" theme={null} ┌─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 : ```sql title="Query" theme={null} SELECT 1 IN (tuple(1, 2), tuple(3, 4)); ``` ```text title="Response" theme={null} 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](/fr/reference/functions/regular-functions/type-conversion-functions#accurateCastOrNull) était appliquée au côté droit. Cela signifie que le type de données devient [Nullable](/fr/reference/data-types/nullable) et, si la conversion ne peut pas être effectuée, renvoie [NULL](/fr/reference/settings/formats#input_format_null_as_default). **Exemple** ```sql title="Query" theme={null} SELECT '1' IN (SELECT 1); ``` ```text title="Response" theme={null} ┌─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 : ```sql title="Query" theme={null} 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 : ```sql title="Query" theme={null} 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 ``` ```text title="Response" theme={null} ┌──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](/fr/reference/settings/formats#input_format_null_as_default) 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](/fr/reference/settings/session-settings#transform_null_in). Voici un exemple avec la table `t_null` : ```text theme={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 : ```text theme={null} ┌─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. ```sql theme={null} SELECT y IN (NULL, 3) FROM t_null ``` ```text theme={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](/fr/reference/settings/session-settings). 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 ```sql theme={null} SELECT uniq(UserID) FROM distributed_table ``` sera envoyé à tous les serveurs distants en tant que ```sql theme={null} 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` : ```sql theme={null} 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 ```sql theme={null} 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 : ```sql theme={null} 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 ```sql theme={null} 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 : ```sql theme={null} 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 : ```sql theme={null} 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 : ```sql theme={null} 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 : ```sql theme={null} 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`](/fr/reference/settings/session-settings#max_rows_in_set) et [`max_bytes_in_set`](/fr/reference/settings/session-settings#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 : ```sql theme={null} SELECT * FROM table1 WHERE col1 GLOBAL IN (SELECT col1 FROM table2 WHERE ) ``` 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`](/fr/reference/settings/session-settings#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](#distributed-subqueries-and-max_parallel_replicas) est supérieur à 1, les requêtes distribuées subissent des transformations supplémentaires. Par exemple : ```sql theme={null} 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 : ```sql theme={null} 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](#distributed-subqueries-and-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](/fr/reference/settings/session-settings#parallel_replicas_custom_key) peuvent être utilisées, ce qui peut produire un comportement différent et plus optimal.