Справка по SQL - W Cat
Шрифт:
Интервал:
Закладка:
1260
350.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;
type
model
price
Laptop
1750
1200.0
Laptop
1752
1150.0
Laptop
1298
1050.0
PC
1233
980.0
Laptop
1321
970.0
PC
1233
950.0
PC
1121
850.0
Laptop
1298
700.0
PC
1232
600.0
PC
1233
600.0
PC
1232
400.0
PC
1232
350.0
PC
1260
350.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
Использование ключевых слов SOME | ANY и ALL с предикатами сравнения
<оператор сравнения SOME|ANY () SOME и ANY являются синонимами, т.е. может использоваться любое из них. Результатом подзапроса является один столбец величин. Если для какого-нибудь значения V, получаемого из подзапроса, результат операции " V " равняется TRUE, то предикат ANY также равняется TRUE.
ALL ()
исполняется так же, как и ANY, но для всех значений, получаемых из подзапроса, проверка должна удовлетворять результату TRUE для предиката " V ".
Пример. Найти поставщиков компьютеров, моделей которых нет в продаже (т.е. отсутствуют в таблице PC):
SELECT DISTINCT maker
FROM Product
WHERE type = 'PC' AND NOT model = ANY
(SELECT model
FROM PC);
Оказалось, что только у поставщика Е есть модели отсутствующие в продаже:
maker
E
Рассмотрим подробно этот пример. Предикат
model = ANY (SELECT model FROM PC);
вернет значение TRUE, если модель, определяемая полем model основного запроса, найдется в списке моделей таблицы PC (возвращаемом подзапросом). Поскольку предикат используется в запросе с отрицанием NOT, то значение TRUE будет получено, если модели не окажется в списке. Этот предикат проверяется для каждой записи основного запроса, которыми являются все модели ПК (предикат type = 'PC') из таблицы Product. Результирующий набор состоит из одного столбца - имени производителя. Чтобы один производитель не выводился несколько раз (что может случиться, если он производит несколько моделей, отсутствующих в таблице PC), используется служебное слово DISTINCT.
Пример. Найти модели и цены ПК-блокнотов, стоимость которых превышает стоимость любого ПК:
SELECT DISTINCT model, price
FROM Laptop
WHERE price ALL
(SELECT price
FROM PC);
model
price
1298
1050.0
1750
1200.0
1752
1150.0
Приведем формальные правила оценки предикатов, использующих параметры ANY|SOME и ALL:
* Если определен параметр ALL или SOME и все результаты сравнения значения выражения и каждого значения, полученного из подзапроса, являются TRUE, истинностное значение равно TRUE.
* Если результат выполнения подзапроса не содержит строк и определен параметр ALL, результат равен TRUE. Если же определен параметр SOME, результат равен FALSE.
* Если определен параметр ALL и результат сравнения значения выражения хотя бы с одним значением, полученным из подзапроса, является FALSE, истинностное значение равно FALSE.
* Если определен параметр SOME и хотя бы один результат сравнения значения выражения и значения, полученного из подзапроса, является TRUE, истинностное значение равно TRUE.
* Если определен параметр SOME и каждое сравнение значения выражения и значений, полученных из подзапроса, равно FALSE, истинностное значение тоже равно FALSE.
* В любом другом случае результат будет равен UNKNOWN.
Еще о подзапросах
Заметим, что в общем случае запрос возвращает множество значений. Поэтому использование подзапроса в предложении WHERE без операторов EXISTS, IN, ALL и ANY, которые дают булево значение, может привести к ошибке времени выполнения запроса.
Пример. Найти модели и цены ПК, стоимость которых превышает минимальную стоимость ПК-блокнотов:
SELECT DISTINCT model, price
FROM PC
WHERE price
(SELECT MIN(price)
FROM Laptop);
Этот запрос вполне корректен, т.к. скалярное значение price сравнивается с подзапросом, который возвращает единственное значение. В результате получим три модели ПК:
model
price
1121
850.0
1233
950.0
1233
980.0
Однако, если в ответ на вопрос "найти модели и цены ПК, стоимость которых совпадает со стоимостью ПК-блокнота" написать следующий запрос:
SELECT DISTINCT model, price
FROM PC
WHERE price =
(SELECT price
FROM Laptop);
то при выполнении последнего мы можем получить такое сообщение об ошибке:
