Подзапросы

Елена Дворядкина

Обзор

Подзапрос — это SQL-запрос, вложенный в другой (родительский) запрос или подзапрос. Сначала выполняется подзапрос, затем полученный результат передается родительскому запросу. Таким образом, подзапросы могут быть полезны, когда для выполнения запроса требуется результат другого запроса. Например, подзапросы можно использовать в запросах SELECT: в выражениях FROM и JOIN для определения источника, из которого должны читаться данные, или в выражении WHERE для фильтрации данных.

От типа возвращаемого подзапросом значения зависит то, для каких операций можно использовать подзапрос. Результатом выполнения подзапроса может быть:

  • Скалярное значение, то есть значение одного столбца из одной строки или одно вычисленное значение (например, значение агрегатной функции — сумма, среднее/минимальное/максимальное значение столбца). Такой подзапрос можно использовать с операторами сравнения >, <, = и др. (см. Comparison Operators в документации ClickHouse), передавать как аргумент функции или как значение столбца в операторе SELECT.

  • Табличное значение, то есть несколько строк (возможно ноль строк). Такой подзапрос можно использовать с операторами для работы с множествами (IN, NOT IN, ANY, ALL, EXISTS) — в этом случае будет проверяться вхождение строки родительского запроса в результат, возвращаемый подзапросом. Также табличное значение подзапроса можно использовать в выражении FROM или JOIN, чтобы указать таблицу-источник для чтения данных.

ПРИМЕЧАНИЕ

  • Синтаксически подзапрос представляет собой запрос SELECT, заключенный в круглые скобки.

  • Синонимы не передаются в подзапросы и между подзапросами.

Ниже в статье приведены различные варианты использования подзапросов в запросах SELECT.

Создание таблиц для тестовых примеров

 
Чтобы воспроизвести приведенные в данной статье примеры, создайте предварительно таблицы:

  • employees

    CREATE TABLE employees (
    id UInt16,
    full_name String,
    hire_date Date,
    position Enum('c developer' = 1,
                  'python developer' = 2,
                  'java developer' = 3,
                  'frontend developer' = 4,
                  'test automation engineer' = 5,
                  'manual testing engineer' = 6,
                  'load testing engineer' = 7,
                  'support engineer 1st line' = 8,
                  'support engineer 2nd line' = 9),
    department Enum('development' = 10, 'qa' = 20, 'tech support' = 30),
    location String,
    email String)
ENGINE = MergeTree
ORDER BY id;
    INSERT INTO employees VALUES
    (1, 'James Wilson', '2020-11-23', 1, 10, 'New York', 'jwilson@example.com'),
    (2, 'Mary Burton', '2023-05-06', 1, 10, 'Chicago', 'mburton@example.com'),
    (3, 'Patricia Robinson', '2021-10-11', 2, 10, 'Los Angeles', 'probinson@example.com'),
    (4, 'Robert Gray', '2019-07-03', 2, 10, 'Seattle', 'rgray@example.com'),
    (5, 'Elizabeth Tucker', '2024-02-03', 4, 10, 'Chicago', 'etucker@example.com'),
    (6, 'Joseph Lewis', '2023-04-05', 4, 10, 'New York', 'jlewis@example.com'),
    (7, 'Paul Brown', '2024-02-03', 3, 10, 'Chicago', 'pbrown@example.com'),
    (8, 'Andrew Clarke', '2024-11-23', 3, 10, 'Los Angeles', 'aclarke@example.com')
    (9, 'William Ferguson', '2023-11-12', 5, 20, 'Chicago', 'wferguson@example.com'),
    (10, 'Linda Black', '2021-07-20', 6, 20, 'New York', 'lblack@example.com'),
    (11, 'David Green', '2019-10-10', 7, 20, 'Chicago', 'dgreen@example.com'),
    (12, 'Daniel Gray', '2020-08-03', 7, 20, 'New York', 'dgray@example.com'),
    (13, 'Mark Armstrong', '2020-04-05', 8, 30, 'New York', 'marmstrong@example.com'),
    (14, 'Donald Carter', '2021-07-07', 8, 30, 'Seattle', 'dcarter@example.com'),
    (15, 'Elizabeth Collins', '2022-01-22', 9, 30, 'Chicago', 'ecollins@example.com');
        ┌─id─┬─full_name─────────┬──hire_date─┬─position──────────────────┬─department───┬─location────┬─email──────────────────┐
 1. │  1 │ James Wilson      │ 2020-11-23 │ c developer               │ development  │ New York    │ jwilson@example.com    │
 2. │  2 │ Mary Burton       │ 2023-05-06 │ c developer               │ development  │ Chicago     │ mburton@example.com    │
 3. │  3 │ Patricia Robinson │ 2021-10-11 │ python developer          │ development  │ Los Angeles │ probinson@example.com  │
 4. │  4 │ Robert Gray       │ 2019-07-03 │ python developer          │ development  │ Seattle     │ rgray@example.com      │
 5. │  5 │ Elizabeth Tucker  │ 2024-02-03 │ frontend developer        │ development  │ Chicago     │ etucker@example.com    │
 6. │  6 │ Joseph Lewis      │ 2023-04-05 │ frontend developer        │ development  │ New York    │ jlewis@example.com     │
 7. │  7 │ Paul Brown        │ 2024-02-03 │ java developer            │ development  │ Chicago     │ pbrown@example.com     │
 8. │  8 │ Andrew Clarke     │ 2024-11-23 │ java developer            │ development  │ Los Angeles │ aclarke@example.com    │
 9. │  9 │ William Ferguson  │ 2023-11-12 │ test automation engineer  │ qa           │ Chicago     │ wferguson@example.com  │
