Операции над файлами UNION, EXCEPT и INTERSECT

1. Условия уборочных работ
2. Оператор UNION - объединение файлов
3. UNION ALL - подключение без удаления дубликатов
4. ИСКЛЮЧЕНИЕ - вычитание множеств
5. ИНТЕРСЕКТ - общая часть коллекций
6. Порядок операций

Описанные в первой статье этой главы способы объединения таблиц в предложении FROM - они касались объединения наборов по горизонтали. Были определены типы соединений (включая INNER, OUTER JOIN) и условия сопоставления записей - указанные перед оператором ON.

В результате мы получили набор элементов, описанных с использованием всех столбцов (атрибутов) объединенных таблиц. Вот почему мы называем эту связь горизонтальной .

Вторая группа операций над множествами - это вертикальные операции, т.е.

• UNION - сумма наборов
• ИСКЛЮЧЕНИЕ - вычитание множеств
• INTERSECT - продукт (общая часть)

Они всегда работают с результатами целых запросов (входные таблицы) и возвращают таблицу результатов, которая является набором, идентично определенным как первая входная таблица (количество и имена столбцов). Однако они содержат элементы (строки), соответствующие арифметике множеств, определенных оператором: UNION, UNION ALL, EXCEPT или INTERSECT. В одном запросе мы можем выполнить ряд операций над наборами, например, объединить (UNION) результаты 5 запросов (входные таблицы).

Общие принципы работы над множествами иллюстрируются следующей схемой:

Условия уборочных работ

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

Мы можем выполнять множество операций над наборами, например, объединяя три результата запросов в один набор:

- первый запрос (набор элементов). ВЫБЕРИТЕ «Первый» как Описание, getdate () - как Dt, 132 - номер UNION - оператор объединения файлов - Второй запрос (набор элементов) ВЫБРАТЬ «Второй» - как CompleteOtherDescription, «2013-01- 01 'as DataOrders, 0.2 UNION - объединение файлов, объединение результата первого UNION с третьим запросом - третий запрос (набор элементов) ВЫБЕРИТЕ' Trzeci 'как Opisik,' 2012-11-21 'как dt, 0

Как мы видим в этом примере, все три «запроса» возвращают один элемент, описанный 3 атрибутами. Некоторые из этих атрибутов имеют другой тип (например, 132 из первого набора - целое число, 0,2 во втором - десятичное), но это совместимые типы (возможно их неявное преобразование без потерь).
Если мы попытались объединить наборы разных типов в одних и тех же столбцах, например,

- значения в столбце 3 имеют тип integer, выберите «First» в качестве Description, getdate () в качестве Date, 132 в качестве номера UNION - в свою очередь, здесь, в третьем столбце, у нас есть текст «Second», «2013-01-01», ' сто два '

мы получим сообщение об ошибке:

Сообщение 245, Уровень 16, Состояние 1, Строка 2 Преобразование не удалось при преобразовании значения varchar «сто два» в тип данных int.

Аналогично, если количество столбцов не будет равно:

выберите «Первый» в качестве описания, getdate () в качестве даты, 132 в качестве объединения выберите «Второй», «2013-01-01»

На этот раз мы получим информацию о различном количестве столбцов в наборах, которые должны быть объединены:

Сообщение 205, уровень 16, состояние 1, строка 1 Все запросы, объединенные с использованием оператора UNION, INTERSECT или EXCEPT, должны иметь одинаковое количество выражений в своих целевых списках.

Вы должны помнить этот основной принцип и убедиться, что он всегда выполняется.
Обычно это не сложно, потому что если у вас есть два запроса, которые вы хотите объединить, и они содержат различное количество столбцов, вам просто нужно искусственно «заполнить» пропущенное число, например, значениями NULL:

выберите kol1, kol2, kol3 из объединения таблиц выберите kol1, kol2, NULL из таблицы2

Точно так же, если типы данных не совместимы, вы всегда можете изменить их (например, используя функцию CAST или CONVERT) для более общего типа - например, для varchar.

При объединении файлов анализируется количество и тип их атрибутов (столбцы, возвращаемые в запросах), поэтому это еще одна причина, по которой нам не следует использовать символ «*» в SELECT в производственных средах. Это может привести к ошибкам - будет изменена только одна из таблиц, участвующих в слиянии (добавлен или удален столбец).

Дополнительное условие вытекает из определения множеств и операций над ними. Элементы в коллекции не упорядочены. Поэтому операции над множествами всегда работают с несортированными элементами.
Поскольку сортировка оказывает большое влияние на производительность и, как мы только что сказали, это не имеет значения для результата - поэтому вы не можете использовать оператор ORDER BY в запросах, которые принимают участие в операциях соединения / вычитания или обозначают части общего набора .

Мы можем добавить ORDER BY в самом конце - он будет ссылаться на таблицу результатов (конечный результат всех указанных операций над наборами).

Оператор UNION - объединение файлов

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

Использовать Northwind GO - первый элемент возвращает 9 элементов (некоторые повторяются), выберите «Страна» из [dbo]. [Сотрудники], где Страна похожа на 'U%' UNION - второй запрос возвращает 20 элементов (некоторые повторяются), выберите Страна из [dbo]. [Клиенты], где Страна, как 'U%'

Каждый из запросов, участвующих в операции присоединения файла, возвращает результат значений дублированных элементов (мы не удаляем их с помощью DISTINCT), это выполняется по умолчанию оператором UNION - то есть мы получаем только уникальные значения элементов набора (A + B) - США и Великобритания. Они удаляются как из входных запросов, так и из общей части множества A и B).

