Комбинирование запросов (UNION, INTERSECT, EXCEPT)

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

Для объединения результатов двух и более запросов можно использовать операторы множеств UNION, INTERSECT и EXCEPT.

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

 
Чтобы воспроизвести приведенные в данной статье примеры и посмотреть, как в ClickHouse работают различные операторы объединения множеств, создайте предварительно таблицы:

  • products_2023 — каталог продуктов за 2023 год:

    CREATE TABLE products_2023
(
    id UInt32,
    name String,
    category String,
    price Float32
) ENGINE = MergeTree()
ORDER BY id;
    INSERT INTO products_2023 VALUES
(1, 'Office Chair', 'Furniture', 170),
(2, 'Backpack', 'Accessories', 60),
(3, 'Monitor', 'Electronics', 200),
(4, 'Tablet', 'Electronics', 750),
(5, 'Water Bottle', 'Accessories', 10),
(6, 'Office Desk', 'Furniture', 250);
       ┌─id─┬─name─────────┬─category────┬─price─┐
1. │  1 │ Office Chair │ Furniture   │   170 │
2. │  2 │ Backpack     │ Accessories │    60 │
3. │  3 │ Monitor      │ Electronics │   200 │
4. │  4 │ Tablet       │ Electronics │   750 │
5. │  5 │ Water Bottle │ Accessories │    10 │
6. │  6 │ Office Desk  │ Furniture   │   250 │
   └────┴──────────────┴─────────────┴───────┘

  • products_2024 — каталог продуктов за 2024 год:

    CREATE TABLE products_2024
(
    id UInt32,
    name String,
    category String,
    price Float32
) ENGINE = MergeTree()
ORDER BY id;
    INSERT INTO products_2024 VALUES
(1, 'Office Chair', 'Furniture', 175),
(2, 'Backpack', 'Accessories', 60),
(3, 'Monitor', 'Electronics', 220),
(6, 'Office Desk', 'Furniture', 250),
(7, 'Laptop', 'Electronics', 1000),
(8, 'Smartphone', 'Electronics', 700),
(9, 'Cap', 'Accessories', 6),
(10, 'Coffee Table', 'Furniture', 180);
       ┌─id─┬─name─────────┬─category────┬─price─┐
1. │  1 │ Office Chair │ Furniture   │   175 │
2. │  2 │ Backpack     │ Accessories │    60 │
3. │  3 │ Monitor      │ Electronics │   220 │
4. │  6 │ Office Desk  │ Furniture   │   250 │
5. │  7 │ Laptop       │ Electronics │  1000 │
6. │  8 │ Smartphone   │ Electronics │   700 │
7. │  9 │ Cap          │ Accessories │     6 │
8. │ 10 │ Coffee Table │ Furniture   │   180 │
   └────┴──────────────┴─────────────┴───────┘

  • sales_2024_may — данные о продажах в мае 2024 года:

    CREATE TABLE sales_2024_may (
    sale_id UInt32,
    product_name String,
    sale_date Date DEFAULT toDate(now())
)
ENGINE = MergeTree()
ORDER BY sale_id;
    INSERT INTO sales_2024_may VALUES
(1, 'Smartphone', '2024-05-06'),
(2, 'Smartphone', '2024-05-07'),
(3, 'Monitor', '2024-05-07'),
(4, 'Office Desk', '2024-05-08'),
(5, 'Monitor', '2024-05-09'),
(6, 'Office Chair', '2024-05-09'),
(7, 'Backpack', '2024-05-10'),
(8, 'Backpack', '2024-05-11'),
(9, 'Cap', '2024-05-11'),
(10, 'Cap', '2024-05-12');
        ┌─sale_id─┬─product_name─┬──sale_date─┐
 1. │       1 │ Smartphone   │ 2024-05-06 │
 2. │       2 │ Smartphone   │ 2024-05-07 │
 3. │       3 │ Monitor      │ 2024-05-07 │
 4. │       4 │ Office Desk  │ 2024-05-08 │
 5. │       5 │ Monitor      │ 2024-05-09 │
 6. │       6 │ Office Chair │ 2024-05-09 │
 7. │       7 │ Backpack     │ 2024-05-10 │
 8. │       8 │ Backpack     │ 2024-05-11 │
 9. │       9 │ Cap          │ 2024-05-11 │
10. │      10 │ Cap          │ 2024-05-12 │
    └─────────┴──────────────┴────────────┘

UNION

Выражение UNION добавляет к результату первого запроса результат второго запроса. Может использоваться в двух режимах:

  • UNION ALL — в результат объединения включаются все строки из результатов обоих запросов, в том числе повторяющиеся;

  • UNION DISTINCT — из результата объединения исключаются повторяющиеся строки.

Если ключевое слово ALL или DISTINCT явно не указано, поведение UNION определяется настройкой union_default_mode, значением которой может быть ALL, DISTINCT или пустая строка (в этом случае при использовании UNION генерируется исключение).

Синтаксис в общем виде:

SELECT <column1> [, <column2> ]
FROM <table1>
[WHERE <condition>]

UNION [ALL | DISTINCT]

SELECT <column1> [, <column2> ]
FROM <table2>
[WHERE <condition>]

Результирующие столбцы сопоставляются по их индексу (порядку внутри SELECT). Если имена столбцов не совпадают, то имена для конечного результата берутся из первого запроса.

