Чем отличается join от union
Оператор UNION подобно inner join или outer join позволяет соединить две таблицы. Но в отличие от inner/outer join объединения соединяют не столбцы разных таблиц, а два однотипных набора в один. Формальный синтаксис объединения:
SELECT_выражение1 UNION [ALL] SELECT_выражение2 [UNION [ALL] SELECT_выражениеN]
Например, пусть в базе данных будут две отдельные таблицы для клиентов банка (таблица Customers) и для сотрудников банка (таблица Employees):
USE usersdb; CREATE TABLE Customers ( Id INT IDENTITY PRIMARY KEY, FirstName NVARCHAR(20) NOT NULL, LastName NVARCHAR(20) NOT NULL, AccountSum MONEY ); CREATE TABLE Employees ( Id INT IDENTITY PRIMARY KEY, FirstName NVARCHAR(20) NOT NULL, LastName NVARCHAR(20) NOT NULL, ); INSERT INTO Customers VALUES ('Tom', 'Smith', 2000), ('Sam', 'Brown', 3000), ('Mark', 'Adams', 2500), ('Paul', 'Ins', 4200), ('John', 'Smith', 2800), ('Tim', 'Cook', 2800) INSERT INTO Employees VALUES ('Homer', 'Simpson'), ('Tom', 'Smith'), ('Mark', 'Adams'), ('Nick', 'Svensson')
Здесь мы можем заметить, что обе таблицы, несмотря на наличие различных данных, могут характеризоваться двумя общими атрибутами — именем (FirstName) и фамилией (LastName). Выберем сразу всех клиентов банка и его сотрудников из обеих таблиц:
SELECT FirstName, LastName FROM Customers UNION SELECT FirstName, LastName FROM Employees
В данном случае из первой таблицы выбираются два значения — имя и фамилия клиента. Из второй таблицы Employees также выбираются два значения — имя и фамилия сотрудников. То есть при объединении количество выбираемых столбцов и их тип совпадают для обеих выборок.
При этом названия столбцов объединенной выборки будут совпадать с названия столбцов первой выборки. И если мы захотим при этом еще произвести сортировку, то в выражениях ORDER BY необходимо ориентироваться именно на названия столбцов первой выборки:
SELECT FirstName + ' ' +LastName AS FullName FROM Customers UNION SELECT FirstName + ' ' + LastName AS EmployeeName FROM Employees ORDER BY FullName DESC
В данном случае каждая выборка имеет по одному столбцу, который представляет объединение имени и фамилии клиента или сотрудника. Но в случае с клиентами столбец будет называться FullName, а в случае с сотрудниками — EmployeeName. Тем не менее для сортировки применяется название столбца из первой выборки и он же будет в результирующей выборке:
Если же в одной выборке больше столбцов, чем в другой, то они не смогут быть объединены. Например, в следующем случае объединение завершится с ошибкой:
SELECT FirstName, LastName, AccountSum FROM Customers UNION SELECT FirstName, LastName FROM Employees
Также соответствующие столбцы должны соответствовать по типу. Так, следующий пример завершится с ошибкой из-за не соответствия по типу данных:
SELECT FirstName, LastName FROM Customers UNION SELECT Id, LastName FROM Employees
В данном случае первый столбец первой выборки имеет тип NVARCHAR, то есть хранит строку. Первый столбец второй выборки — Id имеет тип INT, то есть хранит число.
Если оба объединяемых набора содержат в строках идентичные значения, то при объединении повторяющиеся строки удаляются. Например, в случае с таблицами Customers и Employees сотрудники банка могут быть одновременно его клиентами и содержаться в обеих таблицах. При объединении в примерах выше всех дублирующиеся строки удалялись. Если же необходимо при объединении сохранить все, в том числе повторяющиеся строки, то для этого необходимо использовать оператор ALL :
SELECT FirstName, LastName FROM Customers UNION ALL SELECT FirstName, LastName FROM Employees
Объединять выборки можно и из одной и той же таблицы. Например, в зависимости от суммы на счете клиента нам надо начислять ему определенные проценты:
SELECT FirstName, LastName, AccountSum + AccountSum * 0.1 AS TotalSum FROM Customers WHERE AccountSum < 3000 UNION SELECT FirstName, LastName, AccountSum + AccountSum * 0.3 AS TotalSum FROM Customers WHERE AccountSum >= 3000
В данном случае если сумма меньше 3000, то начисляются проценты в размере 10% от суммы на счете. Если на счете больше 3000, то проценты увеличиваются до 30%.
Явные операции соединения стр. 3
Этот тип соединения был введен в стандарте SQL-92 , но в более поздних версиях стандарта отсутствует. В частности, его уже нет в стандарте SQL2003 (ANSI и ISO). Как и многие другие конструкции языка SQL, соединение UNION JOIN является избыточным, поскольку его можно выразить через разность полного и внутреннего соединений. Формально это можно записать следующим образом:
Ну, а если полное соединение не поддерживается (MySQL), то его можно получить объединением левого и правого внешних соединений. Тогда наша формула примет вид
Давайте в качестве примера, где мог бы пригодиться этот тип соединения, рассмотрим следующую задачу.
Найти производителей, которые выпускают принтеры, но не ПК, или выпускают ПК, но не принтеры.
Будь у нас возможность использовать UNION JOIN, мы бы решили задачу так:
Воспользуемся формулой. Полное соединение производителей ПК и производителей принтеров даст нам как тех, кто производит что-то одно, так и тех, кто производит и то, и другое.
Консоль
Выполнить
Разница между JOIN и UNION в SQL
JOIN и UNION — это предложения в SQL, используемые для объединения данных двух или более отношений. Но способ, которым они объединяют данные и формат полученного результата, отличается. Предложение JOIN объединяет атрибуты двух отношений для формирования результирующих кортежей, тогда как предложение UNION объединяет результат двух запросов. Давайте обсудим разницу между JOIN и UNION с помощью сравнительной таблицы, показанной ниже.
Сравнительная таблица
| Основа для сравнения | ПРИСОЕДИНИТЬСЯ | UNION |
|---|---|---|
| основной | JOIN объединяет атрибуты кортежей, присутствующих в двух разных отношениях, которые имеют общие поля или атрибуты. | UNION объединяет кортежи отношений, которые присутствуют в запросе. |
| Состояние | JOIN применяется, когда два вовлеченных отношения имеют хотя бы один общий атрибут. | UNION применяется, когда число столбцов, присутствующих в запросе, одинаково и соответствующие атрибуты имеют одинаковый домен. |
| Типы | ВНУТРЕННЯЯ, ПОЛНАЯ (НАРУЖНАЯ), ЛЕВАЯ РЕГИСТРАЦИЯ, ПРАВАЯ СОЕДИНЕНИЕ | СОЮЗ и СОЮЗ ВСЕХ. |
| эффект | Длина результирующих кортежей больше по сравнению с длиной кортежей вовлеченных отношений. | Количество результирующих кортежей больше по сравнению с количеством кортежей, присутствующих в каждом отношении, участвующем в запросе. |
| схема |  |
 |
