КАК РАБОТАЕТ ПОДЗАПРОС?

С помощью SQL вы можете вкладывать запросы внутрь друга друга. Обычно, внутренний запрос генерирует значение которое проверяется в предикате внешнего запроса, определяющего верно оно или нет. Например, предположим что мы знаем им продавца: Motika, но не знаем значение его пол snum, и хотим извлечь все порядки из таблицы Порядков. Имеется один способ чтобы сделать это( вывод показывается в Рисунке 10.1 ):

SELECT * 
   FROM Orders 
   WHERE snum = 
 ( SELECT snum 
  FROM Salespeople 
  WHERE sname = 'Motika'); 

Чтобы оценить внешний( основной ) запрос, SQL сначала должен оценить внутренний запрос ( или подзапрос ) внутри предложения WHERE. Он делает это так как и должен делать запрос имеющий единственную цель - отыскать через таблицу Продавцов все строки, где поле sname равно значению Motika, и затем извлечь значения пол snum этих строк.

Единственной найденной строкой естественно будет snum = 1004. Однако SQL, не просто выдает это значение, а помещает его в предикат основного запроса вместо самого подзапроса, так чтобы предиката прочитал что

WHERE snum = 1004 
 
 
===============  SQL Execution Log ============== 
|                                                 | 
| SELECT *                                        | 
| FROM  Orders                                    | 
| WHERE snum =                                    | 
| (SELECT snum                                    | 
| FROM Salespeople                                | 
| WHERE sname = 'Motika');                        | 
|=================================================| 
|   onum       amt      odate      cnum     snum  | 
|  -----     -------  ----------  -----    -----  | 
|   3002     1900.10  10/03/1990   2007     1004  | 
|                                                 | 
 =================================================

Рисунок 10.1: Использование подзапроса

Основной запрос затем выполняется как обычно с вышеупомянутыми результатами. Конечно же, подзапрос должен выбрать один и только один столбец, а тип данных этого столбца должен совпадать с тем значением с которым он будет сравниваться в предикате. Часто, как показано выше, выбранное поле и его значение будут иметь одинаковые имена( в этом случае, snum ), но это необязательно.

Конечно, если бы мы уже знали номер продавца Motika, мы могли бы просто напечатать

WHERE snum = 1004 

и выполнять далее с подзапросом в целом, но это было бы не так универсально. Это будет продолжать работать даже если номер Motika изменился, а, с помощью простого изменения имени в подзапросе, вы можете использовать его для чего угодно.

ЗНАЧЕНИЯ, КОТОРЫЕ МОГУТ ВЫДАВАТЬ ПОДЗАПРОСЫ

Скорее всего было бы удобнее, чтобы наш подзапрос в предыдущем примере возвращал одно и только одно значение.

Имея выбранным поле snum " WHERE city = "London" вместо "WHERE sname = 'Motika", можно получить несколько различных значений. Это может сделать уравнение в предикате основного запроса невозможным для оценки верности или неверности, и команда выдаст ошибку.

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 предотвращает это. Если наш подзапрос возвратит более одного значения, это будет указывать на ошибку в наших данных - хороша вещь для знающих об этом.

ПРЕДИКАТЫ С ПОДЗАПРОСАМИ ЯВЛЯЮТСЯ НЕОБРАТИМЫМИ

Вы должны обратить внимание что предикаты включающие подзапросы, используют выражение

< скалярная форма > < оператор > < подзапрос >, а, не < подзапрос > < оператор > < скалярное выражение > или, < подзапрос > < оператор > < подзапрос >.

Другими словами, вы не должны записывать предыдущий пример так:

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 найдены. Строго говор, быстрее или нет работает вариант подзапроса, практически зависит от реализации - в какой программе вы это используете. Эта часть вашей программы называемой - оптимизатор, пытается найти наиболее эффективный способ выполнения ваших запросов.

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

Конечно вы можете также использовать оператор IN, даже когда вы уверены что подзапрос произведет одиночное значение. В любой ситуации где вы можете использовать реляционный оператор сравнения (=), вы можете использовать IN. В отличие от реляционных операторов, IN не может заставить команду потерпеть неудачу если больше чем одно значение выбрано подзапросом. Это может быть или преимуществом или недостатком. Вы не увидите непосредственно вывода из подзапросов; если вы полагаете что подзапрос собирается произвести только одно значение, а он производит различные. Вы не сможете объяснить различи в выводе основного запроса. Например, рассмотрим команду, которая похожа на предыдущую:

SELECT onum, amt, odate 
    FROM Orders 
    WHERE snum = 
   ( SELECT  snum 
   FROM Orders 
   WHERE cnum = 2001 ); 

Вы можете устранить потребность в DISTINCT используя IN вместо (=), подобно этому:

SELECT onum, amt, odate 
    FROM Orders 
    WHERE snum IN 
   ( SELECT snum 
   FROM Orders 
   WHERE cnum = 2001 ); 

Что случится если есть ошибка и один из порядков был аккредитован к различным продавцам? Версия использующая IN будет давать вам все порядки для обоих продавцов. Нет никакого очевидного способа наблюдения за ошибкой, и поэтому сгенерированные отчеты или решения сделанные на основе этого запроса не будут содержать ошибки. Вариант использующий ( = ) , просто потерпит неудачу.

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

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

SELECT comm 
FROM Salespeople 
WHERE snum IN 
  ( SELECT snum 
 FROM Customers 
 WHERE city = "London" ); 

Выводимыми для этого запроса, показанного в Рисунке 10.5, являются значения комиссионных продавца Peel ( snum = 1001 ), который имеет обоих заказчиков в Лондоне. Это - только для данного случая. Нет никакой причины чтобы некоторые заказчики в Лондоне не могли быть назначенными к кому-то еще. Следовательно, IN - это наиболее логична форма чтобы использовать ее в запросе.

===============  SQL Execution Log ============== 
    |                                                 | 
    | SELECT comm                                     | 
    | FROM  Salespeople                               | 
    | WHERE snum IN                                   | 
    | (SELECT snum                                    | 
    | FROM Customers                                  | 
    | WHERE city = 'London');                         | 
    | =============================================== | 
    |    comm                                         | 
    |  -------                                        | 
    |    0.12                                         | 
    |                                                 | 
    |                                                 | 
 ================================================

Рисунок 10.5 Использование IN с подзапросом для вывода одного значения

Между прочим, префикс таблицы для пол city необязателен в предыду- щем примере, несмотря на возможную неоднозначность между полями city таблицы Заказчика и таблицы Продавцов. SQL всегда ищет первое поле в таблице обозначенной в предложении FROM текущего подзапроса. Если поле с данным именем там не найдено, проверяются внешние запросы. В вышеупомянутом примере, "city" в предложении WHERE означает что имеется ссылка к Customer.city( поле city таблицы Заказчиков). Так как таблица Заказчиков указана в предложении FROM текущего запроса, SQL предполагает что это - правильно. Это предположение может быть отменено полным именем таблицы или префиксом псевдонима, о которых мы поговорим позже когда будем говорить об соотнесенных подзапросах. Если возможен беспорядок, конечно же, лучше всего использовать префиксы.

ПОДЗАПРОСЫ ВЫБИРАЮТ ОДИНОЧНЫЕ СТОЛБЦЫ

ПОДЗАПРОСЫ В ПРЕДЛОЖЕНИИ HAVING

Вы можете также использовать подзапросы внутри предложения HAVING. Эти подзапросы могут использовать свои собственные агрегатные функции если они не производят многочисленных значений или использовать GROUP BY или HAVING. Следующий запрос является этому примером ( вывод показывается в Рисунке 10.7 ):

SELECT rating, COUNT ( DISTINCT cnum ) 
  FROM Customers 
  GROUP BY rating 
  HAVING rating > 
  ( SELECT AVG (rating) 
  FROM Customers 
  WHERE city = " San Jose'; 
 
 
 ===============  SQL Execution Log ============ 
|                                               | 
| SELECT rating,count (DISTINCT cnum)           | 
| FROM  Customers                               | 
| GROUP BY rating                               | 
| HAVING rating >                               | 
| (SELECT AVG (rating)snum + 1000               | 
| FROM Custimers                                | 
| WHERE city = 'San Jose'                       | 
|============================================   | 
|  rating                                       | 
| --------    --------                          | 
|   200             2                           | 
 ================================================

Рисунок 10.7: Нахождение заказчиков с оценкой выше среднего в San Jose

Эта команда подсчитывает заказчиков с оценками выше среднего в San Jose. Так как имеются другие оценки отличные от 300, они должны быть выведены с числом номеров заказчиков которые имели эту оценку.

РЕЗЮМЕ

Теперь вы используете запросы в иерархической манере. Вы видели, что использование результата одного запроса для управления другим, расширяет возможности позволяющие выполнить большее количество функций. Вы теперь понимаете как использовать подзапросы с реляционными опера- торами также как и со специальным оператором IN, или в предложении WHERE или в предложении HAVING внешнего запроса.

РАБОТА С SQL

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