ПОДЗАПРОСЫ: ЗАПРОСЫ ВНУТРИ ЗАПРОСОВ

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

В этой главе обсуждаются следующие темы:

·         Что такое подзапросы;

·         Типы подзапросов;

·         Выражения с подзапросами;

·         Квантифицированные предикаты с подзапросами;

·         Коррелирующиеся подзапросы.

Подзапросы обычно содержатся в предложениях where или having SQL оператора или в списке выбора этого оператора. С помощью подзапросов можно проводить дальнейший отбор данных из результатов других запросов. Оператор, содержащий подзапрос, может обрабатывать строки некоторой таблицы, основываясь на результатах вычисления списка выбора подзапроса, который в свою очередь может ссылаться на эту же таблицу как внешний запрос, или обращаться к другой таблице. В языке Transact-SQL подзапросы допускаются почти везде, где допускаются выражения,  если подзапрос возвращает одно значение.

Операторы выбора, содержащие один или несколько подзапросов, называются также составными запросами или составными операторами выбора. Возможность включения одного оператора выбора внутрь другого является одной из причин, по которой язык SQL называется “структурированным” (Structured Query Language).

SQL оператор, который включает подзапросы, называемые также внутренними запросами, можно иногда заменить соединением. Есть вопросы, которые можно сформулировать только с помощью подзапросов. Некоторые люди предпочитают всегда использовать подзапросы, поскольку находят их легкими для понимания. Другие стремятся их избегать всегда, когда это возможно. Читатель может выбрать сам удобный для себя способ. (SQL Сервер также переводит некоторые подзапросы в соединения, прежде чем выполнять их).

Если нужно найти все книги, имеющие ту же цену, что и книга Straight Talk About Computers, то это можно сделать за два шага. Во-первых найти цену этой книги:

select price

from titles

where title = "Straight Talk About Computers"

price

-------------

       $19.99

(Выбрана 1 строка)

Затем, используя этот результат во втором запросе, уже можно найти все книги, имеющие ту же стоимость, что и Straight Talk:

select title, price

from titles

where price = $19.99

title                                                                          price

-------------------------------------------------------------        --------

The Busy Executive's Database Guide                              19.99

Straight Talk About Computers                                         19.99

Silicon Valley Gastronomic Treats                                     19.99

Prolonged Data Deprivation: Four Case Studies             19.99

(Выбрано 4 строки)

С помощью подзапроса эта задача решается одним оператором:

select title, price

from titles

where price =

   (select price

    from titles

    where title = "Straight Talk About Computers")

title                                                                          price

--------------------------------------------------------             --------

The Busy Executive's Database Guide                              19.99

Straight Talk About Computers                                         19.99

Silicon Valley Gastronomic Treats                                     19.99

Prolonged Data Deprivation: Four Case Studies             19.99

(Выбрано 4 строки)

Общие правила написания и синтаксис подзапросов

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

(select [distinct] список_выбора_подзапроса

[from [[database.]owner.]{название_таблицы | название_вьювера}

 [({index название_индекса | prefetch size |[lru|mru]})]}

        [holdlock | noholdlock] [shared]

     [,[[database.]owner.]{ название_таблицы | название_вьювера }

 [({index название_индекса | prefetch size |[lru|mru]})]}

        [holdlock | noholdlock] [shared]]... ]

[where условия_отбора]

[group by выражение_без_агрегации [,

             выражение_без_агрегации]... ]

[having условия_отбора])

Подзапросы могут быть вложенными в конструкциях (предложениях) where или having внешних операторов выбора (select), вставки (insert), обновления (update) или удаления (delete), а также вложенными в другие подзапросы или помещены в список выбора.

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

На подзапросы накладываются следующие ограничения:

·           Подзапросы нельзя использовать в списках предложений order by, group by и compute by.

·           Подзапрос не может содержать предложения for browse или union.

·           Список выбора внутреннего подзапроса, которому предшествует операция сравнения, может содержать только одно выражение или название столбца, и подзапрос должен возвращать единственный результат. При этом тип данных столбца, указанного в конструкции where внешнего оператора, должен быть совместим c типом данных в столбце, указанным в списке выбора подзапроса (правила здесь такие же как и при соединении).

·           В подзапросах не допускаются текстовые (text) и графические (image) данные.