10. │ 10 │ Linda Black       │ 2021-07-20 │ manual testing engineer   │ qa           │ New York    │ lblack@example.com     │
11. │ 11 │ David Green       │ 2019-10-10 │ load testing engineer     │ qa           │ Chicago     │ dgreen@example.com     │
12. │ 12 │ Daniel Gray       │ 2020-08-03 │ load testing engineer     │ qa           │ New York    │ dgray@example.com      │
13. │ 13 │ Mark Armstrong    │ 2020-04-05 │ support engineer 1st line │ tech support │ New York    │ marmstrong@example.com │
14. │ 14 │ Donald Carter     │ 2021-07-07 │ support engineer 1st line │ tech support │ Seattle     │ dcarter@example.com    │
15. │ 15 │ Elizabeth Collins │ 2022-01-22 │ support engineer 2nd line │ tech support │ Chicago     │ ecollins@example.com   │
    └────┴───────────────────┴────────────┴───────────────────────────┴──────────────┴─────────────┴────────────────────────┘

  • employee_bonus_oct_24

    CREATE TABLE employee_bonus_oct_24 (employee_id UInt16, bonus UInt32) ENGINE = MergeTree ORDER BY employee_id;
    INSERT INTO employee_bonus_oct_24
VALUES (1, 700), (2, 450), (3, 500), (4, 950), (5, 370), (6, 600),(7, 1000), (8, 950),
       (9, 770), (10, 350), (11, 500), (12, 670), (13, 600), (14, 600), (15, 480);
        ┌─employee_id─┬─bonus─┐
 1. │           1 │   700 │
 2. │           2 │   450 │
 3. │           3 │   500 │
 4. │           4 │   950 │
 5. │           5 │   370 │
 6. │           6 │   600 │
 7. │           7 │  1000 │
 8. │           8 │   950 │
 9. │           9 │   770 │
10. │          10 │   350 │
11. │          11 │   500 │
12. │          12 │   670 │
13. │          13 │   600 │
14. │          14 │   600 │
15. │          15 │   480 │
    └─────────────┴───────┘

  • employee_bonus_oct_24_full

    CREATE TABLE employee_bonus_oct_24_full (
    employee_id UInt16,
    full_name String,
    position String,
    department String,
    bonus UInt32)
ENGINE = MergeTree
ORDER BY employee_id;
    INSERT INTO employee_bonus_oct_24_full SELECT
    employee_bonus_oct_24.employee_id,
    employees.full_name,
    employees.position,
    employees.department,
    employee_bonus_oct_24.bonus
FROM employee_bonus_oct_24
INNER JOIN employees ON employee_bonus_oct_24.employee_id = employees.id;
        ┌─employee_id─┬─full_name─────────┬─position──────────────────┬─department───┬─bonus─┐
 1. │           1 │ James Wilson      │ c developer               │ development  │   700 │
 2. │           2 │ Mary Burton       │ c developer               │ development  │   450 │
 3. │           3 │ Patricia Robinson │ python developer          │ development  │   500 │
 4. │           4 │ Robert Gray       │ python developer          │ development  │   950 │
 5. │           5 │ Elizabeth Tucker  │ frontend developer        │ development  │   370 │
 6. │           6 │ Joseph Lewis      │ frontend developer        │ development  │   600 │
 7. │           7 │ Paul Brown        │ java developer            │ development  │  1000 │
 8. │           8 │ Andrew Clarke     │ java developer            │ development  │   950 │
 9. │           9 │ William Ferguson  │ test automation engineer  │ qa           │   770 │
