Коррелированные подзапросы, перед которыми стоит ключевое слово IN

Выше, в разделе "Подзапросы, перед которыми стоит ключевое слово IN", рассказывалось, каким образом некоррелированный подзапрос можно использовать вместе с предикатом IN. А чтобы увидеть, каким образом этот предикат может использоваться, наоборот, коррелированным подзапросом, задайте тот же самый вопрос, что и в случае с предикатом EXISTS. Итак, какие фамилии и телефонные номера у представителей для контакта во всех организациях-покупателях продукции Zetec в Калифорнии? Ответ можно получить с помощью коррелированного подзапроса с IN:

SELECT *

FROM CONTACT

WHERE 'CA' IN

(SELECT CustState

FROM CUSTOMER

WHERE CONTACT.CustID = CUSTOMER.CustID);

Оператор выполняется с каждой записью таблицы CONTACT. Если значение столбца CustID этой записи совпадает с соответствующим значением столбца таблицы CUSTOMER, то значение CUSTOMER.CustState сравнивается со значением 'СА. Результатом выполнения подзапроса является список, в котором содержится не более одного элемента. Ваш этот единственный элемент представляет собой 'СА', то выполняется условие предложения WHERE из замыкающего оператора и строка добавляется в выводимую запросом таблицу.

Коррелированные подзапросы, перед которыми стоят операторы сравнения

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

Компания Zetec выплачивает каждому своему продавцу премию, которая зависит от общей суммы, вырученной им от продаж за месяц. Чем выше эта сумма, тем выше процент премии. Список этих процентов хранится в таблице BONUSRATE (ставка премии) со столбцами MIN_AMOUNT (нижняя граница), МАХ_AMOUNT (верхняя граница) (процент премии).

MIN_AMOUNT	MAX_AMOUNT	BONUS_PCT
----------------	----------------	--------------
0.00	24999.99	0
25000.00	49999.99	0.001
50000.00	99999.99	0.002
100000.00	249999.99	0.003
250000.00	499999.99	0.004
500000.00	749999.99	0.005
750000.00	999999.99	0.006

Если у продавца ежемесячная сумма продаж составляет 100000-249999.99 долл., то он получает премию в размере 0.3% от этой суммы.

Продажи записываются в главную таблицу сделок TRANSMASTER.

TRANSMASTER

-----------------

Столбец Тип Ограничения --------- ---- ---------------- TRANSID (идентификатор сделки) INTEGER PRIMARY KEY CUSTID (идентификатор покупателя) INTEGER FOREIGN KEY EMPID (идентификатор сотрудника) INTEGER FOREIGN KEY TRANSDATE (дата сделки) DATE   NET_AMOUNT (облагаемая налогом сумма) NUMERIC   FREIGHT (стоимость перевозки) NUMERIC   TAX (налог) NUMERIC   INVOICETOTAL (итоговая сумма счета-фактуры) NUMERIC  

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

SELECT BONUS_PCTFROM BONUSRATEWHERE MIN_AMOUNT <=(SELECT SUM (NET_AMOUNT)FROM TRANSMASTERWHERE EMPID = 133)AND MAX_AMOUNT >=(SELECT SUM (NET_AMOUNT)FROM TRANSMASTERWHERE EMPID =133);

Этот запрос интересен тем, что в нем содержатся два подзапроса, для которых приходится использовать логическую связку AND. В подзапросах применяется итоговый оператор SUM, и он возвращает единственное значение – общую сумму продаж за месяц для сотрудника с идентификационным номером 133. Затем это значение сравнивается со значениями в столбцах MIN_AMOUNT и MAX_AMOUNT из таблицы BONUSRATE, и в результате получается процент премии для этого сотрудника.

Если идентификатор продавца, хранящийся в столбце EMPID, вам не известен, но известна фамилия, то такой же ответ можно получить, используя более сложный запрос:

SELECT BONUS_PCTFROM BONUSRATEWHERE MIN_AMOUNT <=(SELECT SUM (NET_AMOUNT)FROM TRANSMASTER WHERE EMPID =(SELECT EMPIDFROM EMPLOYEEWHERE EMPLNAME = 'Coffin'))AND MAX_AMOUNT >=(SELECT SUM (NET_AMOUNT)FROM TRANSMASTER WHERE EMPID =(SELECT EMPIDFROM EMPLOYEEWHERE EMPLNAME = 'Coffin'));

В этом примере, чтобы получить процент премии для сотрудника по фамилии Коффин, используются подзапросы, вложенные в другие подзапросы, а те, в свою очередь, вложены в замыкающий запрос. Эта структура работает только тогда, когда вам наверняка известно, что в компании работает один-единственный сотрудник с этой фамилией. А если вы знаете, что имеются несколько сотрудников с фамилией Коффин? Тогда в предложение WHERE из подзапроса самого нижнего уровня можно добавлять все новые и новые условия, пока не появится уверенность, что будет выбрана единственная строка таблицы EMPLOYEE.