Блог

В чем разница между UNION и UNION ALL ?

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

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

Для выявления дубликатов записи должны быть сопоставимых типов, а также совместимых типов. Это будет зависеть от системы SQL. Например, система может обрезать все длинные текстовые поля, чтобы сделать короткие текстовые поля для сравнения (MS Jet), или может отказаться сравнивать двоичные поля (ORACLE)

Пример ОБЪЕДИНЕНИЯ:

 SELECT 'foo' AS bar UNION SELECT 'foo' AS bar
  

Результат:

  ----- 
| bar |
 ----- 
| foo |
 ----- 
1 row in set (0.00 sec)
  

Пример ОБЪЕДИНЕНИЯ ВСЕХ:

 SELECT 'foo' AS bar UNION ALL SELECT 'foo' AS bar
  

Результат:

  ----- 
| bar |
 ----- 
| foo |
| foo |
 ----- 
2 rows in set (0.00 sec)
  

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

• ОБЪЕДИНЕНИЕ выполняет РАЗЛИЧИЕ в результирующем наборе, устраняя любые повторяющиеся строки.

• ОБЪЕДИНЕНИЕ ВСЕХ не удаляет дубликаты, и поэтому оно быстрее, чем ОБЪЕДИНЕНИЕ.

Примечание: при использовании этой команды все выбранные столбцы должны иметь один и тот же тип данных.

Пример: если у нас есть две таблицы: 1) Employee и 2) Customer

1. Данные таблицы сотрудников:

2. Данные таблицы клиентов:

3. Пример ОБЪЕДИНЕНИЯ (он удаляет все повторяющиеся записи):

4. Пример ОБЪЕДИНЕНИЯ ВСЕХ (он просто объединяет записи, а не устраняет дубликаты, поэтому он быстрее, чем ОБЪЕДИНЕНИЕ):

UNION удаляет дубликаты, тогда UNION ALL как нет.

Для удаления дубликатов результирующий набор должен быть отсортирован, и это может повлиять на производительность ОБЪЕДИНЕНИЯ в зависимости от объема сортируемых данных и настроек различных параметров СУБД (для Oracle PGA_AGGREGATE_TARGET с WORKAREA_SIZE_POLICY=AUTO or SORT_AREA_SIZE и SOR_AREA_RETAINED_SIZE if WORKAREA_SIZE_POLICY=MANUAL ).

В принципе, сортировка выполняется быстрее, если ее можно выполнить в памяти, но применяется то же самое предостережение относительно объема данных.

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

Я бы прокомментировал первое сообщение, чтобы квалифицировать комментарий «гораздо менее эффективный», но для этого недостаточно репутации (баллов).

В ORACLE: UNION не поддерживает типы столбцов BLOB (или CLOB), UNION ALL поддерживает.

Основное различие между ОБЪЕДИНЕНИЕМ и ОБЪЕДИНЕНИЕМ ВСЕХ заключается в том, что операция объединения удаляет дублированные строки из результирующего набора, но объединение всех возвращает все строки после объединения.

из http://zengin.wordpress.com/2007/07/31/union-vs-union-all /

ОБЪЕДИНЕНИЕ
UNION Команда используется для выбора связанной информации из двух таблиц, во многом аналогично JOIN команде. Однако при использовании UNION команды все выбранные столбцы должны иметь один и тот же тип данных. С UNION помощью выбираются только отдельные значения.

ОБЪЕДИНЕНИЕ ВСЕХ
UNION ALL Команда равна UNION команде, за исключением того, что UNION ALL выбирает все значения.

Разница между Union и Union all заключается в том, что Union all не устраняет повторяющиеся строки, вместо этого он просто извлекает все строки из всех таблиц, соответствующих специфике вашего запроса, и объединяет их в таблицу.

UNION Оператор эффективно выполняет a SELECT DISTINCT для набора результатов. Если вы знаете, что все возвращаемые записи уникальны из вашего объединения, используйте UNION ALL вместо этого, это дает более быстрые результаты.

Вы можете избежать дубликатов и по-прежнему работать намного быстрее, чем UNION DISTINCT (который на самом деле такой же, как UNION), выполнив запрос следующим образом:

SELECT * FROM mytable WHERE a=X UNION ALL SELECT * FROM mytable WHERE b=Y AND a!=X

Обратите внимание на AND a!=X часть. Это намного быстрее, чем ОБЪЕДИНЕНИЕ.