10. │          10 │ Linda Black       │ manual testing engineer   │ qa           │   350 │
11. │          11 │ David Green       │ load testing engineer     │ qa           │   500 │
12. │          12 │ Daniel Gray       │ load testing engineer     │ qa           │   670 │
13. │          13 │ Mark Armstrong    │ support engineer 1st line │ tech support │   600 │
14. │          14 │ Donald Carter     │ support engineer 1st line │ tech support │   600 │
15. │          15 │ Elizabeth Collins │ support engineer 2nd line │ tech support │   480 │
    └─────────────┴───────────────────┴───────────────────────────┴──────────────┴───────┘

Подзапросы в FROM

В качестве источника данных для чтения запросом SELECT в выражении FROM можно указывать не только имя таблицы или табличную функцию, но и подзапрос:

SELECT <expression> FROM (<subquery>) [AS <alias>]

В отличие от стандартного SQL, после подзапроса не обязательно указывать синоним (AS <alias>). Это можно сделать для совместимости, но указанное имя нигде не будет использоваться.

В следующем запросе подзапрос в выражении FROM возвращает премии сотрудников отдела development за октябрь и оценку премии каждого сотрудника относительно размера максимальной премии в компании:

SELECT employee, bonus, relative_to_max
FROM
(
    SELECT
        full_name AS employee,
        bonus,
        round((bonus/(SELECT max(bonus) FROM employee_bonus_oct_24_full)), 2) AS relative_to_max
    FROM employee_bonus_oct_24_full
    WHERE department = 'development'
)
WHERE relative_to_max < 0.5;
   ┌─employee─────────┬─bonus─┬─relative_to_max─┐
1. │ Mary Burton      │   450 │            0.45 │
2. │ Elizabeth Tucker │   370 │            0.37 │
   └──────────────────┴───────┴─────────────────┘

Подзапросы в JOIN

В запросе SELECT с оператором JOIN не оптимизируется порядок выполнения соединения по отношению к другим стадиям запроса. Сначала выполняется соединение (поиск в правой таблице), а затем — фильтрация WHERE и агрегация. Чтобы явно указать порядок вычислений и повысить производительность запроса, рекомендуется применять фильтр как можно раньше — для этого можно соединять подзапросы.

Например, в следующем запросе сначала вычисляются подзапросы в выражениях FROM (количество сотрудников в каждом отделе) и LEFT ANY JOIN (количество сотрудников в каждом отделе, получивших премию меньше 500) с группировкой и фильтрацией, а затем результаты этих подзапросов соединяются:

SELECT department, number_of_employees, bonus_less_500
FROM
(
    SELECT department, count() AS number_of_employees
    FROM employees
    GROUP BY department
)
LEFT ANY JOIN
(
    SELECT department, count() AS bonus_less_500
    FROM employee_bonus_oct_24_full
    WHERE bonus < 500
    GROUP BY department
) USING (department)
SETTINGS joined_subquery_requires_alias = 0;
   ┌─department───┬─number_of_employees─┬─bonus_less_500─┐
1. │ development  │                   8 │              2 │
2. │ qa           │                   4 │              1 │
3. │ tech support │                   3 │              1 │
   └──────────────┴─────────────────────┴────────────────┘

Подзапросы в WHERE

Подзапросы часто применяются в выражении WHERE запросов SELECT для фильтрации данных. Как упоминалось выше, для определения условий фильтрации данных подзапросы можно использовать с различными операторами сравнения:

  • подзапросы, возвращающие скалярные значения — до или после операторов сравнения = (==), != (<>), >, >=, <, <=. Например, следующий запрос находит всех сотрудников, получивших в октябре премию выше средней:

    SELECT full_name AS employee, position, department
FROM employee_bonus_oct_24_full
WHERE bonus > (SELECT avg(bonus) FROM employee_bonus_oct_24_full);
       ┌─employee─────────┬─position─────────────────┬─department──┐