UNION ALL - подключение без удаления дубликатов

Второй способ добавления файлов - UNION ALL, то есть без удаления дубликатов. На этот раз из каждого набора мы берем только первые 5 строк (это фиксирует ТОП 5) и, несмотря на то, что элементы повторяются, в результате мы получаем 10 строк.

- первый запрос возвращает 9 элементов (некоторые повторяются) - на этот раз мы берем только первые 5 - ТОП 5 выбираем 5 лучших стран из [dbo]. [Сотрудники], где Страна, например, 'U%', UNION ALL - второй запрос возвращает 20 элементов (некоторые повторяются) - на этот раз мы берем только первые 5 - TOP 5 выбирают топ-5 стран из [dbo]. [Клиенты], где Страна, как 'U%'

ИСКЛЮЧЕНИЕ - вычитание множеств

Принцип работы прост. Из первого набора (то есть слева от оператора EXCEPT) вычитаются все элементы, общие для второго набора (набор результатов, запросы справа).

Вычитание файлов с использованием EXCEPT было реализовано в SQL Server только как EXCEPT DISTINCT, т.е. в наборе результатов все дублирующиеся записи всегда удаляются. пример:

- первый запрос возвращает 4 города - Сиэтл, Такома, Киркланд и Редмонд выбирают город из [dbo]. [Сотрудники], где Страна = 'США', КРОМЕ - оператор вычитает коллекции - второй запрос возвращает еще много городов, среди которых Сиэтл и Киркланд выбирают город из [dbo]. [Клиенты], где Страна = 'США'

Однако, если нам нужно выполнить EXCEPT ALL, не удаляя дубликаты - мы можем сделать это с помощью ROW_NUMBER, планировщика, который даст нам уникальность записей в пределах дубликатов.

Пример действия EXCEPT ALL можно представить в таком сценарии:
Если случится так, что в множестве A появится элемент X 3, а в множестве B элемент X появится дважды, то в наборе результатов элемент X должен появиться один раз.
В этом примере каждый дубликат в наборе пронумерован:

- Q1 первый запрос возвращает 4 города - Сиэтл (дважды), Такома, Киркланд и Редмонд выбирают город, ROW_NUMBER () OVER (разделение по городам по городам) как DuplicateNo из [dbo]. [Сотрудники], где Страна = 'США', КРОМЕ - Q2, второй запрос возвращает намного больше, среди них также Сиэтл (только один раз) и Киркланд, выбирающий город, ROW_NUMBER () OVER (разделение по городам по городам) как DuplicateNo из [ПСЭ]. [Клиенты], где Страна = 'США'

Если вы проверите работу вышеупомянутого запроса - я предлагаю вам начать каждый из них отдельно, чтобы увидеть, какие именно элементы возвращаются. Тогда все это.
Результат операции показан на диаграмме ниже:

ИНТЕРСЕКТ - общая часть коллекций

Для обозначения части совместной коллекции мы используем оператор INTERSECT. Как EXCEPT, реализованный в SQL Server, он также был только как INTERSECT DISTINCT, который является общей частью двух наборов с удалением дубликатов.

- Q1, первый запрос возвращает 4 города - Сиэтл (дважды), Такома, Киркленд и Редмонд выбирают город из [dbo]. [Сотрудники], где Страна = 'США' ИНТЕРСЕКТ - Q2 второй запрос возвращает гораздо больше, но среди них также Сиэтл (только один раз) и Киркланд выбирают город из [dbo]. [Клиенты], где Страна = 'США'

Если вы хотите назначить общую деталь без удаления дубликатов, вы можете использовать точно такой же прием, как и прежде, то есть нумерацию записей. Тогда, если в двух коллекциях будет два дубликата одного и того же элемента - они будут возвращены из-за уникальности элементов, которые ROW_NUMBER представит нам.

- Q1 первый запрос возвращает 2 страны, пронумерованные с использованием ROW_NUMBER - 4 дубликата Великобритании и 5 дубликатов США, выбранной страны, ROW_NUMBER () OVER (порядок по странам по странам) как rn из [dbo]. [Сотрудники], где Страна, например, «U%», ИНТЕРСЕКТ - Q2, второй запрос возвращает гораздо больше, но среди них также есть - как минимум 4 UK и мин. 5 США выбирают страну, ROW_NUMBER () OVER (разделение по странам, порядок по странам) как rn из [dbo]. [Клиенты], где Страна, как 'U%'

Порядок операций

Мы можем работать с несколькими коллекциями как часть одного запроса и применять различные операции. Здесь применяется порядок выполнения действий - как в математике.

Выберите kol1, kol2, kol3 из таблицы 1 UNION Выберите kol1, kol2, kol3 из таблицы 2 ИСКЛЮЧИТЬ Выберите kol1, kol2, kol3 из таблицы3 INTERSECT (выберите kol1, kol2, kol3 из таблицы 4 UNION Выберите kol1, kol2, kol3 из таблицы 5)

Во-первых, будут выполнены действия в скобках, т. Е. Объединение двух последних запросов (извлечение данных из таблиц 4 и 5). Далее, умножение множеств, то есть INTERSECT - часть совпадает между результатом, определенным на первом шаге, и запросом, извлекающим данные из таблицы3.

Наконец, если скобок и продуктов больше нет, будут выполнены все оставшиеся шаги слева направо, в данном случае сначала первый UNION и, наконец, ИСКЛЮЧЕНИЕ.

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

Похожие

Комментарии