Union и union all что быстрее
Объединение UNION и UNION ALL в SQL – описание и примеры
Пришло время поговорить об объединении данных по средствам конструкции union и union all, так как это иногда бывает очень полезно, и без использования такой конструкции бывает порой не обойтись. Примеры будем писать в СУБД MSSQL 2008, используя язык SQL.
И начать хотелось бы с того, что мы с Вами уже рассматривали много примеров написания запросов на SQL, например, оператор select языка SQL, или использование строковых функций SQL, также рассматривали программирование как на plpgsql так и на transact-sql, например, Как написать функцию на PL/pgSQL и Transact-sql – Табличные функции и временные таблицы соответственно.
Я не просто так указал вышеперечисленные статьи, а указал я их, потому, что для более лучшего понимания и усвоения сегодняшнего урока, необходимо начальные знания (это сайт для начинающих программистов), которые как раз Вы можете получить из вышеупомянутого материала.
И так приступим. И для начала давайте рассмотрим, что же это за операторы union и union all.
Что такое UNION и UNION ALL в SQL?
Необходимые условия для операторов union и union all
Теперь давайте поговорим о том, в каких случаях нам может понадобиться использование этих операторов. Ну, например, у Вас есть несколько баз со схожей структурой, каждая из которых создана, например, для какого-нибудь филиала, а Вам необходимо объединить эти данные для предоставления отчетности по всем филиалам руководству и самое простое как это можно сделать, это написать запросы на SQL, каждый из которых будет обращаться к разным базам, и через конструкцию union или union all объединить их. Также иногда бывает необходимо объединить данные в одной базе таким образом, что обычными объединениями это не реализовать и приходится использовать union. Почему я говорю «приходится» да потому что данная конструкция значительно увеличивает время выполнения запроса, если например данных очень много, и злоупотреблять ею не нужно.
Хватит теории, переходим к практике.
Примечание! Как уже говорилось, запросы будем писать в Management Studio для SQL Server 2008
Примеры использования union и union all
Для начала создадим две простых таблицы test_table и test_table_2
Они одинаковые для примера, только разные названия. Я заполнил их вот такими данными:
Теперь давайте напишем запрос, который объединит результирующие данные в одни, например через union. Синтаксис очень прост:
Как Вы видите, вывелось всего 5 строк, так как у нас первая строка в первом запросе и первая строка во втором запросе одинаковые, поэтому они объединились.
Теперь давайте объединим через union all
Здесь уже вывелись все строки, так как мы указали union all.
А теперь давайте рассмотрим, какие могут быть ошибки даже в этом простом запросе. Например, мы перепутали последовательность полей:
Или мы в первом запросе указали дополнительное поле, а во втором этого не сделали.
Также, например, при использовании order by:
Здесь мы указали сортировку в каждом запросе, а нужно было только в последнем, например:
И напоследок, хотел рассказать об одной хитрости, которую можно использовать тогда когда, например, все-таки необходимо вывести в одном запросе какое-то поле, а в других его нет или просто оно не нужно, для этого можете написать вот такой запрос:
т.е. как Вы видите просто там, где должно быть поле ставить пусто и запрос отлично отработает, например:
И еще один небольшой совет, так как запросы при объединении через union довольно обширные, то лучше на их основе создать представление (Views), в случае если данный запрос Вам требуется постоянно, и уже к этому представлению обращаться каждый раз, когда требуется, а зачем нужны представления мы с Вами уже рассматривали вот здесь – Что такое представления и зачем они нужны.
Наверное, все, что я хотел рассказать о конструкции union и union all языка SQL я рассказал, если есть вопросы по использованию этих операторов, задавайте их в комментариях. Удачи!
Заметка! Для комплексного изучения языка T-SQL рекомендую посмотреть мои видеокурсы по T-SQL, в которых используется последовательная методика обучения и рассматриваются все конструкции языка SQL и T-SQL.
В чем разница между UNION и UNION ALL?
Ответов: 22
UNION удаляет повторяющиеся записи (где все столбцы в результатах одинаковы), UNION ALL нет.
SELECT ‘foo’ AS bar UNION SELECT ‘foo’ AS bar Пример:
Результат:
Результат:
И UNION, и UNION ALL объединяют результат двух разных SQL. Они отличаются тем, как они обрабатывают дубликаты.
UNION выполняет DISTINCT в результирующем наборе, исключая любые повторяющиеся строки.
UNION ALL не удаляет дубликаты, а потому быстрее, чем UNION.
Примечание. При использовании этих команд все выбранные столбцы должны быть одного типа данных.
Пример: если у нас есть две таблицы, 1) Сотрудник и 2) Клиент
UNION удаляет дубликаты, тогда как UNION ALL нет.
В принципе, сортировка выполняется быстрее, если она может выполняться в памяти, но применяется одна и та же оговорка об объеме данных.
Конечно, если вам нужны данные, возвращенные без дубликатов, вы должны использовать UNION, в зависимости от источника ваших данных.
Я бы прокомментировал первый пост, чтобы квалифицировать комментарий «гораздо менее результативный», но для этого недостаточно репутации (баллы).
В ORACLE: UNION не поддерживает типы столбцов BLOB (или CLOB), UNION ALL делает.
Как оптимизировать MySQL UNION для высокой скорости
Есть два способа ускорить UNION в базе данных MySQL. Во-первых, используйте UNION ALL, если это вообще возможно, а во-вторых, попытайтесь снизить ваши условия.
1. UNION ALL намного быстрее, чем UNION
Как работает UNION? Представьте, что у вас есть два стола для рубашек. Таблица short_sleeve выглядит следующим образом:
И long_sleeve другой, который выглядит так:
Если вы ОБЪЕДИНИТЕ эти две таблицы, сначала MySQL отсортирует объединенный набор во временную таблицу следующим образом:
После того, как это сделано, он может легко удалить дубликаты синего и дубликата зеленого для этого результирующего набора:
Почему он это делает? UNION определяется таким образом в SQL. Дубликаты должны быть удалены, и для движка MySQL это эффективный способ их удаления. Объедините результаты, сортируйте, удаляйте дубликаты и возвращайте набор.
Что если мы сделали UNION ALL? Результат будет выглядеть так:
Он не должен сортировать и не должен удалять дубликаты. Если вы представляете себе объединение двух 10 миллионов таблиц строк и не должны сортировать, это ускорение может быть ОГРОМНЫМ.
2. Используйте Push-down Условия для ускорения UNION в MySQL
Представьте, что в приведенном выше примере рубашки имеют дату дизайна, год выпуска. Да, мы держим этот пример очень просто, чтобы проиллюстрировать концепцию.
Вот таблица short_sleeve:
И таблица long_sleeve выглядит так:
В 2013 году дизайны могли бы объединить их так:
Здесь предложение WHERE работает с этой временной таблицей из 11 записей:
Но было бы намного быстрее перемещать WHERE внутри каждого подзапроса следующим образом:
Это будет работать на комбинированной таблице 3 записи. Быстрее сортировать и удалять дубликаты. Меньший кэш наборов результатов также лучше, обеспечивая выплату дивидендов. Вот что такое оптимизация производительности!
Помните, что многомиллионные наборы строк в каждой части этого запроса быстро проиллюстрируют оптимизацию. Мы используем очень маленькие результаты, чтобы сделать визуализацию проще.
Вы также можете использовать эту оптимизацию для ORDER BY и для условий LIMIT. Сокращая количество записей, возвращаемых КАЖДОЙ ЧАСТЬЮ СОЮЗА, вы сокращаете объем работы, которая происходит на этапе, когда они объединяются.
Если вы видите некоторые запросы UNION в своем медленном журнале запросов, я предлагаю вам попробовать эту оптимизацию и посмотреть, сможете ли вы ее настроить.
Производительность UNION vs UNION ALL
Я выбираю один столбец внешних ключей из нескольких таблиц через UNION или UNION ALL.
Обычно рекомендуется использовать UNION ALL вместо UNION для проблем с производительностью, когда дубликаты не имеют значения. Однако в моем вызывающем PHP-скрипте было бы более эффективно перебирать данные и манипулировать ими без дубликатов.
Итак, я могу использовать любой из следующих вариантов:
Используйте UNION в базе данных для устранения дубликатов
Используйте UNION ALL в базе данных и удалите данные из моего PHP-скрипта с помощью array_unique () или других подобных функций.
Я предполагаю, что вариант 1 будет предпочтительным и более эффективным методом в большинстве случаев, однако у меня нет ничего, чтобы подтвердить это предположение, и я не уверен, что лучший способ его проверить, тем более что он, вероятно, будет во многом зависеть от того, что данные были.
Верно ли мое предположение в большинстве случаев? Если да, то почему? Если нет, то почему?
2 ответа
Разница в производительности UNION и UNION ALL связана с
необходимо получить отчетливый результат, и для этого движок базы данных и оптимизатор запросов безусловно, более эффективны и действенны, чем фильтрующий алогоритм на основе кода PHP в приложении.
Кроме того, операция dictinct может выиграть от предварительной оптимизации для группировки по функциональности.
Мало того, фильтрация повторяющихся данных обычно основана на упорядоченных данных, в то время как функции select sql работают без явного упорядочивания, и поэтому необходимость фильтрации данных с приложением может привести к менее эффективным и более длинным запросам.
Как правило, механизм db намного эффективнее кода функций PHP приложения, поэтому вариант 1 обычно является лучшим выбором.
Объединение UNION
Для объединения результатов двух и более SQL-запросов SELECT в единую таблицу применяется ключевое слово UNION. Запросыдолжны возвращать одинаковое число и последовательность совместимых типов данных в соответствующих столбцах.
Синтаксис применения SQL запроса UNION
Оператор UNION размещается между SQL запросами :
В представленном примере в одну кучу свалили информацию из трех таблиц, имеющих однотипные столбцы, что, конечно, неправильно, но это работает.
UNION весьма полезен в случаях, когда требуется получить консолидированную информацию, разделенную на несколько одинаковых таблиц, например рабочая и архивная таблицы. В этом случае, с использованием UNION можно извлекать данные, сгруппированные по годам и месяцам, включая и текущий период, а также получить полную информацию, связанную с определенным объектом.
UNION может быть использован совместно с оператором ALL для получения всех записей. Но необходимо отметить, что UNION ALL работает быстрее, чем просто UNION, поскольку при использовании оператора UNION проводится дополнительная фильтрация результата аналогичная SELECT DISTINCT, а при использовании UNION ALL — нет.
Правила применения UNION
При использовании UNION необходимо соблюдать несколько правил, регламентирующих порядок применения оператора :
Тип столбцов нескольких запросов может не совпадать, но обязательно должен быть совместимым путем неявного преобразования. Следует помнить, что столбцы с типом данных XML должны быть эквивалентными, т.е. тип столбцов должен быть определен либо в XML-схеме, либо быть нетипизированными. Типизированные столбцы должны относиться к одной и той же коллекции XML-схем.
Выборка из двух таблиц с использованием UNION
В качестве примера рассмотрим две простенькие таблицы (архивная и рабочая) без определения первичных и внешних ключей, индексов, ограничений на значения колонок и прочих «прелестей» SQL-языка. Допускаем, что в архивной таблице хранится информация о заказах за прошедшие годы. В рабочей таблице размещаются текущие продажи/заказы. Список товаров и услуг, входящих в заказ, не рассматриваем.
Архивная таблица заказов :
Рабочая таблица заказов :
Использование UNION ALL
Запрос выборки данных по продажам товаров (предоставлении услуг) по месяцам за весь период :
Не следует относиться критично к конкатенации и функции преобразования даты; для различных СУБД это может различаться.
Результат выполнения запроса :
Использование UNION
Запрос выборки данных по продажам товаров (предоставлении услуг) по месяцам за весь период для определенного менеджера :
Результат выполнения запроса :
Записи в результирующем наборе можно упорядочить с помощью одного или нескольких полей с использованием оператором ASC или DESC. Номер 1 и 2 в предложении ORDER BY указывает по какому столбцу результирующего набора будет упорядочен результат выборки.
Внешнее объединение с UNION
При помощи UNION можно создавать полные внешние объединения :
Иногда в запросы вставляют дополнительное отладочное поле, чтобы понять к какому SELECT относится текущая запись набора.