1. │ James Wilson     │ c developer              │ development │
2. │ Robert Gray      │ python developer         │ development │
3. │ Paul Brown       │ java developer           │ development │
4. │ Andrew Clarke    │ java developer           │ development │
5. │ William Ferguson │ test automation engineer │ qa          │
6. │ Daniel Gray      │ load testing engineer    │ qa          │
   └──────────────────┴──────────────────────────┴─────────────┘

  • подзапросы, возвращающие наборы строк данных — с операторами IN, NOT IN, ANY, ALL, EXISTS, которые позволяют проверять наличие конкретной строки в наборе строк. Примеры использования подзапросов с этими операторами приведены в разделах ниже.

IN

Синтаксис оператора IN с использованием подзапроса:

<IN_expression> IN (<IN_subquery>)

Оператор IN вычисляет выражение IN_expression, сравнивает его со значениями во всех строках результата подзапроса IN_subquery и возвращает:

  • 1 — если в результатах подзапроса IN_subquery найдена строка, равная IN_expression;

  • 0 — в следующих случаях:

    • в результатах подзапроса IN_subquery не найдено ни одной строки, равной IN_expression;

    • IN_subquery не возвращает ни одной строки;

    • в правой или левой части оператора IN присутствует NULL при условии, что параметр transform_null_in установлен в 0 (по умолчанию).

В следующем запросе используется оператор IN, чтобы вывести информацию о выплаченных в октябре премиях только для сотрудников, проживающих в Нью-Йорке:

SELECT full_name AS employee, bonus FROM employee_bonus_oct_24_full
WHERE employee_id IN (SELECT id FROM employees WHERE location='New York');
   ┌─employee───────┬─bonus─┐
1. │ James Wilson   │   700 │
2. │ Joseph Lewis   │   600 │
3. │ Linda Black    │   350 │
4. │ Daniel Gray    │   670 │
5. │ Mark Armstrong │   600 │
   └────────────────┴───────┘
ПРИМЕЧАНИЕ

  • В подзапросе может быть указано более одного столбца для фильтрации кортежей (типы столбцов слева и справа от оператора IN должны совпадать):

    (<column1>, <column2>) IN (SELECT <IN_subquery_result_column1>, <IN_subquery_result_column2> FROM ...)

  • Оператор IN с подзапросом можно использовать в любой части родительского запроса, в том числе в агрегатной функции или лямбда-функции.

  • При распределенной обработке запроса вместо IN с подзапросом часто более оптимально использовать оператор GLOBAL IN. Более детальную информацию можно посмотреть в разделе Distributed Subqueries документации ClickHouse.

NOT IN

Синтаксис оператора NOT IN с подзапросом:

<NOT_IN_expression> NOT IN (<NOT_IN_subquery>)

Выражение NOT_IN_expression вычисляется и сравнивается с каждой строкой результата подзапроса NOT_IN_subquery. Возвращаемое значение:

  • 1 — если в результатах NOT_IN_expression и NOT_IN_subquery не найдено совпадений, или подзапрос не возвращает ни одной строки;

  • 0 — если в результатах подзапроса NOT_IN_subquery есть хоть одна строка, равная NOT_IN_expression.

Например, следующий запрос выводит данные о премиях сотрудников, проживающих не в Нью-Йорке:

SELECT full_name AS employee, bonus FROM employee_bonus_oct_24_full
WHERE employee_id NOT IN (SELECT id FROM employees WHERE location='New York');
    ┌─employee──────────┬─bonus─┐
 1. │ Mary Burton       │   450 │
 2. │ Patricia Robinson │   500 │
 3. │ Robert Gray       │   950 │
 4. │ Elizabeth Tucker  │   370 │
 5. │ Paul Brown        │  1000 │
 6. │ Andrew Clarke     │   950 │
 7. │ William Ferguson  │   770 │
 8. │ David Green       │   500 │
 9. │ Donald Carter     │   600 │
10. │ Elizabeth Collins │   480 │
    └───────────────────┴───────┘

ANY

Синтаксис оператора ANY с подзапросом:

<ANY_expression> <operator> ANY (<ANY_subquery>)

где operator может быть =, <>, <, <=, >, >=.

Оператор ANY выполняет сравнение результатов выражения ANY_expression и подзапроса ANY_subquery с помощью оператора operator и возвращает 1, если хотя бы один элемент из результата подзапроса удовлетворяет указанному условию.

Например, следующий запрос находит сотрудников не из отдела development, премия которых в октябре больше чем хотя бы у одного сотрудника отдела development в этом же месяце:

SELECT full_name AS employee, bonus, department FROM employee_bonus_oct_24_full
WHERE bonus > ANY (SELECT bonus FROM employee_bonus_oct_24_full WHERE department='development') AND department!='development';
   ┌─employee──────────┬─bonus─┬─department───┐