При объединении выполняется приведение типов. Например, если два запроса имеют одно и то же поле с совместимыми типами non-Nullable и Nullable, результат объединения будет иметь поле типа Nullable.

Запросы, которые являются частью UNION, могут быть заключены в круглые скобки. ORDER BY и LIMIT применяются к отдельным запросам, а не к конечному результату. Если нужно применить преобразование к конечному результату, поместите все запросы с UNION в подзапрос в выражении FROM.

Примеры

UNION ALL

Следующий запрос объединяет каталоги товаров за 2023 и 2024 годы:

SELECT id, name FROM products_2023
UNION ALL
SELECT id, name FROM products_2024;
   ┌─id─┬─name─────────┐
1. │  1 │ Office Chair │
2. │  2 │ Backpack     │
3. │  3 │ Monitor      │
4. │  4 │ Tablet       │
5. │  5 │ Water Bottle │
6. │  6 │ Office Desk  │
   └────┴──────────────┘
    ┌─id─┬─name─────────┐
 7. │  1 │ Office Chair │
 8. │  2 │ Backpack     │
 9. │  3 │ Monitor      │
10. │  6 │ Office Desk  │
11. │  7 │ Laptop       │
12. │  8 │ Smartphone   │
13. │  9 │ Cap          │
14. │ 10 │ Coffee Table │
    └────┴──────────────┘

UNION DISTINCT

Следующий запрос объединяет каталоги товаров за 2023 и 2024 годы, исключая дублирование:

SELECT id, name FROM products_2023
UNION DISTINCT
SELECT id, name FROM products_2024;
   ┌─id─┬─name─────────┐
1. │  1 │ Office Chair │
2. │  2 │ Backpack     │
3. │  3 │ Monitor      │
4. │  4 │ Tablet       │
5. │  5 │ Water Bottle │
6. │  6 │ Office Desk  │
   └────┴──────────────┘
    ┌─id─┬─name─────────┐
 7. │  7 │ Laptop       │
 8. │  8 │ Smartphone   │
 9. │  9 │ Cap          │
10. │ 10 │ Coffee Table │
    └────┴──────────────┘

INTERSECT

Выражение INTERSECT возвращает все строки, которые есть как в результате первого запроса, так и в результате второго запроса. Запросы должны совпадать по количеству столбцов, их порядку и типам данных. Результат INTERSECT может содержать дубликаты строк. Чтобы исключить дубликаты, используйте INTERSECT DISTINCT.

Если используется несколько выражений INTERSECT и скобки не указаны, вычисление пересечений выполняется слева направо. INTERSECT имеет более высокий приоритет выполнения, чем UNION и EXCEPT.

Синтаксис в общем виде:

SELECT <column1> [, <column2> ]
FROM <table1>
[WHERE <condition>]

INTERSECT [DISTINCT]

SELECT <column1> [, <column2> ]
FROM <table2>
[WHERE <condition>]

Примеры

Следующий запрос возвращает названия продуктов, проданных в мае 2024 года, которые входят в категорию Electronics по данным таблицы products_2024:

SELECT product_name FROM sales_2024_may
INTERSECT
SELECT name FROM products_2024 WHERE category='Electronics';

В результате видно, что было по две продажи продуктов Smartphone и Monitor:

   ┌─product_name─┐
1. │ Smartphone   │
2. │ Smartphone   │
3. │ Monitor      │
4. │ Monitor      │
   └──────────────┘

Ключевое слово DISTINCT исключает дубликаты строк из результата предыдущего запроса:

SELECT product_name FROM sales_2024_may
INTERSECT DISTINCT
SELECT name FROM products_2024 WHERE category='Electronics';
   ┌─product_name─┐
1. │ Smartphone   │
2. │ Monitor      │
   └──────────────┘

EXCEPT

Выражение EXCEPT возвращает результат первого запроса, исключая строки, которые также входят в результат второго запроса. Запросы должны совпадать по количеству столбцов, их порядку и типам данных. Результат EXCEPT может содержать дубликаты строк. Чтобы исключить дубликаты, используйте EXCEPT DISTINCT.

Если используется несколько выражений EXCEPT и скобки не указаны, EXCEPT выполняется по порядку слева направо. У EXCEPT такой же приоритет выполнения, как у UNION — более низкий, чем у INTERSECT.

Синтаксис в общем виде:

SELECT <column1> [, <column2> ]
FROM <table1>
[WHERE <condition>]

EXCEPT [DISTINCT]

SELECT <column1> [, <column2> ]
FROM <table2>
[WHERE <condition>]

Примеры

EXCEPT

Следующий запрос возвращает названия проданных в мае 2024 года продуктов, которые не входят в категорию Electronics:

SELECT product_name FROM sales_2024_may
EXCEPT
SELECT name FROM products_2024 WHERE category='Electronics';
   ┌─product_name─┐
1. │ Office Desk  │
2. │ Office Chair │
3. │ Backpack     │
4. │ Backpack     │
5. │ Cap          │
6. │ Cap          │
   └──────────────┘

Используйте ключевое слово DISTINCT, чтобы убрать дублирование из результата предыдущего запроса:

SELECT product_name FROM sales_2024_may
EXCEPT DISTINCT
SELECT name FROM products_2024 WHERE category='Electronics';
   ┌─product_name─┐
1. │ Office Desk  │
2. │ Office Chair │
3. │ Backpack     │
4. │ Cap          │
   └──────────────┘
Нашли ошибку? Выделите текст и нажмите Ctrl+Enter чтобы сообщить о ней