> ## Documentation Index > Fetch the complete documentation index at: https://private-7c7dfe99-fix-nav-issues.mintlify.site/llms.txt > Use this file to discover all available pages before exploring further. > Документация по операторам IN, за исключением операторов NOT IN, GLOBAL IN и GLOBAL NOT IN, которые рассматриваются отдельно # Операторы IN Операторы `IN`, `NOT IN`, `GLOBAL IN` и `GLOBAL NOT IN` рассматриваются отдельно, поскольку их функциональные возможности достаточно широки. Левая часть оператора — это либо один столбец, либо кортеж. Примеры: ```sql theme={null} SELECT UserID IN (123, 456) FROM ... SELECT (CounterID, UserID) IN ((34, 123), (101500, 456)) FROM ... ``` Если левая часть — это один столбец, входящий в индекс, а правая часть — набор констант, система использует индекс для обработки запроса. Не перечисляйте слишком много значений явно (то есть миллионы значений). Если набор данных велик, поместите его во временную таблицу (например, см. раздел [Внешние данные для обработки запросов](/ru/reference/engines/table-engines/special/external-data)) и используйте подзапрос. Правая часть оператора может представлять собой набор константных выражений, набор кортежей с константными выражениями (как показано в примерах выше), имя таблицы базы данных или подзапрос `SELECT` в скобках. Из соображений обратной совместимости, если правая часть представляет собой одно выражение `tuple`, оно может интерпретироваться либо как набор значений, либо как одно значение кортежа — в зависимости от левой части оператора `IN`. Если левая часть является скалярным значением, ClickHouse рассматривает элементы этого единственного выражения `tuple` в правой части как отдельные значения `IN`: ```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 │ └──────────────┴──────────────┴────────────────┘ ``` Это работает аналогично `SELECT 1 IN (1, 2)`. Если левая часть также является кортежем, правая часть интерпретируется как набор значений кортежа: ```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 │ └────────────────┘ ``` Эта специальная обработка применяется только в том случае, если правая часть является единственным выражением `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 допускает различие типов в левой и правой частях подзапроса `IN`. В этом случае значение правой части приводится к типу левой части, как если бы к правой части была применена функция [accurateCastOrNull](/ru/reference/functions/regular-functions/type-conversion-functions#accurateCastOrNull). Это означает, что тип данных становится [Nullable](/ru/reference/data-types/nullable), и если преобразование невозможно, возвращается [NULL](/ru/reference/settings/formats#input_format_null_as_default). **Пример** ```sql title="Query" theme={null} SELECT '1' IN (SELECT 1); ``` ```text title="Response" theme={null} ┌─in('1', _subquery49)─┐ │ 1 │ └──────────────────────┘ ``` Если правая часть оператора является именем таблицы (например, `UserID IN users`), это эквивалентно подзапросу `UserID IN (SELECT * FROM users)`. Используйте это при работе с внешними данными, передаваемыми вместе с запросом. Например, запрос можно отправить вместе с набором идентификаторов пользователей, загруженных во временную таблицу 'users', по которой нужно выполнить фильтрацию. Если правая часть оператора — это имя таблицы с движком Set (подготовленный набор данных, который всегда хранится в оперативной памяти), набор данных не будет создаваться заново при каждом запросе. Подзапрос может задавать более одного столбца для фильтрации Tuple. Пример: ```sql title="Query" theme={null} SELECT (CounterID, UserID) IN (SELECT CounterID, UserID FROM ...) FROM ... ``` Столбцы слева и справа от оператора `IN` должны иметь одинаковый тип. Оператор `IN` и подзапрос могут находиться в любой части запроса, в том числе в агрегатных функциях и лямбда-функциях. Пример: ```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 │ └────────────┴──────────┘ ``` Для каждого дня после 17 марта вычислите долю просмотров страниц, совершённых пользователями, которые посещали сайт 17 марта. Подзапрос в условии `IN` всегда выполняется только один раз на одном сервере. Зависимых подзапросов нет.
## Обработка NULL
При обработке запроса оператор `IN` считает, что результат операции с [NULL](/ru/reference/settings/formats#input_format_null_as_default) всегда равен `0`, независимо от того, находится `NULL` справа или слева от оператора. Значения `NULL` не включаются ни в один набор данных, не считаются равными друг другу и не могут сравниваться, если [transform\_null\_in = 0](/ru/reference/settings/session-settings#transform_null_in). Вот пример с таблицей `t_null`: ```text theme={null} ┌─x─┬────y─┐ │ 1 │ ᴺᵁᴸᴸ │ │ 2 │ 3 │ └───┴──────┘ ``` В результате выполнения запроса `SELECT x FROM t_null WHERE y IN (NULL,3)` вы получите следующий результат: ```text theme={null} ┌─x─┐ │ 2 │ └───┘ ``` Можно видеть, что строка, в которой `y = NULL`, исключается из результатов запроса. Это происходит потому, что ClickHouse не может определить, входит ли `NULL` в множество `(NULL,3)`, возвращает `0` в качестве результата операции, а `SELECT` исключает эту строку из итогового вывода. ```sql theme={null} SELECT y IN (NULL, 3) FROM t_null ``` ```text theme={null} ┌─in(y, tuple(NULL, 3))─┐ │ 0 │ │ 1 │ └───────────────────────┘ ```
## Распределённые подзапросы
Для операторов `IN` с подзапросами (аналогично операторам `JOIN`) существует два варианта: обычный `IN` / `JOIN` и `GLOBAL IN` / `GLOBAL JOIN`. Они различаются способом выполнения при распределённой обработке запросов. Помните, что описанные ниже алгоритмы могут работать по-разному в зависимости от значения настройки `distributed_product_mode` из раздела [настроек](/ru/reference/settings/session-settings). При использовании обычного `IN` запрос отправляется на удалённые серверы, и каждый из них выполняет подзапросы в секции `IN` или `JOIN`. При использовании `GLOBAL IN` / `GLOBAL JOIN` сначала выполняются все подзапросы для `GLOBAL IN` / `GLOBAL JOIN`, а результаты сохраняются во временные таблицы. Затем эти временные таблицы отправляются на каждый удалённый сервер, где запросы выполняются с использованием полученных временных данных. Для `GLOBAL ... JOIN` то, какая сторона JOIN вычисляется как подзапрос, зависит от типа JOIN: для `LEFT` и `INNER` JOIN вычисляется правая таблица; для `RIGHT` JOIN вместо неё вычисляется левая таблица, поскольку правая таблица является сохраняемой стороной и должна считываться из сегментов. Для нераспределённого запроса используйте обычные `IN` / `JOIN`. Будьте осторожны при использовании подзапросов в секциях `IN` / `JOIN` при распределённой обработке запросов. Рассмотрим несколько примеров. Предположим, что на каждом сервере кластера есть обычная таблица **local\_table**. Кроме того, на каждом сервере есть таблица **distributed\_table** с движком **Distributed**, которая охватывает все серверы кластера. При выполнении запроса к **distributed\_table** он будет отправлен на все удалённые серверы и выполнен на них с использованием **local\_table**. Например, запрос ```sql theme={null} SELECT uniq(UserID) FROM distributed_table ``` будет отправлен на все удалённые серверы как ```sql theme={null} SELECT uniq(UserID) FROM local_table ``` и выполняются на каждом из них параллельно, пока не будет достигнута стадия, на которой промежуточные результаты можно объединить. Затем промежуточные результаты возвращаются на сервер-инициатор запроса и объединяются на нём, после чего итоговый результат отправляется клиенту. Теперь рассмотрим запрос с `IN`: ```sql theme={null} SELECT uniq(UserID) FROM distributed_table WHERE CounterID = 101500 AND UserID IN (SELECT UserID FROM local_table WHERE CounterID = 34) ``` * Вычисление пересечения аудиторий двух сайтов. Этот запрос будет отправлен на все удалённые серверы как ```sql theme={null} SELECT uniq(UserID) FROM local_table WHERE CounterID = 101500 AND UserID IN (SELECT UserID FROM local_table WHERE CounterID = 34) ``` Иными словами, набор данных в выражении `IN` будет формироваться на каждом сервере независимо — только из тех данных, которые хранятся локально на этом сервере. Это будет работать корректно и оптимально, если вы заранее предусмотрели такой сценарий и распределили данные по серверам кластера так, что данные для каждого отдельного UserID целиком хранятся на одном сервере. В этом случае все необходимые данные будут доступны локально на каждом сервере. В противном случае результат окажется неточным. Такой вариант запроса называется "local IN". Чтобы исправить поведение запроса в случае, когда данные распределены случайным образом по серверам кластера, можно указать **distributed\_table** внутри подзапроса. Запрос будет выглядеть следующим образом: ```sql theme={null} SELECT uniq(UserID) FROM distributed_table WHERE CounterID = 101500 AND UserID IN (SELECT UserID FROM distributed_table WHERE CounterID = 34) ``` Этот запрос будет отправлен на все удалённые серверы как ```sql theme={null} SELECT uniq(UserID) FROM local_table WHERE CounterID = 101500 AND UserID IN (SELECT UserID FROM distributed_table WHERE CounterID = 34) ``` Подзапрос начнёт выполняться на каждом удалённом сервере. Поскольку подзапрос использует distributed таблицу, подзапрос на каждом удалённом сервере будет повторно отправлен на все удалённые серверы в виде: ```sql theme={null} SELECT UserID FROM local_table WHERE CounterID = 34 ``` Например, при наличии кластера из 100 серверов выполнение всего запроса потребует 10 000 элементарных обращений, что, как правило, считается неприемлемым. В таких случаях следует всегда использовать `GLOBAL IN` вместо `IN`. Рассмотрим, как это работает для следующего запроса: ```sql theme={null} SELECT uniq(UserID) FROM distributed_table WHERE CounterID = 101500 AND UserID GLOBAL IN (SELECT UserID FROM distributed_table WHERE CounterID = 34) ``` Сервер-инициатор выполнит подзапрос: ```sql theme={null} SELECT UserID FROM distributed_table WHERE CounterID = 34 ``` и результат будет помещён во временную таблицу в оперативной памяти. Затем запрос будет отправлен на каждый удалённый сервер в виде: ```sql theme={null} SELECT uniq(UserID) FROM local_table WHERE CounterID = 101500 AND UserID GLOBAL IN _data1 ``` Временная таблица `_data1` будет отправлена на каждый удаленный сервер вместе с запросом (имя временной таблицы зависит от реализации). Это более эффективно, чем использование обычного `IN`. Однако имейте в виду следующее: 1. При создании временной таблицы данные не становятся уникальными. Чтобы уменьшить объем данных, передаваемых по сети, укажите DISTINCT в подзапросе. (Для обычного `IN` этого делать не нужно.) 2. Временная таблица будет отправлена на все удаленные серверы. При передаче не учитывается топология сети. Например, если 10 удаленных серверов находятся в датацентре, значительно удаленном от сервера, инициировавшего запрос, данные будут 10 раз переданы по каналу в этот удаленный датацентр. Старайтесь избегать больших наборов данных при использовании `GLOBAL IN`. 3. При передаче данных на удаленные серверы ограничения пропускной способности сети не настраиваются. Это может привести к перегрузке сети. 4. Старайтесь распределять данные по серверам так, чтобы не приходилось регулярно использовать `GLOBAL IN`. 5. Если вам часто требуется использовать `GLOBAL IN`, спланируйте размещение кластера ClickHouse так, чтобы одна группа реплик находилась не более чем в одном датацентре, а между ними была быстрая сеть, — тогда запрос можно будет полностью обработать в пределах одного датацентра. Также имеет смысл указать локальную таблицу в условии `GLOBAL IN`, если эта локальная таблица доступна только на сервере, инициировавшем запрос, и вы хотите использовать данные из нее на удаленных серверах.
### Распределённые подзапросы и max\_rows\_in\_set
Вы можете использовать [`max_rows_in_set`](/ru/reference/settings/session-settings#max_rows_in_set) и [`max_bytes_in_set`](/ru/reference/settings/session-settings#max_bytes_in_set), чтобы управлять объёмом данных, передаваемых при распределённых запросах. Это особенно важно, если запрос `GLOBAL IN` возвращает большой объём данных. Рассмотрим следующий SQL: ```sql theme={null} SELECT * FROM table1 WHERE col1 GLOBAL IN (SELECT col1 FROM table2 WHERE ) ``` Если `some_predicate` недостаточно селективен, он вернет большой объем данных, что приведет к проблемам с производительностью. В таких случаях стоит ограничить передачу данных по сети. Также обратите внимание, что [`set_overflow_mode`](/ru/reference/settings/session-settings#set_overflow_mode) имеет значение `throw` (по умолчанию), то есть при достижении этих пороговых значений генерируется исключение.
### Распределённые подзапросы и max\_parallel\_replicas
Когда [max\_parallel\_replicas](#distributed-subqueries-and-max_parallel_replicas) больше 1, распределённые запросы дополнительно преобразуются. Например: ```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 ``` на каждом сервере преобразуется в: ```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 ``` где `M` находится в диапазоне от `1` до `3` в зависимости от того, на какой реплике выполняется локальный запрос. Эти настройки влияют на каждую таблицу семейства MergeTree в запросе и дают тот же эффект, что и применение `SAMPLE 1/3 OFFSET (M-1)/3` к каждой из них. Поэтому добавление настройки [max\_parallel\_replicas](#distributed-subqueries-and-max_parallel_replicas) даст корректные результаты только в том случае, если обе таблицы используют одну и ту же схему репликации и сэмплируются по UserID или по одному из его подключей. В частности, если у `local_table_2` нет ключа выборки, результаты будут некорректными. То же правило применяется и к `JOIN`. Один из способов обойти это ограничение, если `local_table_2` не удовлетворяет этим требованиям, — использовать `GLOBAL IN` или `GLOBAL JOIN`. Если у таблицы нет ключа выборки, можно использовать более гибкие варианты [parallel\_replicas\_custom\_key](/ru/reference/settings/session-settings#parallel_replicas_custom_key), которые могут давать иное и более оптимальное поведение.