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

В этом учебном пособии вы узнаете, как использовать оператор INTERSECT с синтаксисом и примерами.

Описание

Хотя в MySQL нет оператора INTERSECT, вы можете легко имитировать этот тип запроса, используя либо предложение IN, либо предложение EXISTS, в зависимости от сложности INTERSECT запроса.

Во-первых, давайте разберем, что такое запрос INTERSECT. Запрос INTERSECT возвращает пересечение двух или более наборов данных. Если запись существует в обоих наборах данных, она будет включена в результаты INTERSECT. Однако, если запись существует в одном наборе данных, а не в другом, она будет опущена из результатов INTERSECT.

Запрос INTERSECT

Пояснение: Запрос INTERSECT вернет записи в серой затененной области. Это записи, которые существуют как в SELECT1, так и в SELECT2.

Синтаксис

Синтаксис для оператора INTERSECT в MySQL:

SELECT expression1, expression2, ... expression_n
FROM tables
[WHERE conditions]
INTERSECT
SELECT expression1, expression2, ... expression_n
FROM tables
[WHERE conditions];

Параметры или аргументы

expression1, expression2, ... expression_n - столбцы или вычисления, которые вы хотите получить.
tables - таблицы, из которых вы хотите получить записи. Должна быть хотя бы одна таблица, перечисленная в операторе FROM.
WHERE conditions - необязательный. Условия, которые должны быть выполнены для выбранных записей.

Примечание

В обоих предложениях SELECT должно быть одинаковое количество выражений и одинаковые типы данных.

Пример с одним выражением

Во-первых, давайте рассмотрим, как имитировать запрос INTERSECT в MySQL, который имеет одно поле с тем же типом данных.
Если база данных поддерживала оператор INTERSECT (чего нет у MySQL), так вы бы использовали оператор INTERSECT для возврата общих значений category_id между таблицами products и inventory.

SELECT category_id
FROM products
INTERSECT
SELECT category_id
FROM inventory;

SELECT category_id

FROM products

INTERSECT

SELECT category_id

FROM inventory;

Поскольку вы не можете использовать оператор INTERSECT в MySQL, вы будете использовать оператор IN для имитации запроса INTERSECT следующим образом:

SELECT products.category_id
FROM products
WHERE products.category_id IN (SELECT inventory.category_id FROM inventory);

SELECT products.category_id

FROM products

WHERE products.category_id IN (SELECT inventory.category_id FROM inventory);

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

Теперь давайте усложним наш пример, добавив условия WHERE к запросу INTERSECT.

Например, так выглядит INTERSECT с условиями WHERE:

SELECT category_id
FROM products
WHERE category_id < 100
INTERSECT
SELECT category_id
FROM inventory
WHERE quantity > 0;

SELECT category_id

FROM products

WHERE category_id < 100

INTERSECT

SELECT category_id

FROM inventory

WHERE quantity > 0;

Вот как вы могли бы моделировать запрос INTERSECT с помощью оператора IN и включать условия WHERE:

SELECT products.category_id
FROM products
WHERE products.category_id < 100
AND products.category_id IN
   (SELECT inventory.category_id
    FROM inventory
    WHERE inventory.quantity > 0);

SELECT products.category_id

FROM products

WHERE products.category_id < 100

AND products.category_id IN

(SELECT inventory.category_id

FROM inventory

WHERE inventory.quantity > 0);

В этом примере были добавлены предложения WHERE, которые фильтруют как таблицу products, так и результаты из таблицы inventory.

Пример с несколькими выражениями

Затем давайте посмотрим, как имитировать запрос INTERSECT в MySQL, который возвращает более одного столбца.

Во-первых, так вы должны использовать оператор INTERSECT для возврата нескольких выражений.

SELECT contact_id, last_name, first_name
FROM contacts
WHERE contact_id < 100
INTERSECT
SELECT customer_id, last_name, first_name
FROM customers
WHERE last_name <> 'Johnson';

SELECT contact_id, last_name, first_name

FROM contacts

WHERE contact_id < 100

INTERSECT

SELECT customer_id, last_name, first_name

FROM customers

WHERE last_name <> 'Johnson';

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

SELECT contacts.contact_id, contacts.last_name, contacts.first_name
FROM contacts
WHERE contacts.contact_id < 100
AND EXISTS (SELECT *
            FROM customers
            WHERE customers.last_name <> 'Markoski'
            AND customers.customer_id = contacts.contact_id
            AND customers.last_name = contacts.last_name
            AND customers.first_name = contacts.first_name);

SELECT contacts.contact_id, contacts.last_name, contacts.first_name

FROM contacts

WHERE contacts.contact_id < 100

AND EXISTS (SELECT *

FROM customers

WHERE customers.last_name <> 'Markoski'

AND customers.customer_id = contacts.contact_id

AND customers.last_name = contacts.last_name

AND customers.first_name = contacts.first_name);

В этом более сложном примере вы можете использовать предложение EXISTS для возврата нескольких выражений, которые существуют как в таблице contacts, где contact_id меньше 100, так и в таблице customers, где last_name не равно 'Markoski'.

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

AND customers.customer_id = contacts.contact_id
AND customers.last_name = contacts.last_name
AND customers.first_name = contacts.first_name

Это соединение выполняется для того, чтобы поля customer_id, last_name и first_name из таблицы customers пересекались бы с полями contact_id, last_name и first_name из таблицы contacts.