Sql intersect: SQL оператор INTERSECT — Oracle PL/SQL •MySQL •MariaDB •SQL Server •SQLite

Операторы intersect, union, union all, except и minus в SQL: m_i_kuznetsov — LiveJournal

?

Category:

  • IT
  • Cancel

Недавно на собеседовании пришлось общаться с обладателем титула MCDBA, которого оператор intersect поставил в тупик. И на самом деле, нередко встречаются разработчики БД, которые за долгую свою карьеру ни разу с этими операторами не сталкивались.

Вот простая иллюстрация для понимания сути операторов:

Или в более динамичном виде:

И от меня довесок. Пусть будут в наличии две таблицы, T1 и T2. У каждой по одному полю: T1.a и T2.b. В каждой — по четыре записи:


SELECT * FROM T1


SELECT * FROM T2

И теперь сделаем несколько нужных нам запросов:


SELECT * FROM T1
INTERSECT
SELECT * FROM T2


SELECT * FROM T1
EXCEPT
SELECT * FROM T2


SELECT * FROM T1
UNION
SELECT * FROM T2


SELECT * FROM T1
UNION ALL
SELECT * FROM T2

Tags: sql, базы данных

Subscribe

  • Видео: Основы SQL

    Видеокурс от Андрея Созыкина «Основы SQL» на примере PostgreSQL. 1. Базы данных и SQL 2. Оператор SELECT 3. Фильтрация данных в SQL: WHERE…

  • Тринадцатый семинар для молодых аналитиков

    Очень тяжёлая тема для понимания — диаграмма классов. И тяжёлая не только с точки зрения понимания самой диаграммы, но и с точки зрения её…

  • 3D-модель с помощью запроса SQL

    Очень прикольная реализация трёхмерного кубика с помощью запросов SQL от Ярослава Сергиенко. Этот кубик и покрутить можно 🙂 SQL 3d engine

  • SQL: что тормозит удаление записей

    В реляционных БД есть 8 основных причин тормозов на DELETE (не считая кривых запросов): большое количество индексов в таблице, из которой…

  • О том, как код писать не надо

    Вот реальная история о том, что некоторые безобразно написанные сайты даже взламывать не нужно… И так сойдёт… или как данные 14 миллионов россиян…

  • NULL в SQL

    Когда некоторое значение не определено, запросы SQL могут взорвать неподготовленную заранее голову. Нет, ну правда, сами судите. Есть у меня таблица…

  • Развёртывание текстовой строки в набор данных SQL

    Как делать вот такое: знает автор этой статьи: Split string into rows and columns in SQL Server

  • Разница между разными видами объединения (join) наборов данных в SQL

    Всё просто и наглядно: Источник: Visual Representation of SQL Joins.

  • Полезные web-заметки

    На мой взгляд, полезный блог: Nice Web Notes.

Photo

Hint http://pics.livejournal.com/igrick/pic/000r1edq

Понимание оператора SQL INTERSECT

Резюме : в этом руководстве объясняется оператор SQL INTERSECT и показано, как его применять для получения пересечения двух или более запросов.

Знакомство с оператором SQL INTERSECT

Оператор INTERSECT — это оператор набора, который возвращает отдельные строки из двух или более наборов результатов из операторов SELECT.

Предположим, у нас есть две таблицы: A(1,2) и B(2,3).

На следующем рисунке показано пересечение таблиц A и B.

Фиолетовая секция — это пересечение зеленого и синего наборов результатов.

Как и оператор UNION, оператор INTERSECT удаляет повторяющиеся строки из конечного результирующего набора.

Следующая инструкция иллюстрирует, как использовать оператор INTERSECT для поиска пересечения двух результирующих наборов.

 
  ВЫБЕРИТЕ
идентификатор
ОТ
а
ПЕРЕСЕЧАТЬ
ВЫБИРАТЬ
идентификатор
ОТ
б;  
  Язык кода: SQL (язык структурированных запросов) (sql)

