Computer Phobic and Non-Phobic Individuals: Behavior Variations

Сначала внутренний запрос находит минимальную цену в таблице titles, затем внешний запрос использует это значение для выбора соответствуюших названий.

Предложения GROUP BY и HAVING должны возвращать единственное значение

Подзапросы с оператором сравнения не могут включать предложения GROUP BY и HAVING, если вы не уверены, что они возвращают единственное значение. Например, следующий запрос находит книги, имеющие цену выше, чем самая дешевая книга в категории trad cook:

SQL:

select title, type from titles where price >

(select min(price) from titles group by type having type = trad cook)

Результат: title

Secrets of Silicon Valley

The Busy Executives Database Guide

Prolonged Data Deprivation: Four Case Studies

Silicon Valley Gastronomic Treats

Sushi, Anyone?

But Is It User Friendly?

Onions, Leeks, and Garlic: Cooking Secrets of the Mediterranean

Straight Talk About Computers

Computer Phobic and Non-Phobic Individuals: Behavior Variations

type

popular comp

business

psychology

mod cook

trad cook

popular comp

trad cook

business psychology

Коррелированные подзапросы с операторами сравнения

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

SQL:

select si.sonum, sl.title id, si.qty ordered from salesdetails si where qty ordered <

(select avg(qty ordered) from salesdetails s2 where sl.title id = s2.title id) order by title id

Результат: sonum

title id lui032

qty ordered 5

5 МС3021 15

7 PS2091 3

3 PS2091 20

2 PS2091 10

Внешний запрос выбирает строки из таблицы salesdetails (т.е. из si) одна за одной. Подзапрос вычисляет среднее количество по каждому рассматриваемому заказу для выбора во внешнем запросе. Для каждого возможного значения si система выполняет подзапрос и включает данную строку в результаты, если соответствую-шее количество оказывается меньше, чем вычисленное среднее для этой таблицы.

В этом, а также в следующем запросе коррелированный подзапрос имитирует действие оператора GROUP BY. Нет необходимости выполнять группирование по типу в явном виде, поскольку с помощью самообъединения в предложении WHERE данного подзапроса определяются средние цены по каждому типу. Чтобы найти книги, цена которых выше средней для книг этого типа, надо выполнить следующий запрос:

SQL:

select tl.type, tl.title from titles tl where tl.price >

(select avg(t2.price) from titles t2 where tl.type = t2.type)

Результат:

type title

business The Busy Executives Database Guide

psychology Prolonged Data Deprivation: Four Case Studies mod cook Silicon Valley Gastronomic Treats

popular comp But Is It User Friendly?

Trad cook Onions, Leeks, and Garlic: Cooking Secrets of the

Mediterranean business Straight Talk About Computers

psychology Computer Phobic and Non-Phobic Individuals: Behavior Variations

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

ПОДЗАПРОСЫ, ВЫПОЛНЯЮЩИЕ ПРОВЕРКУ НА СУЩЕСТВОВАНИЕ

Когда тот или иной подзапрос начинается с ключевого слова EXISTS, этот подзапрос функционирует как тест на существование . Ключевое слово EXISTS в предложении WHERE выполняет проверку на существование (или несуществование) данных, которые удовлетворяют критериям соответствующего подзапроса.

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

Начало операторов SELECT, INSERT, UPDATE, DELETE или подзапроса WHERE [NOT] EXISTS (подзапрос)

[Конец операторов SELECT, INSERT, UPDATE, DELETE или подзапроса]

Если вам требуется найти названия всех издательств, которые публикуют книги по бизнесу, надо выполнить следующий запрос:

SQL:

select distinct pub name from publishers . where exists

(select * from titles where pub id = publishers.pub id and type = business)

Результат: pub name

Algodata Infosystems New Age Books

EXISTS выполняет проверку на наличие или отсутствие пустого набора строк. Если подзапрос возвращает хотя бы одну строку, этот результат оценивается как истина . Это означает, что результат выполнения EXISTS будет успешным, а результат выполнения NOT EXISTS будет неудачным. Если подзапрос возвращает пустой набор (строк нет), этот результат оценивается как ложь . Это означает, что результат выполнения фразы NOT EXISTS будет успешным, а результат выполнения фразы EXISTS будет неудачным.

В этом случае первым названием издательства будет Algodata Infosystems (с идентификационным номером 1389). Проходит ли Algodata Infosystems тест на существование? Другими словами, есть ли какие-то строки в таблице titles, в которых pubid равен 1389, а type соответствует бизнесу? Если есть, то Algodata Infosystems должно быть одним из выбранных значений. Тот же процесс повторяется для каждого из названий остальных издательств.

Обратите внимание, что синтаксис подзапросов, начинающихся с EXISTS, отличается от синтаксиса других подзапросов в следующих отношениях.

Ключевому слову EXISTS не предшествует имя столбца, константа или какое-то другое выражение.

Список выбора подзапроса, начинающегося с EXISTS, почти всегда состоит из звездочки (*). Нет никакого смысла вводить имена столбцов, поскольку вы лишь выполняете тест на существование строк, которые удовлетворяют условиям подзапроса, а они указываются в предложении WHERE этого подзапроса (а не в предложении SELECT этого подзапроса).

Ключевое слово EXISTS - чрезвычайно важный элемент, поскольку зачастую отсутствует альтернатива использованию подзапроса. На практике EXISTS-подзапрос почти всегда является коррелированным подзапросом. Вместо использования внешнего запроса для обработки значений, поставляемых внутренним запросом, внещний запрос можно использовать для выработки одного за другим значений, которые будут тестироваться внутренним запросом.

SQL: SQL:

select title select title

from titles from titles

where pub id in where pub id in

(select pub id (select *

from publishers from publishers

where city like B%) where pub id = titles.pub id

and city like B%)

Рис. 8.5. Сравнение подзапросов с IN и EXISTS 178 Практическое руководство no SQL