·           Подзапросы не могут обрабатывать свои результаты внутренним образом, т.е. подзапрос не может содержать конструкций order by, compute, или ключевого словаinto.

·           Коррелирующиеся (повторяющиеся) подзапросы не допускаются в конструкции select обновляемого курсора, определенного с помощью declare cursor (определить курсор).

·           Количество вложенных уровней для подзапросов не должно превышать 16.

·           Максимальное число подзапросов на каждой стороне объединения не больше 16.

В следующем примере столбец pub_id в конструкции where внешнего запроса неявно определяется таблицей publishers из конструкции from этого запроса. Обращение к столбцу pub_id в списке выбора подзапроса определяется конструкцией from подзапроса, т.е. таблицей titles:

select pub_name

from publishers

where pub_id in

   (select pub_id

    from titles

    where type = "business")

Общее правило таково: названия столбцов в операторе неявно определяются таблицей, которая указана в конструкции from этого уровня вложенности.

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

select pub_name

from publishers

where publishers.pub_id in

   (select titles.pub_id

    from titles

    where type = "business")

Никогда нелишне явно указывать название таблицы и всегда можно заменить неявные предположения явным использованием расширенных названий столбцов вместе с названием таблицы.

Как отмечалось в главе 5, "Соединения: Выбор данных из нескольких таблиц", коррелирующиеся (согласующиеся) названия таблиц необходимы в самосоединениях, поскольку таблица, присоединенная сама к себе, выступает в двух различных ролях. Коррелирующиеся названия могут также использоваться во вложенных запросах, которые ссылаются на одну и ту же  таблицу, как во внутреннем, так и во внешнем запросе.

Например, с помощью следующего подзапроса можно найти писателей, живущих в одном городе с Ливией Карсен:

select au1.au_lname, au1.au_fname, au1.city

from authors au1

where au1.city in

   (select au2.city

    from authors au2

    where au2.au_fname = "Livia"

    and au2.au_lname = "Karsen")

au_lname                  au_fname                 city

-----------        --------------     -----------

Green                       Marjorie                  Oakland

Straight                     Dick                        Oakland

Stringer                     Dirk                         Oakland

MacFeather              Stearns                    Oakland

Karsen                      Livia                        Oakland

(Выбрано 5 строк)

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

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

select au_lname, au_fname, city

from authors

where city in

   (select city

    from authors

    where au_fname = "Livia"

    and au_lname = "Karsen")

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

select au1.au_lname, au1.au_fname, au1.city

from authors au1, authors au2

where au1.city = au2.city

and au2.au_lname = "Karsen"

and au2.au_fname = "Livia"

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

Подзапрос может содержать в себе один или несколько подзапросов следующего уровня. Оператор может содержать подзапросы 16 уровней вложенности.

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

select au_lname, au_fname

from authors

where au_id in

   (select au_id

    from titleauthor

    where title_id in

       (select title_id

        from titles

        where type = "popular_comp") )

au_lname               au_fname

----------------------       ------------

Carson                    Cheryl

Dull                          Ann

Hunter                     Sheryl

Locksley                 Chastity

(Выбрано 4 строки)

Самый внешний запрос выбирает имена и фамилии всех писателей. Запрос следующего уровня находит идентификационные номера писателей, а самый внутренний запрос возвращает идентификационные номера книг PC1035, PC8888 и PC9999.

Этот запрос также можно выразить с помощью соединения:

select au_lname, au_fname

from authors, titles, titleauthor

where authors.au_id = titleauthor.au_id

and titles.title_id = titleauthor.title_id

and type = "popular_comp"

Подзапроcы могут быть вложенными в операторах  модификации (update), удаления (delete) и вставки (insert) так же, как и в операторе выбора.

Замечание: Выполнение следующих примеров изменит содержимое базы данных pubs2. Следует обратиться к системному администратору, чтобы получить исходную копию этой базы.

В следующем запросе удваиваются цены всех книг, изданных компанией New Age Books. Этот оператор модифицирует таблицу titles, а подзапрос обращается к таблицеpublishers.

update titles

set price = price * 2

where pub_id in

   (select pub_id

    from publishers

    where pub_name = "New Age Books")

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

update titles

set price = price * 2

from titles, publishers

where titles.pub_id = publishers.pub_id

and pub_name = "New Age Books"

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

delete salesdetail

where title_id in

   (select title_id

    from titles

    where type = "business")

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

delete salesdetail

from salesdetail, titles

where salesdetail.title_id = titles.title_id

and type = "business"

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

if exists (select title_id

    from titles

    where type = "business")

begin

    delete salesdetail

    where title_id in

      (select title_id

       from titles

       where type = "business")

end

В языке Transact-SQL подзапрос можно подставлять почти в любое место в операторах выбора, модификации, вставки и удаления, где может размещаться выражение.Подзапросы нельзя использовать в списках выбора предложения order by. Ниже приведены некоторые примеры, которые показывают, как правильно  использовать это расширение языка Transact-SQL.

В следующем запросе выбираются названия и типы книг, которые были написаны авторами, живущими в Калифорнии, и изданные там же:

select title, type

from titles

where title in

   (select title

    from titles, titleauthor, authors

    where titles.title_id = titleauthor.title_id

    and titleauthor.au_id = authors.au_id

    and authors.state = "CA")

and title in

   (select title

    from titles, publishers

    where titles.pub_id = publishers.pub_id

    and publishers.state = "CA")

title                                                                          type

--------------------------------------------------         ------------------

The Busy Executive's Database Guide              business

Cooking with Computers:

                  Surreptitious Balance Sheets         business

Straight Talk About Computers                        business

But Is It User Friendly?                                        popular_comp

Secrets of Silicon Valley                                      popular_comp

Net Etiquette                                                          popular_comp

(Выбрано 6 строк)

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

select title, price,

    (select max(price) from titles)

    from titles

    where total_sales > 5000

title                                                                    price

-----------------------------------                     -------     -------

You Can Combat Computer Stress!        2.99     22.95

The Gourmet Microwave                         2.99     22.95

But Is It User Friendly?                          22.95    22.95

Fifty Years in Buckingham Palace          11.95    22.95

                   Kitchens

Существуют два основных типа подзапросов:

·           Подзапросы, которым предшествует немодифицированная операция сравнения и которые возвращают единственное значение, называются подзапросами- выражениями (скалярными подзапросами).

·           Подзапросы, которые возвращают список значений и которым предшествует  ключевое слово in (принадлежит) или операция сравнения, модифицированная кванторами  any (некоторый) или all (все), а также подзапросы, проверяющие существование  с помощью квантора exists (существует), называютсяквантифицированными предикатными подзапросами.

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

·           Некоррелированный подзапрос может вычисляться как независимый запрос. Иначе говоря, результаты подзапроса подставляются в основной оператор (или внешний запрос). Это не значит, что SQL-сервер именно так выполняет операторы с подзапросами. Некорреляционные подзапросы могут быть заменены соединением и будут выполняться как соединения SQL-сервером.

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

В следующих разделах этой главы  рассматриваются различные типы подзапросов.

Подзапросам-выражениям предшествует одна из операций сравнения =, !=, <>, >, >=, <, !< или <= и они имеют следующую общую форму:

[Начало оператора выбора, вставки, модификации, удаления или подзапроса]

where  выражение операция_сравнения (подзапрос)

[Конец оператора выбора, вставки, модификации, удаления или подзапроса]

Подзапрос, которому предшествует немодифицированная операция сравнения, т.е. операция сравнения без квантора any или all, должен возвращать единственное значение. В противном случае SQL-сервер выдает сообщение об ошибке.  

В идеале, для использования подзапроса с немодифицированной операцией сравнения, пользователь должен достаточно хорошо знать табличные данные и понимать природу задачи, чтобы быть уверенным, что подзапрос выдаст единственное значение.

Например, предположим, что каждое издательство находится только в одном городе. Тогда для нахождения писателей, живущих в городе, где располагается издательство Algodata Infosystems, необходимо выполнить оператор с подзапросом, которому предшествует сравнение на равенство:

select au_lname, au_fname

from authors

where city =

   (select city

    from publishers

    where pub_name = "Algodata Infosystems")

au_lname       au_fname

--------------     --------------

Carson         Cheryl

Bennet         Abraham

(Выбраны 2 строки)

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

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

select title

from titles

where price >

   (select min(price)

    from titles)

title

---------------------------------------------------

The Busy Executive's Database Guide