Чтобы использовать оператор INTERSECT, столбцы операторов SELECT должны соответствовать правилам:

  • Типы данных столбцов должны быть совместимы.
  • Количество столбцов и их порядок в операторах SELECT должны совпадать.

Пример оператора SQL INTERSECT

Следующий оператор SELECT возвращает строки из таблицы A:

 
  SELECT
идентификатор
ОТ
А;  
  Язык кода: SQL (язык структурированных запросов) (sql)

Следующий оператор извлекает данные из таблицы B:

 
  ВЫБОР
идентификатор
ОТ
Б;  
  Язык кода: SQL (язык структурированных запросов) (sql)

В следующем операторе оператор INTERSECT используется для получения пересечения обоих запросов.

 
  ВЫБЕРИТЕ
идентификатор
ОТ
а
ПЕРЕСЕЧАТЬ
ВЫБИРАТЬ
идентификатор
ОТ
б;  
  Язык кода: SQL (язык структурированных запросов) (sql)

Пример SQL INTERSECT с ORDER BY

Чтобы отсортировать результирующий набор, возвращаемый оператором INTERSECT, вы помещаете предложение ORDER BY в конец всех операторов.

Например, следующий оператор применяет оператор INTERSECT к таблицам A и B и сортирует объединенный набор результатов по столбцу id в порядке убывания.

 
  ВЫБЕРИТЕ
идентификатор
ОТ
а
ПЕРЕСЕЧАТЬ
ВЫБИРАТЬ
идентификатор
ОТ
б
ЗАКАЗАТЬ ПО ИДЕНТИФИКАТОРУ DESC;  
  Язык кода: SQL (язык структурированных запросов) (sql)

Эмуляция оператора SQL INTERSECT с использованием предложения INNER JOIN

Большинство систем реляционных баз данных поддерживают оператор INTERSECT, например Oracle Database, Microsoft SQL Server, PostgreSQL и т. д. Однако , некоторые системы баз данных не поддерживают оператор INTERSECT, например MySQL.

Чтобы эмулировать оператор SQL INTERSECT, вы можете использовать предложение INNER JOIN следующим образом:

 
  SELECT
помогать
ОТ
а
ВНУТРЕННЕЕ СОЕДИНЕНИЕ b ON b. id = a.id  
  Язык кода: SQL (язык структурированных запросов) (sql)

Возвращает строки в таблице A, которые имеют совпадающие строки в таблице B, что дает то же самое результат как оператор INTERSECT.

Теперь вы должны хорошо понимать оператор SQL INTERSECT и знать, как использовать его для поиска пересечений нескольких запросов.

Было ли это руководство полезным?

Вероятно, вы недостаточно часто используете SQL INTERSECT или EXCEPT — Java, SQL и jOOQ.

Когда люди говорят о SQL JOIN, они часто используют диаграммы Венна, чтобы проиллюстрировать включение и исключение двух объединенных наборов:

Хотя эти диаграммы Венна, безусловно, полезны для понимания (и запоминания) синтаксиса SQL JOIN, они не совсем точны, поскольку SQL JOIN — это особый тип декартова произведения, CROSS JOIN.

Иллюстрация пользователя Википедии Quartl

В декартовом произведении двух наборов A и B результатом является умножение каждого набора, а это означает, что каждый элемент a ∈ A комбинируется с каждым элементом b ∈ B , чтобы сформировать набор кортежей (a, b ) .

Обычные SQL JOIN делают именно это. Когда вы соедините BOOK с AUTHOR, вы, вероятно, получите комбинацию каждого автора ∈ AUTHOR с каждой книг ∈ BOOK , так что для каждой комбинации (автор, книга) , автор на самом деле написал книгу.

Истинное значение диаграмм Венна

Истинный смысл диаграмм Венна намного лучше описывается операциями

  • СОЕДИНЕНИЕ
  • ПЕРЕСЕЧЕНИЕ
  • ЗА ИСКЛЮЧЕНИЕМ (или МИНУС в Oracle)

В следующих разделах мы увидим, что эти операции соответствуют ровно 9 раз.0142 семантику операций, которые можно проиллюстрировать диаграммами Венна, даже если вы сможете «злоупотреблять» СОЕДИНЕНИЕМ операций для достижения того же результата.

СОЕДИНЕНИЕ

Операция UNION является наиболее известной среди этих операций над множествами. Его также часто называют «конкатенацией» двух наборов кортежей, где результатом является объединение набора B с набором A .

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

Оригинальные диаграммы Венна использовали FULL OUTER JOIN для моделирования «одной и той же» концепции, хотя эти две вещи не являются строго одинаковыми. Рассмотрим следующий запрос, который мы запустим к базе данных Sakila:


ВЫБЕРИТЕ имя, фамилия
ОТ клиента
СОЮЗ
ВЫБЕРИТЕ имя, фамилия
ОТ персонала
ЗАКАЗАТЬ ПО first_name, last_name

Результат выглядит так:

имя Фамилия
------------------------------------
ААРОН СЕЛБИ
АДАМ ГУЧ
АДРИАН КЛЭРИ
АГНЕС БИШОП
АЛАН КАН
АЛЬБЕРТ КРУЗ
АЛЬБЕРТО ХЕННИНГ
АЛЕКС ГРЕШЭМ
АЛЕКСАНДР ФЕННЕЛЛ
АЛЬФРЕД КАСИЛЬЯС
АЛЬФРЕДО МКАДАМС
ЭЛИС СТЮАРТ
АЛИСИЯ МИЛЛС
. ..

Теперь запустите следующий «эквивалентный» запрос (на самом деле он не эквивалентен в отношении дубликатов):


ВЫБЕРИТЕ имя, фамилия
ОТ клиента
ПОЛНОЕ ВНЕШНЕЕ СОЕДИНЕНИЕ
  USING (имя, фамилия)
ЗАКАЗАТЬ ПО first_name, last_name

Результат снова даст:

имя Фамилия
------------------------------------
ААРОН СЕЛБИ
АДАМ ГУЧ
АДРИАН КЛЭРИ
АГНЕС БИШОП
АЛАН КАН
АЛЬБЕРТ КРУЗ
АЛЬБЕРТО ХЕННИНГ
...

Это работает только потому, что мы используем предложение USING , которое не каждая база данных поддерживает изначально. Если бы мы использовали JOIN с более часто используемым предложением ON, нам пришлось бы писать более утомительно:


ВЫБИРАТЬ
  COALESCE(c.first_name, s.first_name) КАК first_name,
  COALESCE(c.last_name, s.last_name) AS last_name
ОТ клиента c
ПОЛНОЕ ВНЕШНЕЕ СОЕДИНЕНИЕ
  ON (c. first_name, c.last_name)
  = (s.first_name, s.last_name)
ЗАКАЗАТЬ ПО first_name, last_name

В этом случае большинство людей, вероятно, уже по умолчанию используют UNION , так как это гораздо более известная операция, чем FULL OUTER JOIN .

Все RDBMS, поддерживаемые jOOQ в настоящее время, поддерживают UNION и UNION ALL (последняя не удаляет дубликаты).

Далее мы увидим, что эквивалентные сравнения могут быть сделаны с другими операциями над множествами:

ПЕРЕСЕЧЕНИЕ

ПЕРЕСЕЧЕНИЕ 9Операция 0023 действительно полезна, когда вы хотите сохранить только те кортежи, которые присутствуют в обоих наборах, объединенных с помощью INTERSECT :

Как видите, мы можем захотеть сохранить только тех клиентов, которые также являются актерами. Давайте запустим этот запрос:


ВЫБЕРИТЕ имя, фамилия
ОТ клиента
ПЕРЕСЕЧАТЬ
ВЫБЕРИТЕ имя, фамилия
ОТ актера

 
имя Фамилия
------------------------------------
ДЖЕННИФЕР ДЭВИС