Просто чтобы добавить свои два цента к обсуждению здесь: можно понимать UNION оператор как чистое, ориентированное на МНОЖЕСТВО ОБЪЕДИНЕНИЕ — например, set A= {2,4,6,8}, set B = {1,2,3,4}, ОБЪЕДИНЕНИЕ B = {1,2,3,4,6,8}

При работе с наборами вы бы не хотели, чтобы числа 2 и 4 появлялись дважды, поскольку элемент либо есть, либо его нет в наборе.

Однако в мире SQL вы можете захотеть видеть все элементы из двух наборов вместе в одном «пакете» {2,4,6,8,1,2,3,4} . И для этой цели T-SQL предлагает оператор UNION ALL .

ОБЪЕДИНЕНИЕ — приводит к отдельным записям

в то время как

ОБЪЕДИНЕНИЕ ВСЕХ — приводит ко всем записям, включая дубликаты.

Оба являются блокирующими операторами, и поэтому я лично предпочитаю использовать ОБЪЕДИНЕНИЯ вместо блокирующих операторов (ОБЪЕДИНЕНИЕ, ПЕРЕСЕЧЕНИЕ, ОБЪЕДИНЕНИЕ ВСЕХ и т. Д.) В любое время.

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

 CREATE TABLE #T1 (data VARCHAR(10))

INSERT INTO #T1
SELECT 'abc'
UNION ALL
SELECT 'bcd'
UNION ALL
SELECT 'cde'
UNION ALL
SELECT 'def'
UNION ALL
SELECT 'efg'


CREATE TABLE #T2 (data VARCHAR(10))

INSERT INTO #T2
SELECT 'abc'
UNION ALL
SELECT 'cde'
UNION ALL
SELECT 'efg'
  

Ниже приведены результаты операций ОБЪЕДИНЕНИЯ ВСЕХ и ОБЪЕДИНЕНИЯ.

Оператор ОБЪЕДИНЕНИЯ эффективно выполняет SELECT DISTINCT в наборе результатов. Если вы знаете, что все возвращаемые записи уникальны для вашего union, используйте вместо UNION ALL , это дает более быстрые результаты.

Использование ОБЪЕДИНЕНИЯ приводит к различным операциям сортировки в плане выполнения. Доказательство, подтверждающее это утверждение, показано ниже:

Не уверен, что имеет значение, какая база данных

UNION и UNION ALL должен работать на всех серверах SQL.

Вы должны избегать ненужных UNION s, они являются огромной утечкой производительности. Как правило, используйте UNION ALL , если вы не уверены, что использовать.

(Из онлайн-книги Microsoft SQL Server)

ОБЪЕДИНЕНИЕ [ВСЕ]

Указывает, что несколько наборов результатов должны быть объединены и возвращены как один набор результатов.

ВСЕ

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

UNION это займет слишком много времени, так как к результатам применяется поиск повторяющихся строк like DISTINCT .

 SELECT * FROM Table1
UNION
SELECT * FROM Table2
  

эквивалентно:

 SELECT DISTINCT * FROM (
    SELECT * FROM Table1
    UNION ALL
    SELECT * FROM Table2) DT
  

Побочным эффектом применения DISTINCT к результатам является операция сортировки результатов.

UNION ALL результаты будут показаны в произвольном порядке по результатам, но UNION результаты будут показаны в ORDER BY 1, 2, 3, ..., n (n = column number of Tables) соответствии с результатами. Вы можете увидеть этот побочный эффект, когда у вас нет повторяющейся строки.

Я добавляю пример,

ОБЪЕДИНЕНИЕ, оно сливается с distinct —> медленнее, потому что его нужно сравнивать (в Oracle SQL developer выберите query, нажмите F10, чтобы просмотреть анализ затрат).

ОБЪЕДИНЕНИЕ ВСЕХ, это слияние без distinct —> быстрее.

 SELECT to_date(sysdate, 'yyyy-mm-dd') FROM dual
UNION
SELECT to_date(sysdate, 'yyyy-mm-dd') FROM dual;
  

и

 SELECT to_date(sysdate, 'yyyy-mm-dd') FROM dual
UNION ALL
SELECT to_date(sysdate, 'yyyy-mm-dd') FROM dual;
  

UNION объединяет содержимое двух структурно совместимых таблиц в одну объединенную таблицу.

• Разница:

Разница между UNION и UNION ALL заключается в том, что UNION will опускаются повторяющиеся записи, тогда UNION ALL как будут содержать повторяющиеся записи.

Union Результирующий набор сортируется в порядке возрастания, тогда UNION ALL как результирующий набор не сортируется

UNION выполняет a DISTINCT для своего результирующего набора, чтобы исключить любые повторяющиеся строки. Тогда UNION ALL как не будет удалять дубликаты и, следовательно, быстрее, чем UNION .*

Примечание: производительность UNION ALL , как правило, будет лучше, чем UNION , поскольку UNION требует, чтобы сервер выполнял дополнительную работу по удалению любых дубликатов. Итак, в тех случаях, когда есть уверенность, что дубликатов не будет, или когда наличие дубликатов не является проблемой, рекомендуется использовать из UNION ALL соображений производительности.

Предположим, что у вас есть две таблицы Teacher amp; Student

У обоих есть 4 столбца с разными именами, например

 Teacher - ID(int), Name(varchar(50)), Address(varchar(50)), PositionID(varchar(50))
  

 Student- ID(int), Name(varchar(50)), Email(varchar(50)), PositionID(int)
  

Вы можете применить ОБЪЕДИНЕНИЕ или ОБЪЕДИНЕНИЕ ВСЕХ для тех двух таблиц, которые имеют одинаковое количество столбцов. Но у них разные имена или типы данных.

Когда вы применяете UNION операцию к 2 таблицам, она игнорирует все повторяющиеся записи (значение всех столбцов строки в таблице совпадает с значением другой таблицы). Вот так

 SELECT * FROM Student
UNION
SELECT * FROM Teacher
  

результатом будет

Когда вы применяете UNION ALL операцию к 2 таблицам, она возвращает все записи с дубликатами (если есть какая-либо разница между любым значением столбца строки в 2 таблицах). Вот так

 SELECT * FROM Student
UNION ALL
SELECT * FROM Teacher
  

Вывод

Производительность:

Очевидно, что производительность UNION ALL лучше, чем UNION, поскольку они выполняют дополнительную задачу по удалению повторяющихся значений. Вы можете проверить это по расчетному времени выполнения, нажав ctrl L в MSSQL

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

поскольку union внутренне использует «отдельное» поведение для выбора строк, следовательно, это более затратно с точки зрения времени и производительности. Нравится

 select project_id from t_project
union
select project_id from t_project_contact  
  

это дает мне 2020 записей

с другой стороны

 select project_id from t_project
union all
select project_id from t_project_contact
  

дает мне более 17402 строк

с точки зрения приоритета оба имеют одинаковый приоритет.

Если нет ORDER BY , a UNION ALL может возвращать строки по ходу выполнения, тогда как a UNION заставит вас ждать до самого конца запроса, прежде чем выдавать вам весь результирующий набор сразу. Это может иметь значение в ситуации тайм-аута — a UNION ALL как бы поддерживает соединение.

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

Еще одна вещь, которую я хотел бы добавить-

Объединение: — Результирующий набор сортируется в порядке возрастания.

Объединение всех: — Результирующий набор не отсортирован. просто добавляется вывод двух запросов.

Важно! Разница между Oracle и Mysql: Предположим, что t1 t2 не имеют повторяющихся строк между ними, но у них есть отдельные повторяющиеся строки. Пример: у t1 продажи с 2017 года, а у t2 с 2018 года

 SELECT T1.YEAR, T1.PRODUCT FROM T1

UNION ALL

SELECT T2.YEAR, T2.PRODUCT FROM T2
  

В ORACLE UNION ALL извлекает все строки из обеих таблиц. То же самое произойдет и в MySQL.

Однако:

 SELECT T1.YEAR, T1.PRODUCT FROM T1

UNION

SELECT T2.YEAR, T2.PRODUCT FROM T2
  

В ORACLE UNION извлекает все строки из обеих таблиц, потому что между t1 и t2 нет повторяющихся значений. С другой стороны, в MySQL результирующий набор будет содержать меньше строк, потому что в таблице t1, а также в таблице t2 будут повторяющиеся строки!

UNION ALL также работает и с другими типами данных. Например, при попытке объединить пространственные типы данных. Например:

 select a.SHAPE from tableA a
union
select b.SHAPE from tableB b
  

приведет к

The data type geometry cannot be used as an operand to the UNION, INTERSECT or EXCEPT operators because it is not comparable.

Однако union all не будет.