четверг, 23 февраля 2017 г.

Традиционные операции над множествами и оператор SELECT

Традиционные операции над множествами и оператор SELECT

Традиционные операции над множествами - это объединениепересечениеразность и декартово произведение.

Декартово произведение

Ранее мы уже рассмотрели реализацию декартова произведения, перечисляя через запятую табличные выражения в предложении FROM (таблицы, представления, подзапросы). Кроме того, можно использовать еще одну явную операцию соединения:

SELECT Laptop.model, Product.model
FROM Laptop CROSS JOIN Product;

Напомним, что при декартовом произведении каждая строка из одной таблицы соединяется с каждой строкой второй таблицы. В результате количество строк результирующего набора равно произведению количества строк операндов декартова произведения. В нашем примере таблица Laptop содержит 5 строк, а таблица Product - 16. В результате получается 5*16 = 80 строк. Поэтому мы не приводим здесь результат выполнения этого запроса. Вы можете сами проверить это утверждение, выполнив приведенный выше запрос на учебной базе данных.
В чистом виде декартово произведение практически не используется. Оно, как правило, является промежуточным результатом выполнения операции горизонтальной проекции (выборки) при наличии в операторе SELECT предложения WHERE.

Объединение

Для объединения запросов используется служебное слово UNION:
<запрос 1>
UNION [ALL]
<запрос 2>
Оператор UNION объединяет выходные строки каждого из запросов в один результирующий набор. Если определен параметр ALL, то сохраняются все дубликаты выходных строк, в противном случае в результирующем наборе остаются только уникальные строки. Заметим, что можно связывать вместе любое число запросов. Кроме того, с помощью скобок можно менять порядок объединения.
При этом должны выполняться следующие условия:
  • Количество выходных столбцов каждого из запросов должно быть одинаковым.
  • Выходные столбцы каждого из запросов должны быть сравнимыми между собой (в порядке их следования) по типам данных.
  • В результирующем наборе используются имена столбцов, заданные в первом запросе.
  • Предложение ORDER BY применяется к результату соединения, поэтому оно может быть указано только в конце составного запроса.
Пример. Найти номера моделей и цены ПК и ПК-блокнотов:
SELECT model, price
FROM PC
UNION
SELECT model, price
FROM Laptop
ORDER BY price DESC;

modelprice
17501200.0
17521150.0
12981050.0
1233980.0
1321970.0
1233950.0
1121850.0
1298700.0
1232600.0
1233600.0
1232400.0
1232350.0
1260350.0
Пример. Найти тип продукции, номер модели и цену ПК и ПК-блокнотов:
SELECT Product .type, PC.model, price
FROM PC INNER JOIN
    Product ON PC.model = Product .model
UNION
SELECT Product .type, Laptop.model, price
FROM Laptop INNER JOIN
    Product ON Laptop.model = Product .model
ORDER BY price DESC;

typemodelprice
Laptop17501200.0
Laptop17521150.0
Laptop12981050.0
PC1233980.0
Laptop1321970.0
PC1233950.0
PC1121850.0
Laptop1298700.0
PC1232600.0
PC1233600.0
PC1232400.0
PC1232350.0
PC1260350.0

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

В стандарте языка SQL имеются предложения оператора SELECT для выполнения операций пересечения и разности запросов. Этими предложениями являются INTERSECT(пересечение) и EXCEPT (разность), которые работают аналогично предложению UNION. В результирующий набор попадают только те строки, которые присутствуют в обоих запросах (INTERSECT) или только те строки первого запроса, которые отсутствуют во втором (EXCEPT).
Однако многие СУБД не поддерживают эти предложения в операторе SELECT. Это справедливо и для MS SQL Server. Поэтому для выполнения операций пересечения и разности могут быть использованы другие средства. Здесь уместно заметить, что один и тот же результат можно получить с помощью различных формулировок оператора SELECT. В случае пересечения и разности можно воспользоваться предикатом существования EXISTS.

Предикат EXISTS

EXISTS::=
        [NOT] EXISTS (<табличный подзапрос>)
Предикат EXISTS принимает значение TRUE, если подзапрос возвращает любое количество строк, иначе его значение равно FALSE. Для NOT EXISTS все наоборот. Этот предикат никогда не принимает значение UNKNOWN.
Обычно (как и в нашем случае) предикат EXISTS используется в зависимых подзапросах. Этот вид подзапроса имеет внешнюю ссылку, связанную со значением в основном запросе. Результат подзапроса может зависеть от этого значения и должен оцениваться отдельно для каждой строки запроса, в котором содержится данный подзапрос. Поэтому предикат EXISTS может иметь разные значения для каждой строки основного запроса.
Пример на пересечение. Найти тех производителей ПК-блокнотов, которые производят также и принтеры:
SELECT DISTINCT maker
FROM Product AS Lap_product
WHERE type = 'Laptop' AND EXISTS
       (SELECT maker
       FROM Product
       WHERE type = 'Printer' AND maker = Lap_product.maker);

В подзапросе выбираются производители принтеров и сравниваются с производителем, значение которого передается из основного запроса. В основном же запросе отбираются производители ПК-блокнотов. Таким образом, для каждого производителя ПК-блокнотов проверяется, возвращает ли подзапрос строки (т.е. этот производитель также производит принтеры). Поскольку два условия в предложении WHERE должны выполняться одновременно (AND), то в результирующий набор попадут нужные строки. DISTINCT используется для того, чтобы каждый производитель присутствовал в выходных данных только один раз. В результате получим:

maker
A
Пример на разность. Найти тех производителей ПК-блокнотов, которые не производят принтеров:
SELECT DISTINCT maker
FROM Product AS Lap_product
WHERE type = 'Laptop' AND NOT EXISTS
       (SELECT maker
       FROM Product
       WHERE type = 'Printer' AND maker = Lap_product.maker);

В этом случае достаточно заменить в предыдущем примере EXIST на NOT EXIST. Т.е. выходные данные составят только те уникальные строки основного запроса, для которых подзапрос не возвращает ни одной строки. В итоге получим:

maker
B
C
Рекомендуемые упражнения: 729353641454849.