Підзапити запити всередині запитів

(Вибрані 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.

set price = price * 2

where pub_id in

where pub_name = "New Age Books")

Еквівалентний попередньому оператор модифікації, в якому використовується з'єднання, виглядає наступним чином:

set price = price * 2

from titles, publishers

where titles.pub_id = publishers.pub_id

and pub_name = "New Age Books"

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

where title_id in

where type = "business")

Еквівалентний попередньому оператор видалення, який використовує з'єднання, виглядає наступним чином:

from salesdetail, titles

where salesdetail.title_id = titles.title_id

and type = "business"

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

if exists (select title_id

where type = "business")

where title_id in

Існують два основних типи підзапитів:

· Підзапити, яким передує немодифіковані операція порівняння і які повертають єдине значення, називаються подзапросамі- виразами (скалярними підзапитах).

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

Підзапити будь-якого з цих типів можуть бути або корельованими (повторюваними), або некоррелірованнимі.

· Некорреліровани підзапит може обчислюватися як незалежний запит. Інакше кажучи, результати підзапиту підставляються в основний оператор (або зовнішній запит). Це не означає. що SQL-сервер саме так виконує оператори з підзапитах. Некорреляціонние підзапити можуть бути замінені з'єднанням і будуть виконуватися як з'єднання SQL-сервером.

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

У наступних розділах цієї глави розглядаються різні типи підзапитів.

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

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

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

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

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

В ідеалі, для використання підзапиту з немодифікованої операцією порівняння, користувач повинен досить добре знати табличні дані і розуміти природу завдання, щоб бути впевненим, що підзапит видасть єдине значення.

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

select au_lname, au_fname

where pub_name = "Algodata Infosystems")

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 означає дорівнює деякому значенню

select au_lname, au_fname

where city = any

(Обрана 21 рядок)

Той же результат можна отримати за допомогою квантора! = All. який еквівалентний умові not in:

select au_lname, au_fname

where city! = all

Підзапити з умовою in

where pub_id in

where type = "business")

(Вибрані 2 рядки)

Цей оператор обчислюється за два кроки. На першому кроці внутрішній запит повертає список номерів видавців, які друкують книги по бізнесу, а саме номери 1 389 і 0736. На другому кроці ці величини підставляються в зовнішній запит, щоб знайти назви цих видавництв в таблиці publishers. На цьому кроці запит виглядає наступним чином:

where pub_id in ( "1 389", "0736")

Інший спосіб завдання цього запиту за допомогою підзапиту виглядає наступним чином:

where "business" in

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"

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 рядки)

select au_lname, au_fname

where state = "CA"

where royaltyper <30

(Обрана 1 рядок)

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

З використанням сполуки, оператор виглядає наступним чином:

select au_lname, au_fname

from authors, titleauthor

where state = "CA"

and authors.au_id = titleauthor.au_id

and royaltyper <30

З'єднання завжди може бути виражено за допомогою підзапиту. Підзапит також часто може бути виражений як з'єднання.

Підзапити з умовою notin

Підзапити, яким передує ключова фраза not in (не в), також повертають список з жодного або декількох значень. Ця фраза означає «не а й не в і не з".

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

select pub_name from publishers

where pub_id not in

where type = "business")

Binnet Hardley

(Обрана 1 рядок)

Підзапити з умовою notin, що містять NULL

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

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

Проілюструємо це на такому прикладі, використовуючи базу pubs2:

where $ 100.00 not in

where titles.pub_id = publishers.pub_id)

(Вибрані 2 рядки)

Щоб зрозуміти виконання цього запиту, розглянемо по порядку назву кожного видавництва. Чи буде в результаті підзапиту хоча б один рядок з цією назвою? Іншими словами, чи буде перевірка існування істинної (TRUE)?

В результаті попереднього запиту на другому місці вказано видавництво Algodata Inforsystems, яке має ідентифікаційний номер 1389. Чи є хоча б один рядок в таблиці titles. в якій поле pub_id має значення тисячі триста вісімдесят дев'ять і поле type значення "business"? Якщо так, то видавництво "Algodata Inforsystems" має потрапити в результат. Подібна перевірка здійснюється для кожного видавництва.

Підзапит, якому передує квантор існування exists. має в порівнянні з іншими підзапитах такі особливості:

· Перед ключовим словом exists не повинно бути назв стовпців, констант або інших виразів.

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

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

Ключове слово exists є дуже важливим, оскільки часто не існує альтернативного способу вибору даних без використання підзапиту. Підзапити, яким передує квантор exists завжди є корелює підзапитах (див. Розділ "Використання корелює підзапитів).

Як вже зазначалося, деякі запити з квантором існування не можна висловити в інший спосіб, але все запити з умовою in або з оператором порівняння, доповненим квантором all або any. можна виразити за допомогою підзапиту з exists. Далі наводяться кілька прикладів операторів з квантором exists і їх еквівалентні альтернативні переформулювання.

select au_lname, au_fname

select au_lname, au_fname

Знаходження перетину і різниці множин за допомогою exists

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

select distinct city

where authors.city = publishers.city)

(Обрана 1 рядок)

select distinct city

where not exists

where authors.city = publishers.city)

del Castillo Innes

(Вибрані 9 рядків)

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

Розглянемо докладніше як обчислюється попередній запит. Спочатку Transact-SQL переглядає кожен рядок в таблиці authors і, щоб з'ясувати яку з них треба включити в результат, передає відповідне значення у внутрішній підзапит. Наприклад, припустимо, що Transact-SQL переглядає рядок, відповідну Cheryl Carson. Її ідентифікатор (authors.au_id) дорівнює "238-95-7766", тому це значення підставляється у внутрішній запит:

where au_id = "238-95-7766"

В результаті отримаємо число 100, тому зовнішній запит в цьому випадку буде виглядати наступним чином:

select au_lname, au_fname

where 100 in (100)

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

select distinct t1.type