group by tl.type

having max(tl.advance) in

(select 2 * avg(t2.advance) from titles t2

where tl.type = t2.type)

Результат:

type

mod cook

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

Подзапросы, начинающиеся с операторов сравнения и включающие ключевые слова ANY или ALL

в другом виде подзапросов, которые не возвращают или возвращают несколько строк, используется оператор сравнения, модифицированный ключевыми словами ANY или ALL. Подзапросы, начинающиеся с модифицированного оператора сравнения, имеют общую форму следующего вида:

Начало операторов SELECT, INSERT, UPDATE, DELETE или подзапроса WHERE выражение оператор сравнения [ANY ALL] (подзапрос) [Конец операторов SELECT, INSERT, UPDATE, DELETE или подзапроса]

Что такое ALL и ANY. Если в качестве примера воспользоваться оператором сравнения > , то > ALL означает больше, чем каждое значение (другими словами, больше, чем наибольшее значение ). Таким образом, > ALL (1, 2, 3) означает больше, чем 3 . > ANY означает больше, чем, по крайней мере, одно значение (другими словами, больше, чем наименьшее значение ). Таким образом, > ANY (1, 2, 3) означает больше, чем 1 . Рис. 8.4 иллюстрирует различия между ключевыми словами ANY и ALL.

Ключевые слова ALL и ANY иногда доставляют пользователям немало хлопот, поскольку компьютеры не терпят неоднозначности, которой эти слова отличаются в английском языке. (Действительно, слову all посвящена целая страница убористого текста в Новом большом англо-русском словаре, а слову any - примерно треть страницы. - Прим. перев.)

Рис. 8.4. Сравнение ключевых слов ANY и ALL

Подзапросы с ключевым словом ALL. Вы могли бы, например, задать следующий вопрос: Аванс за какие книги превышает аванс за любую книгу, опубликованную издательством New Age Books? Этот вопрос можно было бы перефразировать, чтобы прояснить его перевод на язык SQL: Аванс за какие книги превышает наибольший аванс, выплаченный издательством New Age Books? Ключевое слово ALL (но не ключевое слово ANY!) - именно то, что требуется в данном случае:

SQL:

select title

from titles

where advance > all

(select advance

from publishers, titles

where titles.pub id = publishers.pub id and pub name = New Age Books)

Результат: title

The Gourmet Microwave

Для каждого названия внутренний запрос находит список значений авансов, выплаченных издательством New Age Books. Внешний запрос находит наибольшее значение в списке и определяет, не выплачен ли за книгу, которая рассматривается в данный момент, еще больший аванс.

Если внутренний подзапрос, начинающийся с ALL и оператора сравнения, возвращает в качестве одного из своих значений NULL, считается, что запрос в целом завершился неудачно. Например, значения авансов за книги издательства Algodata Infosystems выглядят так:

SQL:

select advance from publishers, titles where titles.pub id = publishers,pub id and pub name = Algodata Infosystems

Результат: advance

8000 NULL 5000 5000 5000 7000

Если вас интересуют авансы, превышающие любые авансы, выплаченные издательством Algodata Infosystems, вы не получите никаких результатов, поскольку невозможно сказать, что больше значения NULL.

SQL:

select title

from titles

where advance > all

(select advance from publishers, titles

where titles.pub id = publishers.pub id and pub name = Algodata Infosystems)

Результат: title

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

SQL:

select title

from titles where advance > all (select advance

from publishers, titles

where titles.pub id = publishers.pub id

and pub name = Demo Books)

Подзапросы с ключевым словом ANY. Запрос с ключевым словом ANY находит значения, превышающие некоторое значение из подзапроса. Приведенный ниже запрос находит книги, за которые был выплачен аванс, превышающий минимальное значение аванса ($5000), выплаченного издательством Algodata Infosystems.

SQL:

select title, advance

from titles

where advance > all

(select advance from titles, publishers

where titles.pub id = publishers.pub id and pub name = Algodata Infosystems)

Результат:

title advance

Secrets of Silicon Valley 8000

Sushi, Anyone? 8000

But Is It User Friendly? 7000

You Can Combat Computer Stress! 10125

Life Without Fear 6000

The Gourmet Microwave 15000 Onions, Leeks, and Garlic: Cooking Secrets of the Mediterranean 7000 Computer Phobic and Non-Phobic Individuals: Behavior Variations 7000

Для каждой книги внутренний запрос находит список величин аванса, выплаченных издательством Algodata Infosystems. Внешний запрос просматривает все значения в этом списке и определяет, не выплачен ли за книгу, которая рассматривается в данный момент, аванс больший, чем любая из этих величин.

Если подзапрос не возвращает никаких значений, то считается, что запрос в целом завершился неудачно.

Сравнение ключевых слов IN, ANY и ALL. Оператор = ANY полностью эквивалентен оператору IN. Если, например, требуется найти авторов, которые проживают в том же городе, в котором расположено какое-то определенное издательство, можно воспользоваться либо IN, либо = ANY:

SQL:

select au lname, au fname, city from authors where city in

(select city from publishers) order by city

SQL:

select au lname, au fname, city from authors