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 — это первичный ключ (о ключах см. в Главе 19) таблицы Заказчика, запрос выбирающий его, должен произвести только одно значение. Это рационально только если вы как пользователь имеете доступ к таблице Заказов, но не к таблице Заказчиков. В этом случае вы можете использовать решение, которое мы показали выше (SQL имеет механизмы, которые определяют, кто имеет привилегии, чтобы делать что-то в определенной таблице; это будет объясняться в Главе 22).

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

Предикаты с подзапросами являются необратимыми

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

<скалярная форма> <оператор> <подзапрос>, а не

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

<подзапрос> <оператор> <подзапрос>.

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

SELECT *
FROM Orders
WHERE (SELECT DISTINCT snum
FROM Orders
WHERE cnum = 2001) = snum;

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