1. │ William Ferguson  │   770 │ qa           │
2. │ David Green       │   500 │ qa           │
3. │ Daniel Gray       │   670 │ qa           │
4. │ Mark Armstrong    │   600 │ tech support │
5. │ Donald Carter     │   600 │ tech support │
6. │ Elizabeth Collins │   480 │ tech support │
   └───────────────────┴───────┴──────────────┘

Сравнение = ANY эквивалентно оператору IN. Если приведенный выше пример с IN переписать следующим образом, результат запроса будет тем же:

SELECT full_name AS employee, bonus FROM employee_bonus_oct_24_full
WHERE employee_id = ANY (SELECT id FROM employees WHERE location='New York');
   ┌─employee───────┬─bonus─┐
1. │ James Wilson   │   700 │
2. │ Joseph Lewis   │   600 │
3. │ Linda Black    │   350 │
4. │ Daniel Gray    │   670 │
5. │ Mark Armstrong │   600 │
   └────────────────┴───────┘

ALL

Синтаксис оператора ALL с подзапросом:

<ALL_expression> <operator> ALL (<ALL_subquery>)

где operator может быть =, <>, <, <=, >, >=.

Оператор ALL сравнивает значение выражения ALL_expression с каждым значением в наборе, полученным подзапросом ALL_subquery, и возвращает 1, если условие сравнения верно для всех значений в наборе.

Например, следующий запрос возвращает список сотрудников, размер премий которых меньше чем у любого сотрудника отдела tech support:

SELECT full_name AS employee, bonus, department FROM employee_bonus_oct_24_full
WHERE bonus < ALL (SELECT bonus FROM employee_bonus_oct_24_full WHERE department='tech support');
   ┌─employee─────────┬─bonus─┬─department──┐
1. │ Mary Burton      │   450 │ development │
2. │ Elizabeth Tucker │   370 │ development │
3. │ Linda Black      │   350 │ qa          │
   └──────────────────┴───────┴─────────────┘

Оператор != ALL эквивалентен NOT IN. Приведенный выше пример с NOT IN можно переписать следующим образом:

SELECT full_name AS employee, bonus FROM employee_bonus_oct_24_full
WHERE employee_id != ALL (SELECT id FROM employees WHERE location='New York');

Результат будет такой же:

    ┌─employee──────────┬─bonus─┐
 1. │ Mary Burton       │   450 │
 2. │ Patricia Robinson │   500 │
 3. │ Robert Gray       │   950 │
 4. │ Elizabeth Tucker  │   370 │
 5. │ Paul Brown        │  1000 │
 6. │ Andrew Clarke     │   950 │
 7. │ William Ferguson  │   770 │
 8. │ David Green       │   500 │
 9. │ Donald Carter     │   600 │
10. │ Elizabeth Collins │   480 │
    └───────────────────┴───────┘

EXISTS

Синтаксис оператора EXISTS:

EXISTS (<subquery>)

Оператор EXISTS проверяет, сколько строк содержит результат выполнения подзапроса. Если результат пустой, то оператор возвращает 0. В остальных случаях оператор возвращает 1.

ВНИМАНИЕ
Ссылки на таблицы или столбцы родительского запроса не поддерживаются в подзапросе.

Например, следующий запрос выведет данные таблицы employees, только если в ней есть хотя бы один сотрудник, проживающий в Чикаго:

SELECT full_name AS employee, location AS city
FROM employees
WHERE EXISTS (SELECT * FROM employees WHERE location='Chicago');
    ┌─employee──────────┬─city────────┐
 1. │ James Wilson      │ New York    │
 2. │ Mary Burton       │ Chicago     │
 3. │ Patricia Robinson │ Los Angeles │
 4. │ Robert Gray       │ Seattle     │
 5. │ Elizabeth Tucker  │ Chicago     │
 6. │ Joseph Lewis      │ New York    │
 7. │ Paul Brown        │ Chicago     │
 8. │ Andrew Clarke     │ Los Angeles │
 9. │ William Ferguson  │ Chicago     │
10. │ Linda Black       │ New York    │
11. │ David Green       │ Chicago     │
12. │ Daniel Gray       │ New York    │
13. │ Mark Armstrong    │ New York    │
14. │ Donald Carter     │ Seattle     │
15. │ Elizabeth Collins │ Chicago     │
    └───────────────────┴─────────────┘
Нашли ошибку? Выделите текст и нажмите Ctrl+Enter чтобы сообщить о ней