Мартин Грубер - Понимание SQL
SELECT *
FROM Orders
WHERE snum=
( SELECT snum
FROM Salespeople
WHERE city=Barcelona );
Поскольку мы имеем только одного продавца в Barcelona - Rifkin, то подзапрос будет выбирать одиночное значение snum и следовательно будет принят. Но это - только в данном случае. Большинство SQL баз данных имеют многочисленых пользователей, и если другой пользователь добавит нового продавца из Barcelona в таблицу, подзапрос выберет два значения, и ваша команда потерпит неудачу.
DISTINCT С ПОДЗАПРОСАМИ
Вы можете, в некоторых случаях, использовать DISTINCT чтобы вынудить подзапрос генерировать одиночное значение. Предположим что мы хотим найти все порядки кредитований для тех продавцов которые обслуживают Hoffmanа (cnum=2001). Имеется один способ чтобы сделать это (вывод показывается в Таблице 10.2 ):
SELECT *
FROM Orders
WHERE snum=
( SELECT DISTINCT snum
FROM Orders
WHERE cnum=2001 );
SQL Execution Log
SELECT * FROM Orders WHERE snum=
(SELECT DISTINCT snum FROM Orders Where cnum=2001);
onum
amt
odate
cnum
snum
3003
767.19
10/03/1990
2001
1001
3008
4723.00
10/05/1990
2006
1001
3011
9891.88
10/06/1990
2006
1001
Таблица 10.2: Использование DISTINCT чтобы вынудить получение одного значения из подзапроса
Подзапрос установил что значение поля snum совпало с Hoffman - 1001, и затем основной запрос выделил все порядки с этим значением snum из таблицы Порядков( не разбирая, относятся они к Hoffman или нет). Так как каждый заказчик назначен к одному и только этому продавцу, мы знаем что каждая строка в таблице Порядков с данным значением cnum должна иметь такое же значение snum. Однако так как там может быть любое число таких строк, подзапрос мог бы вывести много (хотя и идентичных ) значений snum для данного поля cnum. Аргумент DISTINCT предотвращает это. Если наш подзапрос возвратит более одного значения, это будет указывать на ошибку в наших данных - хорошая вещь для знающих об этом.
Альтернативный подход должен быть чтобы ссылаться к таблице Заказчиков а не к таблице Порядков в подзапросе. Так как поле cnum - это первичный ключ таблицы Заказчика, запрос выбирающий его должен произвести только одно значение. Это рационально только если вы как пользователь имеете доступ к таблице Порядков но не к таблице Заказчиков. В этом случае, вы можете использовать решение которое мы показали выше. (SQL имеет механизмы которые определяют - кто имеет привилегии чтобы делать что-то в определенной таблице. Это будет объясняться в Главе 22.) Пожалуйста учтите, что методика используемая в предшествующем примере применима только когда вы знаете, что два различных поля в таблице должны всегда совпадать, как в нашем случае. Эта ситуация не является типичной в реляционных базах данных, она являеться исключением из правил.
ПРЕДИКАТЫ С ПОДЗАПРОСАМИ ЯВЛЯЮТСЯ НЕОБРАТИМЫМИВы должны обратить внимание что предикаты включающие подзапросы, используют выражение
< скалярная форма > < оператор > < подзапрос >, а не
< подзапрос > < оператор > < скалярное выражение > или,
< подзапрос > < оператор > < подзапрос >.
Другими словами, вы не должны записывать предыдущий пример так:
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 );
SQL Execution Log
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Вы можете использовать подзапросы которые производят любое число строк если вы используете специальный оператор IN (операторы BETWEEN, LIKE, и IS NULL не могут использоваться с подзапросами). Как вы помните, IN определяет набор значений, одно из которых должно совпадать с другим термином уравнения предиката в порядке, чтобы предикат был верным. Когда вы используете IN с подзапросом, SQL просто формирует этот набор из вывода подзапроса. Мы можем, следовательно, использовать IN чтобы выполнить такой же подзапрос который не будет работать с реляционным оператором, и найти все атрибуты таблицы Порядков для продавца в Лондоне (вывод показывается в Таблице 10.4 ):
SELECT *
FROM Orders
WHERE snum IN
( SELECT snum
FROM Salespeople
WHERE city="LONDON" );
SQL Execution Log
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 найдены.
Строго говоря, быстрее или нет работает вариант подзапроса, практически зависит от реализации - в какой программе вы это используете.
Эта часть вашей программы называемой - оптимизатор, пытается найти наиболее эффективный способ выполнения ваших запросов.
Хороший оптимизатор во всяком случае преобразует вариант обьединения в подзапрос, но нет достаточно простого способа для вас чтобы выяснить выполнено это или нет. Лучше сохранить ваши запросы в памяти чем полагаться полностью на оптимизатор.
