Результат:

au Iname au fname

MacFeather Stearns

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

SQL:

select authors.au id, au lname, au fname

from authors, titleauthors

where royaltyshare < 1.0

and authors.au id = titleauthors.au id

and authors.au id in

{select distinct authors.au id

from authors, titleauthors

where titleauthors.royaltyshare =1.0

and authors.au id = titleauthors.au id)

Результат:

au id au Iname au fname

213-46-8915 Green Marjorie

998-72-3567 Ringer Albert

Сначала внутренний запрос выбирает идентификационные номера авторов, доля авторского гонорара которых равна 100 процентам, а затем внешний запрос сравнивает эти идентификаторы со своей выборкой авторов, доля авторского гонорара которых составляет менее 100 процентов.

Ниже приведен пример объединения, которое отыскивает ту же информацию. Список выбора в этом случае составлен несколько иначе, что позволяет отображать оба значения гонорара (что не удается при использовании подзапроса, когда можно отображать только одно такое значение):

SQL:

select al.au id, au lname, tal.royaltyshare, ta2.royaltyshare from authors al, titleauthors tal, titleauthors ta2 where tal.royaltyshare < 1.0

and al.au id = tal.au id

and al.au id = ta2.au id

and ta2.royaltyshare =1.0

Результат:

au id au lname tal.royaltyshare ta2.royaltyshare

2Гз-4б-8915 Green 0.4 1

998-72-3567 Ringer 0.5 1

Подзапросы, начинающиеся с NOT IN

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

SQL:

select distinct pub name from publishers

where pub id not in (select pub id from titles

where type = business)

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

Binnet & Hardley

Этот запрос ничем не отличается от запроса, приведенного выше, за исключением того, что вместо IN в нем используется конструкция NOT IN. Однако этот оператор NOT IN нельзя преобразовать в не равное объединение. Аналогичное не равное объединение имеет иной смысл: оно отыскивает названия издательств, которые опубликовали какую-то книгу, которая не является книгой по бизнесу.

SQL:

select distinct pub name from publishers, titles

where publishers.pub id = titles.pub id

and type != business

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

Algodata Infosystems Binnet & Hardley New Age Books

Если вам требуется восстановить в своей памяти сведения об объединениях, основанных на неравенствах, обратитесь к главе 7.

Коррелированные подзапросы с IN

Фамилии всех авторов, получающих 100 процентов авторского гонорара, можно найти с помощью следующего оператора:

SQL:

select distinct au lname, au fname from authors where 1.00 in (select royaltyshare

from titleauthors

where au id = authors.au id)

Результат:

au Iname au fname

Blotchet-Halls Reginald

Carson Cheryl

del Castillo Innes

Green Marjorie

Locksley Chastity

Panteley Sylvia

Ringer Albert

Straight Dick

White Johnson

в отличие от большинства предыдуших примеров подзапросов, вы не можете выполнить подзапрос этого оператора независимо от внешнего запроса. Значение, которое требуется этому подзапросу для authors.au id, является переменной: оно изменяется по мере того, как система анализирует разные строки таблицы authors.

Вот как, с концептуальной точки зрения, система обрабатывает этот запрос. Она проверяет каждую строку таблицы authors на соответствие указанному условию. Допустим, что система сначала анализирует строку для Cheryl Carson. Переменная authors.auid принимает значение 238-95-7766 , которое система подставляет во внутренний запрос:

SQL:

select royaltyshare

from titleauthors

where au id = 238-95-7766

Результатом будет 1.00, поэтому внешний запрос примет следующий вид:

SQL:

select au lname, au fname

from authors

where 1.00 in (1.00)

Поскольку это соответствует истине, строка для Cheryl Carson будет включена в результаты. Если ту же процедуру выполнить со строкой для Abraham Bennet, то вы увидите, что эта строка не вписывается в указанные условия.

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

SQL:

select distinct tl.type from titles tl where tl.type in

(select t2.type from titles t2

where tl.pub id != t2.pub id)

Результат:

typ e

business

Чтобы различать две разные роли, которые выполняет таблица titles, здесь требуется применение псевдонимов. Этот вложенный запрос эквивалентен оператору самообъединения:

SQL:

select distinct tl.type from titles tl, titles t2 where tl.type = t2.type and tl.pub id i= t2.pub id

Коррелированные IN-подзапросы в предложении HAVING. Коррелированный подзапрос можно также использовать в предложении HAVING. Такой вариант запроса можно, например, использовать для нахождения типов книг, максимальный аванс за которые, по меньшей мере, в два раза превышает величину среднего аванса для этого типа книг.

SQL:

select tl.type from titles tl