Более удачный пример подзапроса

Возможные прикладные программы подзапросов могут становиться многократно вкладываемыми.

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

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

SELECT *
FROM Salespeople first
WHERE EXISTS (SELECT *
FROM Customers second
WHERE first.snum = second.snum AND
1 < (SELECT COUNT (*)
FROM Orders
WHERE Orders.cnum = second.cnum));

Вывод для этого запроса показывается в Рисунке 12.5.

============== SQL Execution Log =============
| FROM Salespeople first |
| WHERE EXISTS |
| (SELECT * |
| FROM Customers second |
| WHERE first.snum = second.snum |
| AND 1 < |
| (SELECT CONT (*) |
| FROM Orders |
| WHERE Orders.cnum = second.cnum)); |
|===============================================|
| cnum cname city comm |
| ----- -------- ---- -------- |
| 1001 Peel London 0.17 |
| 1002 Serres San Jose 0.13 |
| 1007 Rifkin Barselona 0.15 |
===============================================

Рисунок 12.5. Использование EXISTS с комплексным подзапросом.

Мы могли бы разобрать вышеупомянутый запрос примерно так:

Берем каждую строку таблицы Продавцов как строку-кандидат (внешний запрос) и выполняем подзапросы. Для каждой строки-кандидата из внешнего запроса берем в соответствие каждую строку из таблицы Заказчиков (средний запрос). Если текущая строка заказчиков не совпадает с текущей строкой продавца (т.е. если first.snum <> second.snum), предикат среднего запроса неправилен. Всякий раз, когда мы находим заказчика в среднем запросе, который совпадает с продавцом во внешнем запросе, мы должны рассматривать сам внутренний запрос, чтобы определить, будет ли наш средний предикат запроса верен. Внутренний запрос считает число Заказов текущего заказчика (из среднего запроса). Если это число больше 1, предикат среднего запроса верен, и строки выбираются. Это делает EXISTS предикат внешнего запроса верным для текущей строки продавца, и означает, что, по крайней мере, один из текущих заказчиков продавца имеет более одного заказа.

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

Кроме того, этот запрос, даже если он кажется удобным, довольно извилистый способ извлечения информации и делает много работы. Он связывает три разных таблицы, чтобы дать вам эту информацию, а если таблиц больше, чем здесь указано, будет трудно получить ее напрямую (хотя это не единственный способ, и не обязательно лучший способ в SQL). Возможно, вам нужно получать эту информацию регулярно — если, например, вы назначаете премию в конце недели продавцам, получившим более одного Заказа от одного заказчика. В этом случае, нужно ввести команду и сохранить ее для повторного использования, по мере того как данные будут меняться (лучше всего сделать это с помощью представления, которое мы будем проходить в Главе 20).

Резюме

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

Следующим шагом будет овладение тремя другими специальными операторами, которые берут подзапросы как аргументы, это — ANY, ALL, и SOME. Как вы увидите в Главе 13, это — альтернативные формулировки некоторых вещей, которые вы уже использовали, но которые в некоторых случаях могут оказаться более предпочтительными.

Работа с SQL

1. Напишите запрос, который бы использовал оператор EXISTS для извлечения всех продавцов, которые имеют заказчиков с оценкой 300.

2. Как бы вы решили предыдущую проблему, используя объединение?

3. Напишите запрос, использующий оператор EXISTS который выберет всех продавцов с заказчиками, размещенными в их городах и которые ими не обслуживаются.

4. Напишите запрос, извлекающий из таблицы Заказчиков каждого заказчика назначенного к продавцу, который в данный момент имеет, по крайней мере, еще одного заказчика (кроме заказчика которого вы выберете) с Заказами в таблице Заказов (подсказка: это может быть похоже на структуру в примере с нашим трехуровневым подзапросом).

(См. Приложение A для ответов.)