Пересечение и разность

В стандарте языка SQL имеются предложения оператора SELECT для выполнения операций пересечения и разности результатов запросов-операндов. Этими предложениями являются INTERSECT [ALL] (пересечение) и EXCEPT [ALL] (разность), которые работают аналогично предложению UNION. В результирующий набор попадают только те строки, которые присутствуют в результатах обоих запросах (INTERSECT), или только те строки первого запроса, которые отсутствуют во втором (EXCEPT). При этом оба запроса, участвующих в операции, должны иметь одинаковое число столбцов, и соответствующие столбцы должны иметь одинаковые (или неявно приводимые) типы данных. Имена столбцов результирующего набора формируются из заголовков первого запроса.

Если не используется ключевое слово ALL (по умолчанию подразумевается DISTINCT), то при выполнении операции автоматически устраняются дубликаты строк. Если указано ALL, то количество дублированных строк подчиняется следующим правилам (n1 - число дубликатов строк первого запроса, n2 - число дубликатов строк второго запроса):

• INTERSECT ALL: min(n1, n2)
• EXCEPT ALL: n1 - n2, если n1>n2.

Пример 5.7.3

SELECT name FROM Ships  
INTERSECT  
SELECT ship FROM Outcomes;

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

SELECT ship FROM Outcomes  
INTERSECT  
SELECT name FROM Ships;

SELECT name FROM (  
                  SELECT ship FROM Outcomes  
                  INTERSECT  
                  SELECT name FROM Ships  
                 ) x;

Рекомендуемые упражнения: 23, 38

Пример 5.7.4

Задача легко решается при помощи оператора EXCEPT:

SELECT ship FROM Outcomes  
EXCEPT  
SELECT name FROM Ships;

Операция разности не является коммутативной, поэтому если переставить местами запросы, то мы получим решение совсем другой задачи:

Эта задача на языке предметной области звучит так: “Найти корабли, которые не принимали участие в сражениях”.

Заметим, что если какой-либо корабль принимал участие в сражениях несколько раз, то по причине исключения дубликатов он будет присутствовать только один раз в результирующем наборе. У нас есть такой корабль - California, но он присутствует также и в таблице Ships, а потому не выводится рассмотренным выше запросом. Поэтому, чтобы продемонстрировать сказанное, исключим его из результатов второго запроса в операции разности:

SELECT ship FROM Outcomes  
EXCEPT  
SELECT name FROM Ships WHERE name <> 'California';

Для имеющегося набора данных тот же результат мы получим при выполнении следующего запроса:

SELECT ship FROM Outcomes  
EXCEPT ALL  
SELECT name FROM Ships;

(2 дубликата для ‘California’ в таблице Outcomes минус 1 - в Ships)

Соответственно, запрос

SELECT ship FROM Outcomes  
EXCEPT ALL  
SELECT name FROM Ships WHERE name<>'California';

Рекомендуемые упражнения: 8, 80

Следует сказать, что не все СУБД поддерживают эти предложения в операторе SELECT. Нет поддержки INTERSECT/EXCEPT, например, в MySQL, а  в MS SQL Server она появилась, лишь начиная с версии 2005, и то без ключевого слова ALL. ALL с INTERSECT/EXCEPT также еще не реализована в Oracle. Следует отметить, что вместо стандартного EXCEPT в Oracle используется ключевое слово MINUS.

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

В заключение рассмотрим пример на использование операции INTERSECT ALL.

Пример 5.7.5

SELECT maker 
FROM (  
       SELECT maker 
       FROM Product 
       WHERE type='PC'  
       INTERSECT ALL  
       SELECT maker 
       FROM Product 
       WHERE type ='Printer'  
      ) X 
GROUP BY maker 
HAVING COUNT(*)>1;

INTERSECT ALL в подзапросе этого решения оставит минимальное число дубликатов, т.е. если производитель выпускает 2 модели ПК и одну модель принтера (или наоборот), то он будет присутствовать в результирующем наборе один раз. Далее мы выполняем группировку по производителю, оставляя только тех из них, кто присутствует в результатах подзапроса более одного раза.

Конечно, мы можем решить эту задачу, не используя явно операцию пересечения. Например, одним подзапросом найдем производителей, которые выпускают не менее 2-х моделей ПК, другим - тех, кто выпускает не менее 2-х моделей принтеров. Решение задачи даст внутреннее соединение этих подзапросов. Ниже этот алгоритм реализован на основе еще одного стандартного типа соединений - естественного соединения:

SELECT PC.maker 
FROM (SELECT maker 
      FROM Product  
      WHERE type='PC' 
      GROUP BY maker   
      HAVING COUNT(*)>1
     ) PC  
NATURAL JOIN  
    (SELECT maker 
     FROM Product  
     WHERE type='Printer' 
     GROUP BY maker   
     HAVING COUNT(*)>1
    ) Pr;

Естественное соединение (NATURAL JOIN) – это эквисоединение по столбцам с одинаковыми именами. SQL Server не поддерживает этот тип соединения, поэтому последний запрос можно выполнить, например, с помощью PostgreSQL.

Тот факт, что операция EXCEPT убирает из результата строки-дубликаты, даёт нам еще один способ исключения дубликатов. Продемонстрируем имеющиеся варианты на примере следующей задачи (используется база данных “Окраска”).

1. Использование DISTINCT

select distinct v_color from utV;

2. Использование GROUP BY

select v_color from utV group by v_color;

3. Использование EXCEPT

Идея решения состоит в том, чтобы “вычесть” из имеющегося набора несуществующий цвет, например, ‘Z’:

select v_color from utV
except
select 'Z';

Поскольку столбец v_color не допускает NULL-значений, последний запрос можно переписать в универсальной форме:

select v_color from utV
except
select NULL;

SQL Server оценивает стоимость всех этих запросов равной. В этом нет ничего удивительного в силу того, что каждый из запросов выполняет чтение таблицы и сортировку, а это наиболее “тяжелые” операции плана запроса.