Cooking with Computers: Surreptitious Balance Sheets

Straight Talk About Computers

Silicon Valley Gastronomic Treats

But Is It User Friendly?

Secrets of Silicon Valley

Computer Phobic and Non-Phobic Individuals: Behavior Variations

Is Anger the Enemy?

Life Without Fear

Prolonged Data Deprivation: Four Case Studies

Emotional Security: A New Algorithm

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

Fifty Years in Buckingham Palace Kitchens

Sushi, Anyone?

(Выбрано 14 строк)

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

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

select title

from titles

where price >

   (select min(price)

    from titles

    group by type

    having type = "trad_cook")

Подзапросы, которым предшествует немодифицированная операция сравнения, часто содержат ключевое слово distinct (различные), чтобы в результате получалась скалярная величина.

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

select pub_name from publishers

    where pub_id =

        (select distinct pub_id

        from titles

        where pub_id  = publishers.pub_id)

Квантифицированные подзапросы - это подзапросы, возвращающие несколько значений (или никаких значений), которым в предложениях where или having, предшествует один из кванторов any (некоторый), all (все), in (в) или exist (существует). Кванторы any и all модифицируют операции сравнения.

·           Подзапросы, которым предшествует модифицированная операция сравнения, и которые могут  содержать предложения where или having, имеют следующий общий вид:

       [ Начало оператора выбора, вставки, модификации, удаления или подзапроса]

        where выражение операция_сравнения [any | all] (подзапрос)

       [ Конец оператора выбора, вставки, модификации, удаления или подзапроса]

·           Подзапросы, которым предшествует ключевые слова in (принадлежит) и not in (не принадлежит) имеют следующий общий вид:

       [ Начало оператора выбора, вставки, модификации, удаления или подзапроса]

        where выражение [not] in (подзапрос)

       [ Конец оператора выбора, вставки, модификации, удаления или подзапроса]

·           Подзапросы с кванторами exists (существует) и not exists (не существует), проверяющие существование некоторых значений, имеют следующий общий вид:

       [ Начало оператора выбора, вставки, модификации, удаления или подзапроса]

        where [not] exists (подзапрос)

       [ Конец оператора выбора, вставки, модификации, удаления или подзапроса]

Хотя ключевое слово distinct допускается в подзапросах с кванторами, тем не менее при исполнении оно игнорируется, т.е. выполнение происходит также, как и при его отсутствии.

Ключевые слова all и any модифицируют операцию сравнения, которая формирует подзапрос.

Рассмотрим в качестве примера операцию > (больше):

·           >all означает больше чем любое значение или, что равносильно, больше максимальной величины. Например, >all (1,2,3) означает больше чем 3.

·           >any означает больше, по крайней мере, одного значения или, что равносильно, больше минимальной величины. Поэтому, >any (1,2,3) означает меньше 1.

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

Использование кванторов all и any требует внимательности, поскольку компьютер не допускает двусмысленности, которая свойственна этим словам в обычном языке. Например, можно задать вопрос: “По каким книгам выплачен аванс, больший чем по любой (any) книге, опубликованной издательством New Age Books?”.

Этот вопрос можно перефразировать в SQL в более точной форме: “По каким книгам выплачен аванс, больший чем максимальный аванс, выплаченный издательством New Age Books?”. В данном случае нужно использовать ключевое слово all (все), а не any:

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

(Выбрана одна строка)

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

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

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

select title from titles where price > all

    (select price from titles

    where type =  "mod_cook")

title

---------------------------------------------------

But Is It User Friendly?

Secrets of Silicon Valley

Computer Phobic and Non-Phobic Individuals: Behavior Variations

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

(Выбрано 4 строки)

Однако, если множество значений, возвращаемое подзапросом, содержит неопределенное значение NULL, то запрос возвращает 0 строк (пустое множество), поскольку невозможно сравнить конкретную величину с неопределенным значением.

Например, можно попытаться найти книги, которые стоят больше чем самая дорогая книга в группе popular_comp:

select title from titles where price > all

    (select price from titles

    where title_id = "popular_comp")

title

---------------------------------------------------

(0 rows affected)

В результате возвращается пустое множество, поскольку в подзапросе обнаруживается, что книга Net Etiquette имеет неопределенную цену.

