В самому загальному випадку, запити можуть управляти іншими запитами - це робиться шляхом розміщення запиту всередину предиката іншого, який використовує висновок внутрішнього запиту для встановлення вірного або невірного значення предиката. Наприклад, потрібна інформація про успішність студента з прізвищем Поляків, проте через які-небудь причини невідомий номер цього студента. Тоді необхідно витягати цей номер з таблиці з даними про студентів, і після цього застосовувати результат до таблиці успішності. Це можна реалізувати шляхом наступної конструкції:
SELECT * FROM USP
WHERE SNUM = (SELECT SNUM
FROM STUDENTS
WHERE SFAM = 'Поляков');
Результат цього запиту буде наступним:
UNUM OCENKA UDATE SNUM PNUM
1003- 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. TNTJM FROM TEACHERS WHERE TEACHERS.TFAM BETWEEN 'И' AND ' С' ) ;
Висновок цього запиту такий:
PNUM PNAME TNUM HOURS COURS
2002 Хімія 4002 68 1
2003 Математика 4003 68 1 2005 Економіка 4004 17 3
Такий запит набагато зрозумілість, ніж виконаний за допомогою об'єднання, хоча даючий такий же результат:
SELECT PREDMET.PNUM, PREDMET.PNAME, TEACHERS.TNUM, PREDMET.HOURS, PREDMET.COURS FROM PREDMET, TEACHERS WHERE TEACHERS.TNUM = PREDMET.TNUM
AND TEACHERS.TFAM BETWEEN 'И1 AND 'С';
Взагалі кажучи швидкість виконання того або іншого запиту, наприклад, як в розглянутому випадку, залежить від реалізації тієї СУБД, яка для цього використовується. Вище вже підіймалася проблематика, пов'язана з роботою оптимізатора запитів, який намагається знайти найбільш ефективний спосіб виконання. Традиційно добрий оптимізатор гущавині всього перетворить варіант об'єднання в підзапит, який звичайно виконується швидше.
І ще один цікавий момент: в будь-якій ситуації, де застосовується реляційний оператор рівності (=), можна використати 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
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 Нагірний НЕМАЄ
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. дозволяючи виконати більшу кількість функцій.
Використовування операторів EXISTS, ANY, ALL, І SOME
Після ознайомлення з роботою підзапитів, необхідно поговорити про деяких спеціальних операторів, які завжди беруть підзапити в якості аргументів, - це EXISTS, ANY. ALL. і SOME.
Оператор EXISTS використовується для вказівки предикату на те, щоб виробляти або не виробляти висновок в підзапиті, при цьому EXISTS дає в якості результату значення ІСТИНА або БРЕХНЯ. Це у свою чергу означає, що він може працювати в предикаті або в комбінації з іншими булевими виразами - AND, OR, і NOT. Іншими словами, EXISTS бере підзапит як аргумент і оцінює його як істинний, якщо він здійснює будь-який висновок, або як помилковий, якщо він не робить це. Наприклад, можна вирішити, чи витягати дані з таблиці успеваемости^ якщо в ній присутні відмінні оцінки. Це реалізується таким чином:
SELECT *
FROM USP
WHERE USP.OCENKA = 5
AND EXISTS
(SELECT *
FROM USP
WHERE USP.OCENKA = 5);
Результати запиту будуть наступними:
UNUM OCENKA UDATE SNUM PNUM
1001 5 10/06/1999 3412 2001
1005 5 12/06/1999 3416 2004
При цьому внутрішній запит вибирає всі дані для всіх студентів, що отримали оцінку 5. Оператор EXISTS в зовнішньому предикаті відзначає, що деякий висновок в підзапиті мав місце, значить, це робить предикат вірним. Підзапит був виконаний тільки один раз для всього зовнішнього запиту, і, отже, має одне значення у всіх випадках - з цієї причини EXISTS робить предикат вірним або невірним для всіх рядків відразу. В останньому прикладі, якщо строго говорити, EXISTS повинен бути встановлений так, щоб вибрати тільки один стовпець. Проте він вибирає всі стовпці у вкладеній пропозиції SELECT *. Це не викликає помилки, оскільки при виборі EXISTS як одного стовпця, так і всіх, стовбців, він просто помічає виконання висновку з підзапиту, а отримані значення не використовує.
В співвіднесеному підзапиті, пропозиція EXISTS оцінюється окремо для кожного рядка таблиці, ім'я якого вказане в зовнішньому запиті, точно так, як і інші оператори предиката, коли використовується співвіднесений підзапит. Це дає можливість використати EXISTS як предикат, який генерує різні значення для кожного запису таблиці, вказаної в основному запиті. Отже, інформація з внутрішнього запиту буде зберігатися. Наприклад, за допомогою наступного запиту виведемо інформацію про студентів, які мають декілька оцінок:
SELECT DISTINCT SNUM
FROM USP FIRST
WHERE EXISTS
(SELECT *
FROM USP SECOND
WHERE SECOND.SNUM = FIRST.SNUM
AND SECOND.PNUM < > FIRST. PNUM);
Результат запиту наступний:
SNUM
Тут для кожного поточного рядка зовнішнього запиту внутрішній запит знаходить записи, які співпадають із значенням поля номера студента SNUM, але не співпадають із значенням коду предмета PNUM. Якщо будь-які такі рядки будуть знайдені підзапитом, це означає, що є, принаймні, дві оцінки, отримані поточним студентом. Якби в зовнішньому запиті DISTINCT не був вказаний, то кожний із студентів, має декілька оцінок, був би вибраний стільки раз, скільки в нього оцінок.
Для ілюстрації можливості використовування комбінації з EXISTS і об'єднання, удосконалимо останній приклад так, щоб виводилася більш докладна інформація про студентів:
