Використання вкладених запитів

У самому загальному випадку, запити можуть керувати іншими запитами - це робиться шляхом розміщення запиту усередину предиката іншого, котрий використовує висновок внутрішнього запиту для установлення вірного чи невірного значення предиката Наприклад, потрібно інформація про успішність студента з прізвищем Поляків, однак у силу яких-небудь причин невідомий номер цього студента. Тоді необхідно витягти цей номер з таблиці з даними про студентів, і після цього застосовувати результат до таблиці успішності. Це можна реалізувати шляхом наступної конструкції:

SELECT * FROM USP

WHERE SNUM = (SELECT SNUM

FROM STUDENTS

WHERE SFAM = 'Поляків');

Результат цього запиту буде наступний:

UNUM OCENKA UDATE SNUM PNUM

1001 5 10/06/1999 3412 2001
1004 4 12/06/1999 3412 2003

Щоб виконати основний запит. SQL спочатку повинний оцінити внутрішній запит (його називають підзапитом) усередині пропозиції WHERE. Відбувається це традиційним образом, тобто виповнюється вкладений запит, що витягає необхідні для визначення значення предиката дані, а тільки потім -основний. Зрозуміло, підзапит повинний вибрати тільки одне поле, а тип даних цього поля повинний збігатися з тим значенням. з який він буде порівнюватися в предикаті.

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

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

SELECT *

FROM PREDMET WHERE TNUM =

(SELECT DISTINCT TNUM FROM TEACHERS WHERE TFAM = 'Викулина');

В результаті маємо:

PNUM PNAME TNUM HOURS COURS

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

2001 ФІЗИКА 4001 34 1

Підзапит установив, що значення поля TNUM збіглося з прізвищем Викулина при значенні 4001, а потім основний запит виділив усі записи з цим значенням TNUM з таблиці предметів Т.к.. узагалі говорячи, могло вийти, що викладач веде кілька предметів, то фраза DISTINCT у даному випадку обов'язкова - якщо підзапит повернув би більш одного значення, те це викликало би помилку.

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

<ВИРАЖЕННЯ> <ОПЕРАТОР> <ПІДЗАПИТ>, і ні в якому разі не

<ПІДЗАПИТ> <ОПЕРАТОР> <ВИРАЖЕННЯ>,

ЧИ

<ПІДЗАПИТ> <ОПЕРАТОР> <ПІДЗАПИТ>,

Інакше кажучи, що предыдет приклад, записаний у такий спосіб:

SELECT *

FROM PREDMET

WHERE (SELECT DISTINCT TNUM FROM TEACHERS WHERE TFAM = 'Викулина') = TNUM;

є невірним.

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

SELECT * FROM USP WHERE OCENKA >

(SELECT AVG (OCENKA) FROM USP);

Висновок такого запиту наступний:
UNUM OCENKA UDATE SNUM PNUM

1001 5 10/06/1999 3412 2001
1005 5 12/06/1999 3416 2004

Середня оцінка по наявним даним складає 4.2, отже, основний запит вибирає тільки ті записи, у яких значення поля OCENKA більше, ніж 4.2.

Не варто забувати, що згруповані агрегатні функції, визначені в термінах пропозиції GROUP BY, можуть видавати численні значення, а виходить, не допускаються в підзапитах такого характеру. Навіть якщо GROUP BY чи HAVING використовуються так, що тільки одна група значень виводиться за допомогою підзапита, усе рівно команда буде відхилена. До речі говорячи, можна використовувати підзапити, що роблять будь-як число рядків, якщо використовується спеціальний оператор IN (оператори BETWEEN, LIKE, і IS NULL не можуть використовуватися з підзапитами). IN визначає набір значень предиката, одне з яких повинно збігатися з іншим один по одному. При використанні IN з підзапитом, SQL просто формує цей набір з висновку підзапита, а виходить, допускається використання IN для того, щоб виконати такий же підзапит. Наприклад, запит

SELECT *

FROM PREDMET WHERE PREDMET.TNUM IN (SELECT TEACHERS.TNUM FROM TEACHERS WHERE TEACHERS. TEAM

BETWEEN 'И' AND 'С');

Висновок цього запиту такої:

PNUM PNAME TNUM HOURS COURS

	Хімія
	Математика
	Економіка

Такий запит набагато зрозуміліше, ніж виконаний за допомогою об'єднання, хоча результат, що дає такий же:

SELECT PREDMET. PNUM, PREDMET.PNAME, TEACHERS.TNUM, PREDME Т.HOURS, PREDMET.COURS

FROM PREDMET, TEACHERS

WHERE TEACHERS. TNUM = PREDMET. TNUM

AND TEACHERS.TFAM BETWEEN 'И' AND 'C1;

Узагалі говорячи швидкість виконання того чи іншого запиту, наприклад, як у розглянутому випадку, залежить від реалізації тієї СУБД, що для цього використовується. Вище вже піднімалася проблематика, зв'язана з роботою оптимізатора запитів, що намагається знайти найбільш ефективний спосіб виконання. Традиційно гарний оптимізатор найчастіше перетворить варіант об'єднання в підзапит, що звичайно виконується швидше.

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

SELECT *

FROM PREDMET WHERE TNUM IN (SELECT TNUM

FROM TEACHERS

WHERE TFAM = 'Никулина');

Таким чином, підзапиты завжди визначають одиночні стовпці - це обов'язково, оскільки обраний висновок порівнюється з одиночним значенням. Підтвердженням цьому є те, що команда SELECT * не може використовуватися в підзапите. У підзапите допускається використовувати вираження, засноване на поле, а не просто саме поле, у пропозиції SELECT. Це може бути виконане за допомогою реляційних операторів при використанні IN. Прикладом може служити наступний запит:

SELECT *

FROM PREDMET WHERE PNUM =

(SELECT PNUM-1 FROM PREDMET WHERE PNAME = 'Філософія');

Висновок для цього запиту такої:

PNUM PNAME TNUM HOURS COURS

2003 Математика 4003 68 1

Цей запит знаходить інформацію про навчальний предмет, код якого на 1 менше коду філософії. Зрозуміло, поле PNUM не повинне містити повторюваних значень, інакше запит викликає помилку.

Можна також використовувати підзапити усередині пропозиції HAVING. Ці підзапиты можуть використовувати свої власні агрегатні функції, якщо вони не роблять численних значень, чи використовувати GROUP BY чи HAVING. Наприклад, розглянемо запит:

SELECT OCENKA, COUNT (DISTINCT SNUM) FROM USP GROUP BY OCENKA HAVING OCENKA > =

(SELECT AVG (OCENKA) FROM USP

WHERE PNUM = 2003);

Висновок для цього запиту наступний:

OCENKA

5 2

4 2

Даний запит підраховує кількість студентів з оцінками вище середньої, чим по дисципліні з PNUM = 2003.

Тепер кілька слів про об'єднання запитів і використанні при цьому підзапитов. Операція, що буває часто корисне - це об'єднання з двох запитів, у якому другий запит вибирає рядка, виключені першим. Найбільше часто це роблять для того, щоб не виключати рядка, що не задовольнили предикату при об'єднанні таблиць. Цей процес прийнятий називати зовнішнім об'єднанням.

Припустимо, що деякі зі студентів ще не одержали оцінку, однак уже внесені в таблицю USP. Наприклад, у цю таблицю доданий запис {1006. NULL. NULL, 3416, NULL}. Якщо виникає необхідність у перегляді успішності студентів по дисципліні, не з огляду на тех. хто ще не одержав оцінку. Цього можна досягти, формуючи об'єднання з двох запитів, один із яких виконує об'єднання, а іншої вибирає студентів з NULL значеннями поля OCENKA. Цей останній запит повинний уставляти повідомлення в поля, що відповідають полю PNAME. і значення 0 у поле OCENKA у першому запиті. Як було розглянуто раніше, можна вставляти текстові рядки у висновок, щоб ідентифікувати запит, що вивів даний рядок Використання цієї методики в зовнішнім об'єднанні дає можливість застосовувати предикати для класифікації, а не для виключення Наступний запит виконує ці дії:

SELECT USP.SNUM, STUDENTS.SFAM,

PREDMET.PNAME, USP.OCENKA FROM USP, STUDENTS, PREDMET

WHERE USP.SNUM = STUDENTS.SNUM

AND USP.PNUM = PREDMET.PNUM UNION SELECT USP.SNUM, STUDENTS.SFAM,

'НЕМАЄ ', 0 FROM USP, STUDENTS

WHERE USP.SNUM = STUDENTS.SNUM AND NOT USP.OCENKA = ANY (SELECT OCENKA

FROM USP) ORDER BY 2 ASC;

Висновок цього запиту наступний:3414 Гриценко Економіка 3

3416 Нагорний Філософія 5

3416 Нагорний НЕМАЄ 0

3412 Поляків Фізика 5

3412 Поляків Математика 4

3413 Старова Математика 4

Зверніть увагу на те. що рядок 'НЕМАЄ ' була доповнена пробілами, щоб одержати збіг поля PNAME по довжині. Другий запит вибирає навіть ті рядки, що були виключені першими.

Кілька слів про використаного оператора ANY. більш докладно про яке будемо говорити нижче. Оператор ANY бере всі значення, виведені підзапитом (для нашого випадку - це всі значення, OCENKA у таблиці USP), і оцінює їхній як вірні, якщо кожне з них дорівнює значенню оцінки поточної запису зовнішнього запиту.

На закінчення розмови про вкладені запити, поговоримо про так званих співвіднесений підзапитах. Коли використовуються підзапиты, у SQL мається можливість звернутися до внутрішнього запиту' таблиці в пропозиції зовнішнього запиту FROM, за допомогою співвіднесеного підзапита. При цьому підзапит виконується неодноразово, по одному разі для кожного запису таблиці основного запиту. З використанням співвіднесеного підзапита можна знайти дані на всіх студентів, що одержували оцінки 10/06/1999:

SELECT *

FROM STUDENTS FIRST WHERE 10/06/1999 IN (SELECT UDATE

FROM USP SECOND

WHERE FIRST.SNUM = SECOND.SNUM);

Висновок цього запиту такий:

SNUM SFAM SIMA SOTCH STIP

3412 Поляків Анатолій Олексійович 25.50

3413 Старова Любов Михайлівна 17.00

У цьому прикладі FIRST і SECOND - псевдоніми таблиць, при цьому виходить, що значення в поле SNUM зовнішнього запиту міняється, а виходить, внутрішній запит повинний виконуватися окремо для кожного рядка зовнішнього запиту.

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

• вибір рядка з таблиці в зовнішньому запиті - це поточний рядок:

• збереження значення поточної рядка в псевдонімі, ім'я якого визначено в пропозиції FROM зовнішнього запиту:

• виконання підзапита, при цьому скрізь, де знайдений псевдонім із зовнішнього запиту, використовується значення з поточної рядка (це прийнято називати зовнішнім посиланням);

• оцінка предиката зовнішнього запиту на основі результатів підзапита:

• описана вище послідовність повторюється для наступної рядка з таблиці зовнішнього запиту, і так доти, поки всі рядки не будуть перевірені. Узагалі говорячи, останній приклад міг бути реалізований, використовуючи об'єднання наступного виду:

SELECT STUDENTS.SNUM, STUDENTS.SFAM, STUDENTS.SIMA, STUDENTS.SOTCH, STUDENTS.STIP FROM STUDENTS, USP

WHERE STUDENTS.SNUM = USP.SNUM AND USP.UDATE = 10/06/1999;

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

SELECT SNUM, SFAM

FROM STUDENTS FIRST WHERE 1 < (SELECT COUNT(*) FROM USP WHERE SNUM = FIRST. SNUM);

Висновок цього запиту приведений нижче:

SNUM SFAM

3412 Поляків

Варто звернути увагу на те. що пропозиція FROM підзапита в цьому прикладі не використовує псевдонім. При відсутності імені чи таблиці префікса псевдоніма. SQL може для початку прийняти, що будь-яке поле виводиться з таблиці з ім'ям, зазначеним у пропозиції FROM поточного запиту. Якщо поле з цим ім'ям відсутнє у цій таблиці. SQL буде перевіряти зовнішні запити. Саме з цієї причини префікс імені таблиці звичайно необхідний у співвіднесених підзапитах - для скасування цього припущення. Псевдоніми також часто необхідні для того, щоб дати можливість посилатися до тієї ж самої таблиці у внутрішньому і зовнішньому запиті без якої-небудь неоднозначності.

Часто співвіднесений підзапит використовують на основі тієї ж самої таблиці, що й основний запит. Це дає можливість витягти складні форми інформації. Наприклад, за допомогою наступного запиту можна знайти всі оцінки по дисципліні зі значеннями, вище середньої по цій же дисципліні:

SELECT *

FROM USP FIRST WHERE OCENKA > (SELECT AVG (OCENKA) FROM USP SECOND WHERE SECOND.PNUM = FIRST.PNUM);

У даному випадку висновку в запиту не буде, тому що в таблиці USP немає записів для студентів, що мають по якому-небудь навчальному предметі оцінку вище середньої. Якщо ж умова зовнішнього предиката змінити на >=, то як результат будуть виведені всі дані таблиці успішності.

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

SELECT UDATE, AVG (OCENKA) FROM USP FIRST GROUP BY UDATE HAVING AVG (OCENKA) >=

(SELECT MIN(OCENKA) +0.5 FROM USP SECOND WHERE FIRST.UDATE = SECOND.UDATE);

Результат запиту буде такою:

UDATE

10/06/1999 4.5 12/06/1999 4.5

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

Співвіднесені підзапиты по своїй суті близькі до об'єднань - обидві конструкції включають перевірку кожного запису однієї таблиці з кожним записом інший чи з псевдонімом з тієї ж таблиці, при цьому більшість операцій у них схожі. З цієї причини докладно зупинятися на цьому матеріалі не будемо.

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

Дата добавления: 2014-01-04