Квантор =all означает равенство каждому возвращаемому значению. Текущая строка будет удовлетворять условию, указанному во внешнем запросе, если значение в сравниваемом столбце будет равно каждому значению, которое возвращается подзапросом.

Например, в следующем запросе находятся авторы, живущие в одном городе, путем сравнения их почтовых индексов:

select au_fname, au_lname, city

from authors

where city = all

     (select city

     from authors

     where postalcode like "946%")

Квантор >any означает, что текущая строка будет удовлетворять условию, указанному во внешнем запросе, если значение в сравниваемом столбце будет больше, по крайней мере, одного значения, которое возвращается подзапросом.

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

select title

from titles

where advance > any

   (select advance

    from titles, publishers

    where titles.pub_id = publishers.pub_id

    and pub_name = "New Age Books")

title

---------------------------------------------------

Sushi, Anyone?

Life Without Fear

Is Anger the Enemy?

The Gourmet Microwave

But Is It User Friendly?

Secrets of Silicon Valley

Straight Talk About Computers

You Can Combat Computer Stress!

Emotional Security: A New Algorithm

The Busy Executive's Database Guide

Fifty Years in Buckingham Palace Kitchens

Cooking with Computers: Surreptitious Balance Sheets

Computer Phobic and Non-Phobic Individuals: Behavior Variations

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

(Выбрано 14 строк)

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

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

Квантор =any означает проверку существования, поэтому он эквивалентен условию in (в). Например, чтобы найти всех авторов, которые живут в одном городе с некоторым издателем, можно использовать как =any, так и in:

select au_lname, au_fname

from authors

where city = any

   (select city

    from publishers)

select au_lname, au_fname

from authors

where city in

   (select city

    from publishers)

au_lname       au_fname

--------------     --------------

Carson         Cheryl

Bennet         Abraham

(Выбраны 2 строки)

Однако, квантор !=any будет не равносилен условию not in (не в). Квантор !=any означает “не а или не в или не с”, в то время как условие not in означает “не а и не в и не с”.

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

select au_lname, au_fname

from authors

where city != any

   (select city

    from publishers)

В результате будут перечислены все 23 автора, поскольку каждый автор живет в городе, где нет некоторого издательства.

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

Посмотрим, что произойдет, если в тот же самый запрос вставить условие not in:

select au_lname, au_fname

from authors

where city not in

   (select city

    from publishers)

au_lname                                au_fname

--------------                               ------------

del Castillo                             Innes

Blotchet-Halls                        Reginald

Gringlesby                              Burt

DeFrance                                Michel

Smith                                       Meander

White                                      Johnson

Greene                                     Morningstar

Green                                       Marjorie

Straight                                   Dick

Stringer                                   Dirk

MacFeather                            Stearns

Karsen                                    Livia

Dull                                          Ann

Hunter                                     Sheryl

Panteley                                  Sylvia

Ringer                                     Anne

Ringer                                     Albert

Locksley                                 Chastity

O'Leary                                   Michael

McBadden                             Heather

Yokomoto                               Akiko

(Выбрана 21 строка )

Это как раз тот результат, который необходимо было получить. Он включает всех авторов, за исключением Cheryl Carson и Abraham Bennet, которые живут в Беркли, где расположено издательство Algodata Infosystem.

Тот же результат можно получить с помощью квантора !=all, который эквивалентен условию not in:

select au_lname, au_fname

from authors

where city != all

   (select city

    from publishers)

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

select pub_name

from publishers

where pub_id in

   (select pub_id

    from titles

    where type = "business")

pub_name

------------------------------

New Age Books

Algodata Infosystems

(Выбраны 2 строки)

Этот оператор вычисляется за два шага. На первом шаге внутренний запрос возвращает список номеров издателей, которые печатают книги по бизнесу, а именно номера 1389 и 0736. На втором шаге эти величины подставляются во внешний запрос, чтобы найти названия этих издательств в таблице publishers. На этом шаге запрос выглядит следующим образом:

select pub_name

from publishers

where pub_id in ("1389", "0736")

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

select pub_name

from publishers

where "business" in

   (select type

    from titles

    where pub_id = publishers.pub_id)

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

select distinct pub_name

from publishers, titles

where publishers.pub_id = titles.pub_id

and type = "business"

