Подзапросы: запросы внутри запросов
01.01.2007
Подзапросы: Запросы внутри запросов
Подзапрос - это оператор выбора, который содержится внутри другого оператора выбора, вставки, обновления или удаления, внутри условного оператора или внутри другого подзапроса.
В этой главе обсуждаются следующие темы:
| · | Выражения с подзапросами; |
| · | Квантифицированные предикаты с подзапросами; |
| · | Коррелирующиеся подзапросы. |
Подзапросы обычно содержатся в предложениях 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 в подзапросах-выражениях |
Поскольку подзапросы, которым предшествует немодифицированная операция сравнения, должны возвращать скалярную величину, то обычно они не могут включать предложений 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 (различные), чтобы в результате получалась скалярная величина.
Например, следующий запрос без слова 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 допускается в подзапросах с кванторами, тем не менее при исполнении оно игнорируется, т.е. выполнение происходит также, как и при его отсутствии.
| Подзапросы с кванторами any и all |
Ключевые слова 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 означает больше чем все значения |
В контексе подзапроса квантор >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 означает равно каждому значению |
Квантор =all означает равенство каждому возвращаемому значению. Текущая строка будет удовлетворять условию, указанному во внешнем запросе, если значение в сравниваемом столбце будет равно каждому значению, которое возвращается подзапросом.
Например, в следующем запросе находятся авторы, живущие в одном городе, путем сравнения их почтовых индексов:
select au_fname, au_lname, city
from authors
where city = all
(select city
from authors
where postalcode like "946%")
| >any означает больше, по крайней мере, одного значения |
Квантор >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 означает равно некоторому значению |
Квантор =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 |
Подзапросы, которым предшествует ключевая фраза 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, содержащие NULL |
Подзапрос, которому предшествует 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 |
Подзапросы, которым предшествует ключевое слово 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 |
Подзапросы, которым предшествуют кванторы 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 |
Квантифицированные подзапросы также могут быть коррелированными. В следующем примере с коррелированным подзапросом, расположенном в предложении 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 строка)
Этот подзапрос будет вычисляться один раз для каждой группы, опеределенной во внешнем запросе, т.е. один раз для каждого типа книг.