Один из наших клиентов тоже актер. Тот же запрос можно было написать с INNER JOIN как таковой:


ВЫБЕРИТЕ имя, фамилия
ОТ клиента
ВНУТРЕННЕЕ СОЕДИНЕНИЕ актер
  USING (имя, фамилия)

… или с синтаксисом ON 


ВЫБЕРИТЕ c.first_name, c.last_name
ОТ клиента c
ВНУТРЕННЕЕ СОЕДИНЕНИЕ актер a
  ON (c.first_name, c.last_name)
  = (a.first_name, a.last_name)

На этот раз COALESCE не требуется, так как INNER JOIN сохраняет только те кортежи из декартова произведения, которые присутствуют «с обеих сторон» JOIN , поэтому мы можем выбрать любую из таблиц для префикса наших столбцов. .

Вместо этого вы можете даже решить использовать полусоединение, которое даст те же результаты:


ВЫБЕРИТЕ имя, фамилия
ОТ клиента
ГДЕ (имя, фамилия) В (
  ВЫБЕРИТЕ имя, фамилия
  ОТ актера
)

или, используя более подробный, но эквивалентный предикат EXISTS :


ВЫБЕРИТЕ имя, фамилия
ОТ клиента c
ГДЕ СУЩЕСТВУЕТ (
  ВЫБЕРИТЕ 1
  ОТ актера а
  ГДЕ (c. first_name, c.last_name)
      = (a.first_name, a.last_name)
)

Все вышеперечисленное, опять же, дает:

имя Фамилия
------------------------------------
ДЖЕННИФЕР ДЭВИС

, КРОМЕ

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

Выполнение этого запроса:


ВЫБЕРИТЕ имя, фамилия
ОТ клиента
КРОМЕ
ВЫБЕРИТЕ имя, фамилия
ОТ персонала
ЗАКАЗАТЬ ПО first_name, last_name

… будет давать:

имя Фамилия
------------------------------------
ААРОН СЕЛБИ
АДАМ ГУЧ
АДРИАН КЛЭРИ
АГНЕС БИШОП
АЛАН КАН
АЛЬБЕРТ КРУЗ
АЛЬБЕРТО ХЕННИНГ
...

Согласно оригинальным диаграммам Венна, это можно настроить с помощью LEFT JOIN и предиката IS NULL :


ВЫБЕРИТЕ имя, фамилия
ОТ клиента
ВЛЕВО ПРИСОЕДИНЯЙТЕСЬ к персоналу
  USING (имя, фамилия)
ГДЕ staff_id имеет значение NULL
ЗАКАЗАТЬ ПО first_name, last_name

или с пунктом ON :


ВЫБЕРИТЕ c. first_name, c.last_name
ОТ клиента c
LEFT JOIN персонала s
  ON (c.first_name, c.last_name)
  = (s.first_name, s.last_name)
ГДЕ staff_id имеет значение NULL
ЗАКАЗАТЬ ПО first_name, last_name

Это совершенно нечитаемо и не сообщает о том, что мы удаляем кортежи из набора CUSTOMER , учитывая их присутствие в другом наборе STAFF .

Эквивалентная версия, использующая антисоединение, может быть более читаемой (однако следите за NULL в предикатах NOT IN !):


ВЫБЕРИТЕ c.first_name, c.last_name
ОТ клиента c
ГДЕ (имя, фамилия) НЕ В (
  ВЫБЕРИТЕ имя, фамилия
  ОТ персонала
)
ЗАКАЗАТЬ ПО first_name, last_name

… или, используя NOT EXISTS :

ВЫБЕРИТЕ c.first_name, c.last_name
 ОТ клиента c
 ГДЕ НЕ СУЩЕСТВУЕТ(
 ВЫБЕРИТЕ 1
 ОТ персонала с
 ГДЕ (c.first_name, c.last_name)
 = (s.