Как этот запрос, так и запрос с подзапросом, будут находить издательства, публикующие книги по бизнесу. Оба они корректны и выдают одинаковые результаты, за исключением того, что в последнем случае используется слово distinct, чтобы исключить повторы.

Однако, одно из преимуществ запроса на соединение перед запросом с подзапросом заключается в том, что в этом случае можно помещать в результат данные из различных таблиц. Например, чтобы включить в результат названия книг по бизнесу, можно воспользоваться следующим запросом на соединение:

select pub_name, title

from publishers, titles

where publishers.pub_id = titles.pub_id

and type = "business"

pub_name                  title

----------------------------   -----------------------------------------------------

Algodata Infosystems   The Busy Executive's Database Guide

Algodata Infosystems   Cooking with Computers: Surreptitious       Balance Sheets

New Age Books           You Can Combat Computer Stress!

Algodata Infosystems   Straight Talk About Computers

(Выбрано 4 строки)

Далее рассмотрим еще один пример, который можно сформулировать как через подзапрос, так и через соединение. На естественном языке этот запрос формулируется следующим образом: “Найти имена всех вторых авторов, кто живет в Калифорнии и получил менее 30 процентов гонорара за книгу”. С использованием подзапроса, оператор будет выглядеть следующим образом:

select au_lname, au_fname

from authors

where state = "CA"

and au_id in

   (select au_id

    from titleauthor

    where royaltyper < 30

    and au_ord = 2)

au_lname                  au_fname

------------------------      ------------

MacFeather               Stearns

(Выбрана 1 строка)

Внешний запрос формирует список из 15 авторов, живущих в Калифорнии. Затем выполняется внутренний запрос и формируется список авторов, которые удовлетворяют всем условиям.

Заметим, что как во внешнем, так и во внутреннем запросе, приходится использовать несколько условий в предложении where.

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

select au_lname, au_fname

from authors, titleauthor

where state = "CA"

  and authors.au_id = titleauthor.au_id

  and royaltyper < 30

  and au_ord = 2

Соединение всегда может быть выражено с помощью подзапроса. Подзапрос также часто может быть выражен как соединение.

Подзапросы, которым предшествует ключевая фраза not in (не в), также возвращают список из ни одного или  нескольких значений. Эта фраза означает “не а и не в и не с”.

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

select pub_name from publishers

where pub_id not in

   (select pub_id

    from titles

    where type = "business")

pub_name

----------------------------------------

Binnet & Hardley

(Выбрана 1 строка)

Этот запрос в точности совпадает с ранее рассмотренным за исключением фразы not in, которая подставлена вместо in. Однако, этот запрос нельзя заменить соединением. Соединение через “не равно” будет иметь другой смысл, а именно, оно будет искать названия издательств, которые опубликовали некоторую книгу не по бизнесу. Трудности, возникающие в интерпретации запросов на соединение, в которых используется неравенство, подробно обсуждались в предыдущей главе 5 “Соединения: Выбор данных из нескольких таблиц”.

Подзапрос, которому предшествует not in, возвращает список значений для каждой строки внешнего запроса. Если значение поля, указанного во внешнем запросе, не содержится в этом списке, то фраза not in интерпретируется как истинная (TRUE) и внешний запрос помещает рассмотреную запись в результирующий список.

Однако, если список значений, возвращаемый внутренним запросом (подзапросом), не содержит указанного значения, но содержит неопределенное значение NULL, то фраза not in интерпретируется как логически неопределенная (UNKNOWN), поскольку в этом случае невозможно точно опеределить принадлежность указанного значения к возвращаемому списку. В этом случае внешний запрос не включает рассмотренную строку (запись) в результат запроса.

Проиллюстрируем это на следующем примере, используя базу pubs2:

select pub_name

    from publishers

    where $100.00 not in

        (select price

         from titles

         where titles.pub_id = publishers.pub_id)

returns:

pub_name

----------------------

New Age Books

В результате указано только издательство New Age Books, которое не публиковало книг по цене 100 долларов. Издательства Binnet & Handley и Algodata Infosystems не были включены в результат, поскольку каждое из них публиковало книги с неустановленной ценой.

Подзапросы, которым предшествует ключевое слово exists (существует), осуществляют проверку существования. Другими словами, в предложении where внешнего запроса проверяется существование, хотя бы одной строки, удовлетворяющей подзапросу. На самом деле подзапрос не возвращает никаких данных, а вместо этого возвращает логическое значение TRUE (истина) или FALSE (ложь).

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

