SQL объединение UNION - мощный инструмент для работы с данными

SQL объединение UNION позволяет эффективно комбинировать данные из разных источников. Это мощный и гибкий инструмент для специалистов по данным. Давайте подробно разберемся как использовать все возможности оператора UNION в SQL для решения аналитических задач.

Основы SQL объединения UNION

UNION - это оператор в SQL, который позволяет объединять данные из двух или более запросов в один результирующий набор. Он как бы "склеивает" строки из разных источников по определенным правилам.

Основной синтаксис UNION выглядит так:

 SELECT columns FROM table1 UNION [ALL] SELECT columns FROM table2; 

Здесь важно, чтобы количество и порядок столбцов в обоих запросах совпадали. Их типы данных также должны быть совместимы.

UNION. Указывает на то, что несколько результирующих наборов следует объединить и возвратить в виде единого результирующего набора.ALL.Объединяет в результирующий набор все строки, в том числе повторяющиеся. Если обратное не указано, дубликаты строк удаляются.

ALL. Объединяет в результирующий набор все строки, в том числе повторяющиеся. Если обратное не указано, дубликаты строк удаляются.

UNION отличается от JOIN тем, что не объединяет таблицы по столбцам, а просто конкатенирует строки. JOIN более точный инструмент для связывания данных.

Основные правила использования UNION:

• Число и порядок столбцов в запросах должно совпадать
• Типы данных в столбцах должны быть совместимы
• По умолчанию удаляются дублирующиеся строки
• Порядок строк не гарантируется, если не использовать ORDER BY

Например, объединим данные о ПК и ноутбуках:

 SELECT ProductModelID, Name FROM Production.ProductModel WHERE ProductModelID IN (1, 2) UNION SELECT ProductModelID, Name FROM Production.ProductModel WHERE ProductModelID IN (3, 4); 

Здесь мы получим уникальные строки из двух запросов к одной таблице.

Чтобы сохранить дубликаты, используется UNION ALL:

 SELECT column1 FROM table1 UNION ALL SELECT column2 FROM table2; 

Порядок строк при объединении не гарантируется. Чтобы отсортировать результат, ORDER BY пишется в конце:

 SELECT ... FROM tab1 UNION SELECT ... FROM tab2 ORDER BY column; 

Объединение данных из разных таблиц

Рассмотрим более реалистичные примеры объединения данных из разных таблиц с помощью UNION.

Допустим, у нас есть две таблицы с данными о продуктах - одна с ПК, другая с ноутбуками:

 CREATE TABLE pc ( model VARCHAR(10), price DECIMAL(10,2) ); CREATE TABLE laptops ( model VARCHAR(10), price DECIMAL(10,2) ); 

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

 SELECT model, price FROM pc UNION SELECT model, price FROM laptops; 

А вот как объединить данные сразу из трех таблиц:

 SELECT column1 FROM table1 UNION SELECT column2 FROM table2 UNION SELECT column3 FROM table3; 

UNION ALL позволяет сохранить все дублирующиеся строки:

 SELECT model, price FROM pc UNION ALL SELECT model, price FROM laptops; 

Скобки в UNION нужны для группировки и изменения порядка обработки:

 SELECT ... UNION (SELECT ... UNION SELECT ...); 

А вот как отсортировать результат UNION по столбцу:

 SELECT ... FROM t1 UNION SELECT ... FROM t2 ORDER BY column; 

Объединение запросов к одной таблице

UNION часто применяется для объединения данных внутри одной таблицы. Например, есть таблица сотрудников:

 CREATE TABLE employees ( id INT, name VARCHAR(50), salary INT ); 

Тогда запрос с UNION позволяет разделить данные по департаментам:

 SELECT id, name, salary FROM employees WHERE department = 'IT' UNION SELECT id, name, salary FROM employees WHERE department = 'Sales'; 

А вот как эмулировать FULL OUTER JOIN через UNION:

 SELECT column_name(s) FROM table1 UNION SELECT column_name(s) FROM table2; 

UNION удобно использовать с группировкой данных в подзапросах:

 SELECT * FROM (SELECT ... UNION SELECT ...) WHERE condition GROUP BY column; 

Предикат EXISTS также работает в UNION:

 SELECT col1 FROM t1 WHERE EXISTS (SELECT col2 FROM t2 UNION SELECT col3 FROM t3) 

Для оптимизации производительности запросов с UNION следует создавать индексы на соответствующих столбцах.

Обработка столбцов и порядка в UNION

При работе с UNION в SQL часто нужно обрабатывать столбцы и менять порядок результатов.

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

 SELECT col1 AS new_name FROM t1 UNION SELECT col2 FROM t2; 

Можно добавить вычисляемый столбец:

 SELECT *, quantity * price AS total FROM t1 UNION SELECT * FROM t2; 

