Oracle intersect: INTERSECT ОПЕРАТОР — Oracle PL/SQL •MySQL •MariaDB •SQL Server •SQLite

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

Операторы множеств используются для объединения результатов двух (или более) операторов 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.

UNION, UNION ALL, INTERSECT, MINUS

UNION, UNION ALL, INTERSECT, MINUS

UNION, UNION ALL

Пример 1.

SELECT region_id, name

FROM dept

ORDER BY name;

SELECT id, name

FROM region;

REGION_ID  NAME
———- ———————-
1          Administration
1          Finance
1          Operations
2          Operations
5          Operations
4          Operations
3          Operations
1          Sales
2          Sales
3          Sales
4          Sales
5          Sales		ID         NAME
———- ———————-
1          North America
2          South America
3          Africa / Middle East
4          Asia
5          Europe

 SELECT name FROM region
 UNION
 SELECT name FROM dept;

 NAME
 —————————————————
 Administration
 Africa / Middle East Asia
 Europe
 Finance
 North
 America
 Operations
 Sales
 South
 America

Комментарий:

Оператор UNION позволяет объединить две или больше инструкций SELECT. Набор результатов каждой инструкции SELECT вычисляются и расположены друг над другом, а затем сортируются с целью устранения дубликатов.

Пример 2.

 SELECT name FROM region
 UNION ALL
 SELECT name FROM dept;

 NAME
 —————————————————
 North America
 South America
 Africa / Middle East
 Asia
 Europe
 Finance
 Sales
 Sales
 Sales
 Sales
 Sales
 Operations
 Operations
 Operations
 Operations

 . . .

Комментарий:
Оператор UNION ALL не устраняет дубликатов и не сортирует данные. Первые 5 строк из таблицы региона и последние 12 таблицы DEPT.

Пример 3.

 SELECT DISTINCT name FROM region
 UNION ALL
 SELECT DISTINCT name FROM dept
 ORDER BY 1 DESC;

 NAME
 —————————————————
 South America
 Sales
 Operations
 North America
 Finance
 Europe
 Asia
 Africa / Middle East
 Administration

Комментарий:
На этот раз мы использовали оператор DISTINCT, который вызвал устранения дубликатов. Конечный результат сортируется по убыванию — явно пользователем, а не автоматически.

INTERSECT

Пример 4.

 SELECT id FROM customer
 INTERSECT
 SELECT customer_id FROM ord;

         ID
 ———-
        201
        202
        203
        204
        205
        206
        208
        209
        210
        211
        212
        213
        214

Комментарий:
Оператор INTERSECT возвращает строки, которые появляются в обоих запросах SELECT. В нашем случае, возвращаемый результат следует читать следующим образом: результататом запроса является ID клиентов, которые имеют хотябы один заказ.

MINUS

Пример 5.

 SELECT id FROM customer
 MINUS
 SELECT customer_id FROM ord;

         ID
 ———-
        207
        215

Комментарий:
Оператор MINUS возвращает строки, которые появляются в первом запросе SELECT, и не встречаются во втором. В нашем случае, возвращаемый результат следует читать следующим образом: результататом запроса является ID клиентов, которые не сделали ни одного заказа.

Следующее

Предыдущее

Главная страница

Oracle Intersect, Minus, Union [операции SQL]

RelationalDBDesign

  • SiteMap

Оператор Select
«Пред.
Следующий»

  • Расширения SQL
  • Компоненты базы данных
  • Структура БД Oracle
  • просмотров словаря данных
  • Ведение журнала базы данных SQL Plus
  • Oracle SQL отличается
  • Заключение программного обеспечения базы данных
  • Расширения Oracle — викторина
  • Оператор выбора
  • Условия операторов запросов
  • Подключение к иерархическому поиску
  • Союз пересечений минус
  • Союз пересечений минус
  • Оператор группы SQL
  • Расширенные групповые операторы
  • Псевдостолбцы Oracle
  • Заключение по расширению Oracle
  • Манипуляции с данными
  • Функции строки символов
  • Concat Substr Длина
  • instr Синтаксис функции
  • Верхний initcap rtrim
  • to_char, круглый, усеченный
  • круглый, ствол Функции
  • Стандартный формат даты
  • Формат даты Oracle
  • Функции, связанные с действительным временем
  • Функции возвращают значения
  • Сравнение дат
  • Декодирование функций NVL
  • Суммировать с помощью декодирования
  • Реализации расширения функций
  • Запросы на объединение таблиц
  • Внутреннее соединение Внешнее соединение — Викторина
  • Расширенные запросы Заключение
  • Среда SQL Plus
  • SQL Plus TextEditor
  • Местоположение файла SQL Plus сохранено
  • Рабочий лист SQL Plus
  • Создание структур таблиц
  • Установка места для хранения
  • Ограничения уникальности первичного ключа

Урок 4 ОБЪЕДИНЕНИЕ, ПЕРЕСЕЧЕНИЕ и МИНУС
Цель Сравните команды ПЕРЕСЕЧЕНИЕ, МИНУС и ОБЪЕДИНЕНИЕ.

Как объединить результаты двух отдельных запросов?
Oracle предоставляет три команды,

  1. СОЕДИНЕНИЕ ,
  2. ПЕРЕСЕЧЕНИЕ и
  3. МИНУС ,

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

Команда Описание
UNION Наиболее часто используемая команда, UNION объединяет два набора ответов в один набор ответов. Он автоматически удаляет повторяющиеся строки из результатов.
INTERSECT INTERSECT дает вам строки, найденные в обоих запросах, путем исключения строк, которые находятся только в одном или другом запросе.
МИНУС МИНУС дает вам строки, которые находятся в первом запросе, а не во втором запросе, удаляя из результатов все строки, которые находятся только во втором запросе.

Различия между командами

Следующая анимация показывает разницу между этими тремя командами с помощью двух кругов для представления двух наборов результатов запроса, помеченных
A и B. Анимация показывает, какие части двух наборов результатов возвращаются при объединении двух запросов с каждым из трех наборов.
команды: ПЕРЕСЕЧЕНИЕ, затем ОБЪЕДИНЕНИЕ, затем МИНУС.

ПЕРЕСЕЧЕНИЕ, затем ОБЪЕДИНЕНИЕ, затем МИНУС команды

Правила команд

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

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

Вот пример двух запросов, связанных с командой INTERSECT :

 ВЫБЕРИТЕ TO_CHAR(LAST_UPDATE_DATE,'ГГГГ-ММ'),
 ОБНОВЛЕНО_BY_USER
ИЗ ПРОДУКТА
ПЕРЕСЕЧАТЬ
ВЫБЕРИТЕ TO_CHAR (LAST_UPDATE_DATETIME, 'ГГГГ-ММ'),
 CREATED_BY_USER
ОТ PET_CARE_LOG
ЗАКАЗАТЬ ПО 1

Этот запрос находит одинаковые даты (только год и месяц) и имена сотрудников для изменений, внесенных в таблицу PRODUCT , и записи журнала, внесенные в PET_CARE_LOG.
На следующем уроке вы узнаете, как использовать расширенные операторы множеств.

Объединение пересечений минус операции

Щелкните ссылку ниже, чтобы прочитать об операциях пересечения, объединения и минуса, используемых в Oracle.
Пересечение, объединение, минус Операции

Werbung

Werbung

Werbung

Werbung

Werbung

Werbung

Werbung

Select Statement
«Пред.
Следующий»

Операторы Oracle UNION, INTERSECT, MINUS и результат запроса сортировки

Oracle UNION, INTERSECT, MINUS ОПЕРАТОРЫ И ЗАПРОС СОРТИРОВКИ
РЕЗУЛЬТАТ

Операторы UNION [ALL], INTERSECT, MINUS

Вы можете комбинировать несколько запросов, используя операторы множества UNION, UNION ALL,
ПЕРЕСЕЧЕНИЕ и МИНУС. Все операторы множества имеют одинаковый приоритет. Если SQL
оператор содержит несколько операторов набора, Oracle оценивает их слева
вправо, если скобки явно не указывают другой порядок.

UNION Пример

Следующий оператор объединяет результаты с
Оператор UNION, который устраняет повторяющиеся выбранные
ряды.

выберите empno,ename,sal из emp
UNION
выберите
empno,ename,salary from oldemp

Что делать, если вам нужно выбрать строки из двух таблиц, но таблицы имеют
разные колонки?
В этой ситуации вы должны использовать TO_CHAR
функция для заполнения недостающих столбцов.

Например,

Этот оператор показывает, что вы должны сопоставить тип данных (используя
TO_CHAR), когда столбцы не существуют в той или иной таблице:

выберите empno, ename, sal, to_char(null) как «Дата передачи» из emp

 UNION
выберите empno,ename,to_char(null) как «Sal»,tdate from oldemp;

EMPNO     ENAME     SAL
Дата перевода
——     ——     ——
————-
101       Сами
5000     
102       Смит
11 июля 2000 г.
201       Тамим
10 августа 2000 г.
209       Рави
2400     

UNION ALL Пример

Оператор UNION возвращает только отдельные строки,
результат, а оператор UNION ALL возвращает все строки. Оператор UNION ALL
не удаляет повторяющиеся выбранные строки:

выбрать empno,ename из emp
объединение всех
выбрать empno,ename из oldemp;

INTERSECT Пример

Следующий оператор объединяет результаты с оператором INTERSECT
оператор, который возвращает только те строки, которые были возвращены обоими запросами:

SELECT empno FROM emp
INTERSECT
SELECT empno FROM oldemp;

МИНУС Пример

Следующий оператор объединяет результаты с оператором МИНУС, который
возвращает только строки, возвращенные первым запросом, но не возвращенные вторым:

ВЫБРАТЬ empno ИЗ emp
МИНУС
ВЫБРАТЬ empno ИЗ oldemp;

СОРТИРОВКА РЕЗУЛЬТАТОВ ЗАПРОСА

Чтобы отсортировать результат запроса, вы можете использовать предложение ORDER BY в операторе SELECT.