select pub_name

from publishers

where exists

   (select *

    from titles

    where pub_id = publishers.pub_id

    and type = "business")

pub_name

----------------------------------------

New Age Books

Algodata Infosystems

(Выбрано 2 строки)

Чтобы понять выполнение этого запроса, рассмотрим по порядку название каждого издательства. Будет ли в результате подзапроса хотя бы одна строка с этим названием? Другими словами, будет ли проверка существования истинной (TRUE)?

 В результате предыдущего запроса на втором месте указано издательство Algodata Inforsystems, которое имеет идентификационный номер 1389. Имеется ли хотя бы одна строка в таблице titles, в которой поле pub_id имеет значение 1389 и поле type значение “business”? Если так, то издательство “Algodata Inforsystems” должно попасть в результат. Подобная проверка осуществляется для каждого издательства.

Подзапрос, которому предшествует квантор существования exists, имеет по  сравнению с другими подзапросами следующие особенности:

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

·           Подзапрос с квантором существования возвращает значения TRUE или FALSE и не возвращает никаких данных из таблицы.

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

Ключевое слово exists является очень важным, поскольку часто не существует альтернативного способа выбора данных без использования подзапроса. Подзапросы, которым предшествует квантор exists всегда являются коррелирующимися подзапросами (см. раздел “Использование коррелирующихся подзапросов).

Как уже отмечалось, некоторые запросы с квантором существования нельзя выразить иным способом, но все запросы с условием in или с оператором сравнения, дополненным квантором all или any, можно выразить с помощью подзапроса с exists. Далее приводятся несколько примеров операторов с квантором exists и их эквивалентные альтернативные переформулировки.

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

select au_lname, au_fname

from authors

where city =any

   (select city

    from publishers)

select au_lname, au_fname

from authors

where exists

   (select *

    from publishers

    where authors.city = publishers.city)

au_lname          au_fname

--------------        --------------

Carson            Cheryl

Bennet            Abraham

            (Выбрано 2 строки)

Далее приводятся два запроса, которые находят книги, опубликованные издательством, расположенном в городе, название которого начинается на букву “В”:

select title

from titles

where exists

   (select *

    from publishers

    where pub_id = titles.pub_id

    and city like "B%")

select title

from titles

where pub_id in

   (select pub_id

    from publishers

    where city like "B%")

title

---------------------------------------------------

The Busy Executive's Database Guide

Cooking with Computers: Surreptitious Balance Sheets

You Can Combat Computer Stress!    

Straight Talk About Computers   

But Is It User Friendly?     

Secrets of Silicon Valley

Net Etiquette

Is Anger the Enemy?

Life Without Fear

Prolonged Data Deprivation: Four Case Studies

Emotional Security: A New Algorithm

(Выбрано 11 строк)

Действие квантора not exists (не существует) аналогично действию квантора exists за исключением того, что предложение where, где он используется, считается истинным, когда ни одна строка не удовлетворяет подзапросу.

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

select pub_name

from publishers

where not exists

   (select *

    from titles

    where pub_id = publishers.pub_id

    and type = "business")

pub_name

-------------------------------

Binnet & Hardley

(Выбрана 1 строка)

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

select title

from titles

where not exists

   (select title_id

    from salesdetail

    where title_id = titles.title_id)

title

-----------------------------------------

The Psychology of Computer Cooking

Net Etiquette

(Выбрано 2 строки)

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

Пересечение таблиц authors и publishers по столбцу city состоит из множества городов, в которых есть и авторы и издательства:

select distinct city

from authors

where exists

  (select *

   from publishers

   where authors.city = publishers.city)

city

--------------------

Berkeley

(Выбрана 1 строка)

Разность таблиц authors и publishers по столбцу city состоит из множества городов, где проживает автор, но нет издательств, т.е. всех городов за исключением Беркли:

select distinct city

from authors

where not exists

  (select *

   from publishers

   where authors.city = publishers.city)

city

--------------------

Gary                 

Covelo               

Oakland              

Lawrence             

San Jose             

Ann Arbor            

Corvallis            

Nashville            

Palo Alto            

Rockville            

Vacaville            

Menlo Park           