Чтобы поменять порядок столбцов, меняем их порядок в списке SELECT:

 SELECT col2, col1 FROM t1 UNION SELECT col3, col4 FROM t2; 

При необходимости можно выбрать нужные столбцы из таблиц:

 SELECT col1, col2 FROM t1 UNION SELECT col3 FROM t2; 

Если типы данных в столбцах отличаются, то определяется общий минимальный тип по правилам неявного приведения типов.

Расширенное использование UNION

Кроме базовых примеров, UNION можно использовать более гибко и мощно в SQL.

Например, вложенные запросы с UNION:

 SELECT * FROM (SELECT * FROM t1 UNION SELECT * FROM t2) UNION SELECT * FROM t3; 

Можно применять агрегатные функции COUNT, SUM, AVG и т.д.:

 SELECT COUNT(*) FROM (SELECT * FROM t1 UNION SELECT * FROM t2) x; 

UNION удобно использовать в представлениях:

 CREATE VIEW myview AS SELECT * FROM t1 UNION SELECT * FROM t2; 

Или в подзапросах:

 SELECT * FROM t1 WHERE column IN (SELECT column FROM t2 UNION SELECT column FROM t3); 

Также можно объединять табличные выражения:

 SELECT * FROM (VALUES (1,2), (3,4)) AS t1(a,b) UNION (VALUES (1,3), (5,7)) AS t2(a,b); 

По производительности UNION может проигрывать JOIN, если нужно именно соединение данных.

Лучшие практики применения UNION

Чтобы оптимизировать работу с UNION в SQL, рекомендуется:

• Использовать UNION ALL вместо UNION, если дубликаты не важны
• Применять индексы на соединяемых столбцах
• Добавлять ORDER BY в конец запроса для сортировки
• Объединять запросы со схожей выборкой данных
• Избегать неподходящих преобразований типов

Распространенные ошибки при работе с UNION:

• Разное число столбцов в запросах
• Несовместимые типы данных
• Неверное расположение ORDER BY
• Дублирующиеся имена столбцов

В некоторых случаях вместо UNION лучше подходят:

• JOIN - для связи таблиц по столбцам
• CONCAT - для обычной конкатенации строк
• UNION ALL - чтобы сохранить дубликаты

При рефакторинге запросов с UNION полезно:

• Выносить запросы с UNION в представления
• Заменять хардкод именами столбцов
• Разбивать сложные запросы на простые

Особенности UNION в разных СУБД

Реализация UNION может отличаться в разных СУБД.

В Oracle UNION называется логическим объединением наборов данных. Поддерживается синтаксис:

 SELECT ... FROM tab1 UNION [ALL] SELECT ... FROM tab2; 

В PostgreSQL UNION реализован в соответствии со стандартом SQL. Есть несколько ограничений по сравнению с JOIN.

В MySQL UNION работает как в SQL, но есть отличия в типах данных и индексации:

 SELECT ... UNION [ALL | DISTINCT] SELECT ...; 

Таким образом, при использовании UNION нужно учитывать особенности конкретной СУБД.

Применение UNION к связанным подзапросам

UNION можно использовать в связанных подзапросах в SQL. Например, есть основной запрос к таблице customers:

 SELECT name, city FROM customers 

А в подзапросе объединяются данные из таблиц orders и returns:

 WHERE customer_id IN (SELECT customer_id FROM orders UNION SELECT customer_id FROM returns) 

Здесь в результат попадут клиенты, которые есть и в заказах, и в возвратах.

Использование UNION в СУБД Access

В MS Access оператор UNION тоже поддерживается, но есть некоторые особенности. Например, запрос:

 SELECT City FROM Customers UNION SELECT City FROM Suppliers; 

Вернет объединенный список городов из двух таблиц. Однако в Access нужно явно приводить типы данных:

 SELECT CStr(City) FROM Customers UNION SELECT CStr(City) FROM Suppliers; 

Описание оператора UNION ALL

UNION ALL - это вариант оператора UNION, который возвращает все строки из обоих наборов результатов, включая дубликаты. Например:

 SELECT column1 FROM table1 UNION ALL SELECT column2 FROM table2; 

В отличие от просто UNION, UNION ALL не удаляет повторяющиеся строки. Это позволяет сохранить дубликаты, если в этом есть необходимость.

По скорости UNION ALL выигрывает у UNION, так как не тратит ресурсы на удаление дубликатов.

Особенности NULL при использовании UNION

При объединении данных через UNION нужно учитывать значения NULL в столбцах.

NULL считается отдельным значением. Поэтому если в обоих наборах данных присутствует NULL, то это не дубликат:

 SELECT NULL UNION SELECT NULL; 

Вернет две строки со значением NULL.

Кроме того, NULL имеет наивысший приоритет при сортировке результатов UNION. Все строки NULL выводятся в начале:

 SELECT * FROM t1 UNION SELECT * FROM t2 ORDER BY col; 

Это нужно учитывать при работе с объединенными данными.