Intersect oracle: 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

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

  • Расширения 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.
Пересечение, объединение, минус Операции

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

Пересечение и Минус в Oracle SQL | Учебники по Oracle SQL -25

Мехмет Салих Девечи
8 июля 2020 г.
Оставить комментарий

Привет,

Я объясню Union и Union All в Oracle SQL в этом посте из серии учебных пособий по Oracle SQL.

Прочтите предыдущую публикацию этой серии руководств перед этой.

Объединение и объединение Все в Oracle SQL | Oracle SQL Tutorials -24

Оператор пересечения

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

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

 

Синтаксис оператора пересечения

Синтаксис оператора пересечения следующий.

 ВЫБРАТЬ столбец 1, столбец 2, столбец (n)... ИЗ таблицы 1
  ПЕРЕСЕЧЕНИЕ 
ВЫБЕРИТЕ столбец 1, столбец 2, столбец (n)... ИЗ таблицы 2;

 

Пример оператора пересечения выглядит следующим образом.

 SQL> Выберите имя, фамилию, зарплату из hr.employees, где зарплата от 2000 до 5000
         пересекаются 
       выберите имя, фамилию, зарплату из hr.employees, где зарплата от 4000 до 7500;
FIRST_NAME LAST_NAME ЗАРПЛАТА
-------------------- ------------------------- ----- -----
Алана Уолш 4100
Александр Ху 4100
Энтони Кабриолет 4000
Бритни Эверетт 4900
Кертис Дэвис 4100
Джейсон Маллин 4300
Жан Флер 4100
Дженнифер Дилли 4600
Джулия Деллинджер 4400
Юлия Найер 4200
Келли Чанг 4800
FIRST_NAME LAST_NAME ЗАРПЛАТА
-------------------- ------------------------- ----- -----
Кевин Фини 4000
Лаура Биссо 4300
Ренске Ладвиг 4600
Сэмюэл Маккейн 4200
Сара Белл 5000
Стивен Стайлз 4200
Тренна Райс 4500
Уинстон Тейлор 4200
19строки выбраны. 
SQL>

Оператор минус

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

Если вам нужна разница двух таблиц (разные строки), вы должны использовать оператор минус.

 

Минус таблицы A из таблицы B выделен светло-оранжевым цветом следующим образом.

 

 

 

Минус-синтаксис

Минус-оператор имеет следующий синтаксис.

 ВЫБРАТЬ столбец 1, столбец 2, столбец (n)... ИЗ таблицы 1
  МИНУС 
ВЫБЕРИТЕ столбец 1, столбец 2, столбец (n)... ИЗ таблицы 2; 

 SQL> SELECT employee_id, job_id
       ОТ ч. сотрудников
       минус
       ВЫБЕРИТЕ employee_id, job_id
       ОТ hr.job_history;
EMPLOYEE_ID JOB_ID
----------- ----------
        101 АД_ВП
        102 АД_ВП
        103 ИТ_ПРОГ
        104 ИТ_ПРОГ
        105 ИТ_ПРОГ
        106 ИТ_ПРОГ
        107 ИТ_ПРОГ
        108 FI_MGR
        109FI_ACCOUNT
        110 FI_ACCOUNT
        111 FI_ACCOUNT
EMPLOYEE_ID JOB_ID
----------- ----------
        112 FI_ACCOUNT
        113 FI_ACCOUNT
        114 ПУ_МАН
        115 ПУ_КЛЕРК
        116 ПУ_КЛЕРК
        117 ПУ_КЛЕРК
        118 ПУ_КЛЕРК
        119 ПУ_КЛЕРК
        120 ST_MAN
        121 СТ_МАН
        122 СТ_МАН
EMPLOYEE_ID JOB_ID
----------- ----------
        123 СТ_МАН
        124 СТ_МАН
        125 СТ_КЛЕРК
        126 СТ_КЛЕРК
        127 СТ_КЛЕРК
        128 СТ_КЛЕРК
        129ST_CLERK
        130 СТ_КЛЕРК
        131 СТ_КЛЕРК
        132 СТ_КЛЕРК
        133 СТ_КЛЕРК
EMPLOYEE_ID JOB_ID
----------- ----------
        134 СТ_КЛЕРК
        135 СТ_КЛЕРК
        136 СТ_КЛЕРК
        137 СТ_КЛЕРК
        138 СТ_КЛЕРК
        139 СТ_КЛЕРК
        140 СТ_КЛЕРК
        141 СТ_КЛЕРК
        142 СТ_КЛЕРК
        143 СТ_КЛЕРК
        144 СТ_КЛЕРК
EMPLOYEE_ID JOB_ID
----------- ----------
        145 СА_MAN
        146 СА_МАН
        147 СА_МАН
        148 СА_МАН
        149 СА_МАН
        150 СА_РЕП
        151 СА_РЕП
        152 СА_РЕП
        153 СА_РЕП
        154 СА_РЕП
        155 СА_РЕП
EMPLOYEE_ID JOB_ID
----------- ----------
        156 СА_РЕП
        157 СА_РЕП
        158 СА_РЕП
        159SA_REP
        160 СА_РЕП
        161 СА_РЕП
        162 СА_РЕП
        163 СА_РЕП
        164 СА_РЕП
        165 СА_РЕП
        166 СА_РЕП
EMPLOYEE_ID JOB_ID
----------- ----------
        167 СА_РЕП
        168 СА_РЕП
        169 СА_РЕП
        170 СА_РЕП
        171 СА_РЕП
        172 СА_РЕП
        173 СА_РЕП
        174 СА_РЕП
        175 СА_РЕП
        177 СА_РЕП
        178 СА_РЕП
EMPLOYEE_ID JOB_ID
----------- ----------
        179 СА_РЕП
        180 SH_CLERK
        181 SH_CLERK
        182 SH_CLERK
        183 SH_CLERK
        184 SH_CLERK
        185 SH_CLERK
        186 SH_CLERK
        187 SH_CLERK
        188 SH_CLERK
        189SH_CLERK
EMPLOYEE_ID JOB_ID
----------- ----------
        190 SH_CLERK
        191 SH_CLERK
        192 SH_CLERK
        193 SH_CLERK
        194 SH_CLERK
        195 SH_CLERK
        196 SH_CLERK
        197 SH_CLERK
        198 SH_CLERK
        199 SH_CLERK
        201 МК_МАН
EMPLOYEE_ID JOB_ID
----------- ----------
        202 МК_РЕП
        203 ЧАС_РЕП
        204 PR_REP
        205 AC_MGR
        206 AC_ACCOUNT
Выбрано 104 строки. 
SQL>

 

 

Вы должны использовать MINUS вместо EXISTS

Оператор «минус» в основном работает в более быстром плане выполнения, чем оператор NOT IN и NOT Exists.

 

 

Если вы хотите изучить Учебное пособие по Oracle SQL для начинающих, прочитайте следующие статьи.

Учебники по Oracle SQL для начинающих. Изучайте Oracle SQL с нуля с помощью онлайн-курса Oracle SQL Сертифицированный эксперт по базам данных Oracle и SQL Server, Goldengate, Exadata Machine, администратор Oracle Database Appliance с более чем 10-летним опытом работы. У меня есть сертификаты экспертов OCA, OCP, OCE RAC. в качестве консультанта, Insource или Outsource. Я выполнил более 200 операций в этих клиентах, таких как установка Exadata, PoC, миграция и обновление, обновление базы данных Oracle и SQL Server, установка Oracle RAC, установка SQL Server AlwaysOn, миграция базы данных, аварийное восстановление, Восстановление резервной копии, настройка производительности, периодические проверки работоспособности.