РЕЗЮМЕ
Теперь Вы понимаете возможности объединения и можете использовать их для ограничения связей с таблицей, между различными таблицами, или в обоих случаях. Вы могли видеть некоторые возможности объединения при использовании его способностей. Вы теперь познакомились с терминами порядковые переменные, корреляционные переменные и предложения (эта терминология будет меняться от изделия к изделию, так что мы предлагаем Вам познакомится со всеми тремя терминами). Кроме того Вы поняли, немного, как в действительности работают запросы.
Следующим шагом после комбинации многочисленых таблиц или многочисленых копий одной таблицы в запросе, будет комбинация многочисленных запросов, где один запрос будет производить вывод который будет затем управлять работой другого запроса. Это другое мощное средство SQL, о котором мы расскажем в Главе 10 и более тщательно в последующих главах.
РАБОТА С SQL
* Напишите запрос который бы вывел все пары продавцов живущих в одном и том же городе. Исключите комбинации продавцов с ними же, а также дубликаты строк выводимых в обратным порядке.
* Напишите запрос который вывел бы все пары порядков по данным заказчикам, именам этих заказчиков, и исключал дубликаты из вывода, как в предыдущем вопросе.
* Напишите запрос который вывел бы имена(cname) и города(city) всех заказчиков с такой же оценкой(rating) как у Hoffmanа. Напишите запрос использующий поле cnum Hoffmanа а не его оценку, так чтобы оно могло быть использовано если его оценка вдруг изменится.
Глава 10. ВСТАВКА ОДНОГО ЗАПРОСА ВНУТРЬ ДРУГОГО
В КОНЕЦ ГЛАВЫ 9, МЫ ГОВОРИЛИ ЧТО ЗАПРОСЫ могут управлять другими запросами. В этой главе, вы узнаете как это делается (большей частью), помещая запрос внутрь предиката другого запроса, и используя вывод внутреннего запроса в верном или неверном условии предиката. Вы сможете выяснить какие виды операторов могут использовать подзапросы и посмотреть как подзапросы работают со средствами SQL, такими как DISTINCT, с составными функциями и выводимыми выражения. Вы узнаете как использовать подзапросы с предложением HAVING и получите некоторые наставления как правильно использовать подзапросы.
КАК РАБОТАЕТ ПОДЗАПРОС?
С помощью 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", можно получить несколько различных значений. Это может сделать уравнение в предикате основного запроса невозможным для оценки верности или неверности, и команда выдаст ошибку.
При использовании подзапросов в предикатах основанных на реляционных операторах (уравнениях или неравенствах, как объяснено в Главе 4 ), вы должны убедиться что использовали подзапрос который будет выдавать одну и только одну строку вывода. Если вы используете подзапрос который не выводит никаких значений вообще, команда не потерпит неудачи; но основной запрос не выведет никаких значений. Подзапросы которые не производят никакого вывода (или нулевой вывод) вынуждают рассматривать предикат ни как верный ни как неверный, а как неизвестный. Однако, неизвестный предикат имеет тот же самый эффект что и неверный: никакие строки не выбираются основным запросом (смотри Главу 5 для подробной информации о неизвестном предикате ).
Это плохая стратегия, чтобы делать что-нибудь подобное следующему:
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.) Пожалуйста учтите, что методика используемая в предшествующем примере применима только когда вы знаете, что два различных поля в таблице должны всегда совпадать, как в нашем случае. Эта ситуация не является типичной в реляционных базах данных, она являеться исключением из правил.