Walnut Creek         

San Francisco        

Salt Lake City

(Выбрано 15 строк)

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

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

select au_lname, au_fname

from authors

where 100 in

   (select royaltyper

    from titleauthor

    where au_id = authors.au_id)

au_lname        au_fname

--------------     --------------

Carson          Cheryl               

Ringer          Albert               

Straight        Dick                  

White           Johnson              

Green           Marjorie             

Panteley        Sylvia               

Locksley        Chastity             

del Castillo    Innes                

Blotchet-Hall   Reginald

(Выбрано 9 строк)

В противоположность большинству ранее рассмотреных примеров, подзапрос в данном случае нельзя вычислять независимого от основного запроса. В нем используется значение authors.au_id, которое является переменным и зависит от строки, которую SQL Сервер рассматривает в таблице authors.

Рассмотрим подробнее как вычисляется предыдущий запрос. Сначала Transact-SQL просматривает каждую строку в таблице authors и, чтобы выяснить какую из них надо включить в результат, передает соответствующее значение во внутренний подзапрос. Например, предположим, что Transact-SQL просматривает строку, соответствующую Cheryl Carson. Ее идентификатор (authors.au_id) равен “238-95-7766”, поэтому это значение подставляется во внутренний запрос:

select royaltyper

from titleauthor

where au_id = "238-95-7766"

В результате получим число 100, поэтому внешний запрос в этом случае будет выглядеть следующим образом:

select au_lname, au_fname

from authors

where 100 in (100)

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

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

select distinct t1.type

from titles t1

where t1.type in

   (select t2.type

    from titles t2

    where t1.pub_id != t2.pub_id)

type

--------------------

business

psychology

(Выбрано 2 строки)

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

select distinct t1.type

from titles t1, titles t2

where t1.type = t2.type

and t1.pub_id != t2.pub_id

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

select s1.ord_num, s1.title_id, s1.qty

from salesdetail s1

where title_id like "PS%"

and s1.qty <

   (select avg(s2.qty)

    from salesdetail s2

    where s2.title_id = s1.title_id)

Далее приводятся результаты этого запроса:

ord_num                                 title_id        qty    

------------------                    -----------      -----

91-A-7                                     PS3333        90 

91-A-7                                     PS2106        30 

55-V-7                                      PS2106        31 

AX-532-FED-452-2Z7           PS7777       125

BA71224                                 PS7777       200 

NB-3.142                                 PS2091       200 

NB-3.142                                 PS7777       250 

NB-3.142                                 PS3333       345 

ZD-123-DFG-752-9G8           PS3333       750 

91-A-7                                     PS7777       180 

356921                                     PS3333       200

(Выбрано 11 строк)

Внешний запрос выбирает книги из таблицы sales (или “s1”) одну за другой. Подзапрос вычисляет среднее значение числа экземпляров этой книги продаваемых в одном заказе и если это значение превосходит число запрашиваемых экзепляров, то соответствующий заказ включается в результат.

В некоторых случаях коррелированный подзапрос имитирует действие оператора, содержащего предложение group by. Следующий запрос находит названия книг, имеющих цену, превосходящую среднюю цену книг этого типа:

select t1.type, t1.title

from titles t1

where t1.price >

   (select avg(t2.price)

    from titles t2

    where t1.type = t2.type)

type                                         title

---------                  -------------------------------------------------------------

business                  The Busy Executive's Database Guide

business                  Straight Talk About Computers

mod_cook               Silicon Valley Gastronomic Treats

popular_comp       But Is It User Friendly?

psychology            Computer Phobic and Non-Phobic Individuals: Behavior Variations

psychology              Prolonged Data Deprivation: Four Case Studies

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

(Выбрано 7 строк)

Для каждой строки из таблицы t1 Transact-SQL вычисляет подзапрос и включает строку в результат, если цена книги, указанная в этой строке, больше чем вычисленная средняя цена. Здесь нет необходимости явно группировать книги по типам, поскольку строки, по которым вычисляются средняя цена, отбираются в предложении whereподзапроса.

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

select t1.type

from titles t1

group by t1.type

having max(t1.advance) >=any

   (select 2 * avg(t2.advance)

   from titles t2

   where t1.type = t2.type)

type

-------------

mod_cook

(Выбрана 1 строка)

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