Определение JOIN
Предложение JOIN в SQL объединяет кортежи из двух отношений или таблиц, что приводит к увеличению размера кортежа. Результирующий кортеж содержит атрибуты из обоих отношений. Атрибуты объединяются на основе общих атрибутов между ними. Различные типы JOIN в SQL: INNER JOIN, LEFT JOIN, RIGHT JOIN, FULL OUTER JOIN.
INNER JOIN объединяет кортежи из обеих таблиц, если между ними существует общий атрибут. LEFT JOIN приводит ко всем кортежам левой таблицы и соответствующим кортежу из правой таблицы. RIGHT JOIN приводит ко всем кортежам из правой таблицы и соответствует только кортежу из левой таблицы. FULL OUTER JOIN приводит ко всем кортежам из обеих таблиц, хотя они имеют совпадающие атрибуты или нет.
ВНУТРЕННЕЕ СОЕДИНЕНИЕ — то же самое, что СОЕДИНЕНИЕ. Вы также можете удалить ключевое слово INNER и просто использовать JOIN для выполнения INNER JOIN.
Определение СОЮЗА
UNION — это операция над множествами в SQL. UNON объединяет результат двух запросов. Результат UNION включает в себя кортежи обоих отношений, присутствующих в запросе. Условия, которые должны быть выполнены для объединения двух отношений:
- Два отношения должны иметь одинаковое количество атрибутов.
- Домены соответствующего атрибута должны быть одинаковыми.
Существует два типа UNION: UNION и UNION ALL . Результат, полученный с помощью UNION, не содержит дубликатов. С другой стороны, результат, полученный с помощью UNION ALL, сохраняет дубликат.
Ключевые различия между JOIN и UNION в SQL
- Основное различие между JOIN и UNION состоит в том, что JOIN объединяет кортежи из двух отношений, а результирующие кортежи включают атрибуты из обоих отношений. С другой стороны, UNION объединяет результат двух запросов SELECT.
- Предложение JOIN применимо только тогда, когда два участвующих отношения имеют хотя бы один общий атрибут в обоих. С другой стороны, UNION применяется, когда два отношения имеют одинаковое количество атрибутов и домены соответствующих атрибутов одинаковы.
- Существует четыре типа РЕАГИРОВАНИЯ ВНУТРЕННЕГО РЕШЕНИЯ, ЛЕВОГО СОЕДИНЕНИЯ, ПРЯМОГО СОЕДИНЕНИЯ, ПОЛНОГО НАРУЖНОГО СОЕДИНЕНИЯ. Но есть два типа UNION, UNION и UNION ALL.
- В JOIN результирующий кортеж имеет больший размер, поскольку включает атрибуты обоих отношений. С другой стороны, в UNION число кортежей увеличивается, в результате чего в них входит кортеж из обоих отношений, присутствующих в запросе.
Заключение:
Обе операции объединения данных используются в разных ситуациях. JOIN используется, когда мы хотим объединить атрибуты двух отношений, имеющих хотя бы один общий атрибут. UNION используется, когда мы хотим объединить кортежи двух отношений, которые присутствуют в запросе.
UNION в SQL
Оператор UNION используется для объединения результатов двух или более запросов SELECT в один набор результатов. Команда UNION отличается от JOIN-операторов, которые объединяют столбцы из двух таблиц. UNION создает новую таблицу, помещая все строки из двух исходных таблиц в таблицу результатов и располагая эти строки друг над другом.
Далее приведены основные правила объединения наборов результатов двух запросов SELECT с помощью UNION :
- Количество и порядок столбцов должны быть одинаковыми во всех запросах.
- Типы данных соответствующих столбцов должны быть совместимы.
Если эти критерии выполняются, то таблицы совместимы с оператором UNION .
Синтаксис
Базовый синтаксис оператора UNION представлен следующим образом:
SELECT список_столбцов FROM таблица1
UNION SELECT список_столбцов FROM таблица2;
Чтобы лучше понять, как работает оператор UNION , предположим, что в таблицах employees и departments существуют некоторые гипотетические поля first_name и last_name . Обратите внимание, что эти поля на самом деле не существуют в демонстрационных таблицах.
Таблица employees
Таблица departments
+----+------------+-----------+--------+ | id | first_name | last_name | salary | +----+------------+-----------+--------+ | 1 | Ethan | Hunt | 5000 | | 2 | Tony | Montana | 6500 | | 3 | Sarah | Connor | 8000 | | 4 | Rick | Deckard | 7200 | | 5 | Martin | Blank | 5600 | +----+------------+-----------+--------+
+----+------------+-----------+----------+ | id | first_name | last_name | city | +----+------------+-----------+----------+ | 1 | Maria | Anders | Berlin | | 2 | Fran | Wilson | Madrid | | 3 | Dominique | Perrier | Paris | | 4 | Martin | Blank | Turin | | 5 | Thomas | Hardy | Portland | +----+------------+-----------+----------+
Выполним оператор UNION , чтобы объединить результаты двух запросов.
Следующая команда вернет имена и фамилии всех клиентов и сотрудников:
SELECT first_name, last_name FROM employees
UNION
SELECT first_name, last_name FROM customers;
После выполнения приведенной выше команды вы получите такой результат:
+---------------+--------------+ | first_name | last_name | +---------------+--------------+ | Ethan | Hunt | | Tony | Montana | | Sarah | Connor | | Rick | Deckard | | Martin | Blank | | Maria | Anders | | Fran | Wilson | | Dominique | Perrier | | Thomas | Hardy | +---------------+--------------+
Оператор UNION по умолчанию удаляет дублирующиеся строки из объединенного набора результатов. Вот почему приведенный выше запрос возвращает только 9 строк. Если вы заметили, имя Martin Blank встречается и в таблице employees , и в таблице customers .
Однако если вы хотите оставить дублирующиеся строки, используйте ключевое слово ALL , как показано ниже:
SELECT first_name, last_name FROM employees
UNION ALL
SELECT first_name, last_name FROM customers;