Учебники

Использование операторов множеств

Май 22, 2019

Операторы множеств используются для объединения результатов двух (или более) операторов SELECT. Операторы SET, доступные в Oracle 11g, это UNION, UNION ALL, INTERSECT и MINUS.

Оператор набора UNION возвращает объединенные результаты двух операторов SELECT. По сути, он удаляет дубликаты из результатов, т. Е. Для каждого дублированного результата будет указана только одна строка. Чтобы противостоять этому поведению, используйте оператор набора UNION ALL, который сохраняет дубликаты в окончательный результат. INTERSECT перечисляет только те записи, которые являются общими для обоих запросов SELECT; оператор множества MINUS удаляет результаты второго запроса из выходных данных, если они также найдены в результатах первого запроса. Операции INTERSECT и MINUS set дают дублированные результаты.

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

Нужно помнить

  • Все участвующие операторы SELECT должны выбирать одинаковое количество столбцов. Имена столбцов, используемые на дисплее, берутся из первого запроса.

  • Типы данных списка столбцов должны быть совместимы / неявно преобразованы оракулом. Oracle не будет выполнять неявное преобразование типов, если соответствующие столбцы в запросах компонентов принадлежат разным группам типов данных. Например, если столбец в первом запросе компонента имеет тип данных DATE, а соответствующий столбец во втором запросе компонента — данные Тип CHAR, Oracle не будет выполнять неявное преобразование, но вызовет ошибку ORA-01790.

  • Позиционное упорядочение должно использоваться для сортировки набора результатов. Индивидуальное упорядочение набора результатов не допускается с помощью операторов Set. ORDER BY может появиться один раз в конце запроса. Например,

  • Операторы UNION и INTERSECT являются коммутативными, то есть порядок запросов не важен; это не меняет окончательный результат.

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

  • Операторы множеств могут быть частью подзапросов.

  • Операторы set нельзя использовать в инструкциях SELECT, содержащих выражения коллекции TABLE.

  • Таблицы LONG, BLOB, CLOB, BFILE, VARRAY или вложенные таблицы не разрешены для использования в операторах Set. Предложение обновления для операторов набора недопустимо.

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

Типы данных списка столбцов должны быть совместимы / неявно преобразованы оракулом. Oracle не будет выполнять неявное преобразование типов, если соответствующие столбцы в запросах компонентов принадлежат разным группам типов данных. Например, если столбец в первом запросе компонента имеет тип данных DATE, а соответствующий столбец во втором запросе компонента — данные Тип CHAR, Oracle не будет выполнять неявное преобразование, но вызовет ошибку ORA-01790.

Позиционное упорядочение должно использоваться для сортировки набора результатов. Индивидуальное упорядочение набора результатов не допускается с помощью операторов Set. ORDER BY может появиться один раз в конце запроса. Например,

Операторы UNION и INTERSECT являются коммутативными, то есть порядок запросов не важен; это не меняет окончательный результат.

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

Операторы множеств могут быть частью подзапросов.

Операторы set нельзя использовать в инструкциях SELECT, содержащих выражения коллекции TABLE.

Таблицы LONG, BLOB, CLOB, BFILE, VARRAY или вложенные таблицы не разрешены для использования в операторах Set. Предложение обновления для операторов набора недопустимо.

UNION

Когда несколько запросов SELECT объединяются с использованием оператора UNION, Oracle отображает объединенный результат всех составных запросов SELECT после удаления всех дубликатов и в отсортированном порядке (по возрастанию по умолчанию), не игнорируя значения NULL.

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

SELECT 1 NUM FROM DUAL
UNION
SELECT 5 FROM DUAL 
UNION
SELECT 3 FROM DUAL
UNION
SELECT 6 FROM DUAL
UNION
SELECT 3 FROM DUAL;

NUM
-------
1
3
5
6

Следует отметить, что столбцы, выбранные в запросах SELECT, должны иметь совместимый тип данных. Oracle выдает сообщение об ошибке при нарушении правила. 

SELECT TO_DATE('12-OCT-03') FROM DUAL
UNION
SELECT '13-OCT-03' FROM DUAL;

SELECT TO_DATE('12-OCT-03') FROM DUAL
       *
ERROR at line 1:
ORA-01790: expression must have same datatype as corresponding expression

СОЮЗ ВСЕХ

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

Рассмотрим запрос, продемонстрированный в разделе UNION. Обратите внимание на разницу в выводе, который генерируется без сортировки и дедупликации. 

SELECT 1 NUM FROM DUAL
UNION ALL
SELECT 5 FROM DUAL 
UNION ALL
SELECT 3 FROM DUAL
UNION ALL
SELECT 6 FROM DUAL
UNION ALL
SELECT 3 FROM DUAL;

NUM
-------
1
5
3
6
3

ПЕРЕСЕЧЕНИЕ

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

Например, приведенный ниже запрос SELECT извлекает зарплату, которая является обычной в отделах 10 и 20. В соответствии со стандартами ISO SQL, INTERSECT превосходит другие по приоритетности при оценке операторов множеств, но это все еще не включено Oracle. 

SELECT SALARY
FROM employees
WHERE DEPARTMENT_ID = 10
INTRESECT
SELECT SALARY 
FROM employees
WHERE DEPARTMENT_ID = 20

SALARY
---------
1500
1200
2000

МИНУС

Оператор минус отображает строки, которые присутствуют в первом запросе, но отсутствуют во втором запросе, без дубликатов и данных, расположенных по умолчанию в порядке возрастания. 

SELECT JOB_ID
FROM employees
WHERE DEPARTMENT_ID = 10
MINUS
SELECT JOB_ID
FROM employees
WHERE DEPARTMENT_ID = 20;

JOB_ID
-------------        
HR
FIN
ADMIN

Соответствие инструкции SELECT

Возможны сценарии, в которых составные операторы SELECT могут иметь различное количество и тип данных выбранных столбцов. Поэтому, чтобы явно соответствовать списку столбцов, столбцы NULL вставляются в отсутствующие позиции, чтобы соответствовать количеству и типу данных выбранных столбцов в каждом операторе SELECT. Для числовых столбцов можно также заменить ноль, чтобы он соответствовал типу столбцов, выбранных в запросе.

В приведенном ниже запросе тип данных имени сотрудника (varchar2) и идентификатора местоположения (номера) не совпадают. Поэтому выполнение приведенного ниже запроса вызовет ошибку из-за проблемы совместимости. 

SELECT DEPARTMENT_ID "Dept", first_name "Employee"
FROM employees
UNION
SELECT DEPARTMENT_ID, LOCATION_ID
FROM departments;

ERROR at line 1:
ORA-01790: expression must have same datatype as corresponding expression

Явно, столбцы могут быть сопоставлены путем замены NULL для идентификатора местоположения и имени сотрудника. 

SELECT DEPARTMENT_ID "Dept", first_name "Employee", NULL "Location"
FROM employees
UNION
SELECT DEPARTMENT_ID, NULL "Employee", LOCATION_ID
FROM departments;

Использование предложения ORDER BY в операциях SET

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

Приведенный ниже запрос на объединение объединяет результаты двух отделов и сортирует их по столбцу SALARY.

119
Share: