< подзапрос > < оператор > < скалярное выражение > или,
< подзапрос > < оператор > < подзапрос >.
Другими словами, вы не должны записывать предыдущий пример так:
SELECT *
FROM Orders
WHERE (SELECT DISTINCT snum
FROM Orders
WHERE cnum=2001 )
=snum;
В строгой ANSI реализации, это приведет к неудаче, хотя некоторые программы и позволяют делать такие вещи. ANSI также предохраняет вас от появления обеих значений при сравнении, которые нужно вывести с помощью подзапроса.
Один тип функций, который автоматически может производить одиночное значение для любого числа строк, конечно же, - агрегатная функция.
Любой запрос использующий одиночную функцию агрегата без предложения GROUP BY будет выбирать одиночное значение для использования в основном предикате. Например, вы хотите увидеть все порядки имеющие сумму приобретений выше средней на 4-е Октября (вывод показан в Таблице 10.3 ):
SELECT *
FROM Orders
WHERE amt >
( SELECT AVG (amt)
FROM Orders
WHERE odate=10/04/1990 );
SELECT * FROM Orders WHERE amt >
(SELECT AVG (amt) FROM Orders
WHERE odate=01/04/1990 );
|
onum |
amt |
odate |
cnum |
snum |
|
3002 |
1900.10 |
10/03/1990 |
2007 |
1004 |
|
3005 |
2345.45 |
10/03/1990 |
2003 |
1002 |
|
3006 |
1098.19 |
10/03/1990 |
2008 |
1007 |
|
3009 |
1713.23 |
10/04/1990 |
2002 |
1003 |
|
3008 |
4723.00 |
10/05/1990 |
2006 |
1001 |
|
3010 |
1309.95 |
10/06/1990 |
2004 |
1002 |
|
3011 |
9891.88 |
10/06/1990 |
2006 |
1001 |
Таблица 10.3: Выбор всех сумм со значением выше средней на 10/04/1990
Средняя сумма приобретений на 4 Октября - 1788.98 (1713.23 + 75.75) делится пополам, что в целом равняется =894.49. Все строки со значением в поле amt выше этого - являются выбраными.
Имейте ввиду что сгруппированные агрегатные функции, которые являются агрегатными функциями определенными в терминах предложения GROUP BY, могут производить многочисленые значения. Они, следовательно, не позволительны в подзапросах такого характера. Даже если GROUP BY и HAVING используются таким способом, что только одна группа выводится с помощью подзапроса, команда будет отклонена в принципе. Вы должны использовать одиночную агрегатную функцию с предложением WHERE что устранит нежелательные группы. Например, следующий запрос который должен найти среднее значение комиссионных продавца в Лондоне -
SELECT AVG (comm)
FROM Salespeople
GROUP BY city
HAVlNG city="London";
не может использоваться в подзапросе! Во всяком случае это не лучший способ формировать запрос. Другим способом может быть -
SELECT AVG (comm)
FROM Salespeople
WHERE city="London";
Вы можете использовать подзапросы которые производят любое число строк если вы используете специальный оператор IN (операторы BETWEEN, LIKE, и IS NULL не могут использоваться с подзапросами). Как вы помните, IN определяет набор значений, одно из которых должно совпадать с другим термином уравнения предиката в порядке, чтобы предикат был верным. Когда вы используете IN с подзапросом, SQL просто формирует этот набор из вывода подзапроса. Мы можем, следовательно, использовать IN чтобы выполнить такой же подзапрос который не будет работать с реляционным оператором, и найти все атрибуты таблицы Порядков для продавца в Лондоне (вывод показывается в Таблице 10.4 ):
SELECT *
FROM Orders
WHERE snum IN
( SELECT snum
FROM Salespeople
WHERE city="LONDON" );
SELECT * FROM Orders WHERE snum IN
(SELECT snum FROM Salespeople WHERE city='London');
|
onum |
amt |
odate |
cnum |
snum |
|
3003 |
767.19 |
10/03/1990 |
2001 |
1001 |
|
3002 |
1900.10 |
10/03/1990 |
2007 |
1004 |
|
3006 |
1098.19 |
10/03/1990 |
2008 |
1007 |
|
3008 |
4723.00 |
10/05/1990 |
2006 |
1001 |
|
3011 |
9891.88 |
10/06/1990 |
2006 |
1001 |
Таблица 10. 4: Использование подзапроса с IN
В ситуации подобно этой, подзапрос - более прост для пользователя чтобы понимать его и более прост для компьютера чтобы его выполнить, чем если бы Вы использовали обьединение:
SELECT onum, amt, odate, cnum, Orders.snum
FROM Orders, Salespeople
WHERE Orders.snum=Salespeople.snum
AND Salespeople.city="London";
Хотя это и произведет тот же самый вывод что и в примере с подзапросом, SQL должен будет просмотреть каждую возможную комбинацию строк из двух таблиц и проверить их снова по составному предикату. Проще и эффективнее извлекать из таблицы Продавцов значения поля snum где city="London", и затем искать эти значения в таблице Порядков, как это делается в варианте с подзапросом. Внутренний запрос дает нам snums=1001 и snum=1004. Внешний запрос, затем, дает нам строки из таблицы Поряд ков где эти поля snum найдены.
Строго говоря, быстрее или нет работает вариант подзапроса, практически зависит от реализации - в какой программе вы это используете.
Эта часть вашей программы называемой - оптимизатор, пытается найти наиболее эффективный способ выполнения ваших запросов.
Хороший оптимизатор во всяком случае преобразует вариант обьединения в подзапрос, но нет достаточно простого способа для вас чтобы выяснить выполнено это или нет. Лучше сохранить ваши запросы в памяти чем полагаться полностью на оптимизатор.
Конечно вы можете также использовать оператор IN, даже когда вы уверены что подзапрос произведет одиночное значение. В любой ситуации где вы можете использовать реляционный оператор сравнения (=), вы можете использовать IN. В отличие от реляционных операторов, IN не может заставить команду потерпеть неудачу если больше чем одно значение выбрано подзапросом. Это может быть или преимуществом или недостатком. Вы не увидите непосредственно вывода из подзапросов; если вы полагаете что подзапрос собирается произвести только одно значение, а он производит различные. Вы не сможете объяснить различия в выводе основного запроса. Например, рассмотрим команду, которая похожа на предыдущую:
SELECT onum, amt, odate
FROM Orders