Після ознайомлення з роботою підзапитів, необхідно поговорити про деяких спеціальних операторів, які завжди беруть підзапити як аргументи - це 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 й об'єднання, удосконалимо останній приклад таким чином, щоб виводилася більше докладна інформація про студентів:
SELECT DISTINCT FIRST.SNUM, FIRST.SFAM,
FIRST.SIMA, FIRST.SOTCH
FROM STUDENTS FIRST, USP SECOND
WHERE EXISTS
(SELECT *
FROM USP THIRD
WHERE SECOND.SNUM = THIRD.SNUM
AND SECOND.PNUM < > THIRD. PNUM)
AND FIRST.SNUM = SECOND.SNUM;
У результаті буде отримано:
SNUM SFAM SIMA SOTCH
-------------------------------------
3412 Поляков Анатолій Олексійович
У цьому прикладі внутрішній запит, як й у попередньому варіанті, повідомляє, що студент одержав кілька оцінок. Зовнішній запит - це об'єднання таблиць успішності й студентів. нова пропозиція, Що З'явилася, основного предиката AND FJRST.SNUM = SECOND.SNUM оцінюється на тім же самому рівні, що й пропозиція EXISTS, тому що це елемент предиката самого об'єднання двох таблиць із зовнішнього запиту.
З обліком того, що EXISTS може працювати в комбінації з булевскими операторами, найбільш очевидним способом його використання є сполучення з оператором NOT. Наприклад, для одержання інформації про студентів, що мають тільки одну оцінку, можна скористатися наступним запитом:
SELECT DISTINCT FIRST.SNUM, FIRST.SFAM,
FIRST.SIMA, FIRST.SOICH
FROM STUDENTS FIRST, USP SECOND
WHERE NOT EXISTS
(SELECT *
FROM USP THIRD
WHERE SECOND.SNUM = THIRD.SNUM
AND SECOND.PNUM < > THIRD. PNUM)
AND FIRST.SNUM = SECOND.SNUM;
Висновок цього запиту наведений нижче:
SNUM SFAM SIMA SOTCH
--------------------------------
3413 Старова Любов Михайлівна
3414 Гриценко Володимир Миколайович
3416 Нагорний Євген Васильович
Важливою властивістю EXISTS є те, що він не може взяти агрегатну функцію в подзапросе, тому що якщо агрегатна функція знайшла які-небудь записи для операцій з ними, то EXISTS буде вірний у кожному разі. Виходом з такої ситуації є використання вкладеного подзапроса в подзапросе, у предикаті якого є присутнім EXISTS. Це дозволяє ретельно структурировать запити, роблячи їх більше зрозумілими. Повертаючись наприклад зі знаходженням інформації про студентів, що мають більше однієї оцінки, можна запропонувати такий варіант запиту:
SELECT *
FROM STUDENTS FIRST
WHERE EXISTS
(SELECT *
FROM USP SECOND
WHERE FIRST.SNUM = SECOND.SNUM
AND 1 <
(SELECT COUNT (*)
FROM USP
WHERE USP.SNUM = SECOND.SNUM));
У результаті буде отримано:
SNUM SFAM SIMA SOTCH STIP
-----------------------------------------
3412 Поляков Анатолій Олексійович 25.50
Така конструкція працює в такий спосіб: зовнішній запит вибирає з таблиці студентів STUDENTS поточний рядок і виконує подзапросы. Для поточного рядка середнім запитом береться у відповідність кожен рядок з таблиці успішності USP. Щораз, коли виявляється інформація в середньому запиті, що збігається по номері студентського квитка з поточним рядком у зовнішньому запиті, SQL повинен розглядати внутрішній подзапрос для того, щоб визначити, чи буде предикат середнього запиту вірний. Внутрішній запит уважає кількість оцінок поточного студента, і, якщо це число більше, ніж 1, робить предикат середнього запиту вірним. Це. У свою чергу, робить EXISTS-предикат зовнішнього запиту вірним для поточного рядка таблиці.
Таким чином, незважаючи на гадану простоту, EXISTS може виявитися одним із самих важких з погляду використання операторів в SQL. Існують ще три спеціальних оператори, ориентируемых на подзапросы - це ANY, ALL й SOME, що нагадують EXISTS, які сприймають подзапросы, як аргументи. Однак ці оператори відрізняються від EXISTS тим, що використаються разом з реляционными операторами, аналогічно IN у подзапросах
Оператори SOME й ANY досить часто взаємозамінні, і в наших прикладах будуть працювати однаково. Розходження в термінології полягає в тому, щоб дозволити користувачам уживати той термін, що найбільш однозначний. Тому все сказане відносно ANY рівною мірою ставиться й до SOME.
Розглянемо наступний спосіб знаходження студентів, які одержували оцінки по різних навчальних предметах:
SELECT *
FROM STUDENTS .
WHERE SNUM = ANY
(SELECT SNUM
FROM USP);
Висновок цього запиту наведений нижче:
SNUM SFAM SIMA. SOTCH STIP
3412 Поляков Анатолій Олексійович 25.50
3413 Старова Любов Михайлівна 17.00
3414 Гриценко Володимир Миколайович 0.00 3416 Нагорний Євген Васильович 25.50
Оператор ANY бере всі значення, виведені подзапросом, тобто в прикладі - всі значення поля SNUM, і оцінює їх як вірні, якщо кожне з них рівняється номеру студентського квитка поточного рядка зовнішнього запиту. Звідси треба, що подзапрос повинен вибирати значення такого ж типу, як і ті, які рівняються в основному предикаті. У цьому його відмінність від EXISTS, що просто визначає, чи робить висновок подзапрос чи ні, фактично не використовуючи ці результати.
Загалом кажучи, у ряді випадків допускається використання операторів IN або EXISTS замість оператора ANY. Наприклад, використовуючи, оператор IN, можна створити запит, аналогічний попередній:
SELECT *
FROM STUDENTS
WHERE SNUM IN
(SELECT SNUM
FROM USP);
Висновок цього запиту такий, як у попередньому прикладі, по цьомуе приводити його не будемо.
Цікавий той факт, що оператор ANY може використати інші реляционные оператори, за винятком рівняється, і. таким чином, реалізовувати порівняння, які недоступні з IN. Наприклад, можна виконати наступний запит:
SELECT PNAME
FROM PREDMET
WHERE HOURS > ANY
(SELECT HOURS
FROM PREDMET);
Результат цього запиту наступний:
PNAME
---------
Фізика
Хімія
Математика
Тут виводяться назва навчальних предметів, для яких існує хоча б одна навчальна дисципліна з кількістю годин, меншим, чим у поточної.
У принципі, цей же запит може бути реалізований з використанням EXISTS, у такий спосіб:
SELECT PNAME
FROM PREDMET FIRST
WHERE EXISTS
(SELECT *
FROM PREDMET SECOND
WHERE FIRST.HOURS > SECOND.HOURS);
У кожному разі запит, що може бути реалізований з ANY й ALL, може бути також сформульований з EXISTS, але не навпаки. Правда, у ряді випадків, через розходження в обробці NULL значень, варіант із EXISTS не абсолютно ідентичний, чим при використанні ANY, тому необхідно в таких випадках застосовувати команду IS NULL.
Основна перевага у використанні ANY й ALL у порівнянні з EXISTS полягає в тім, що останній вимагає співвіднесених подзапросов, часом складних для розуміння. Крім того, подзапросы з ANY або ALL можуть виконуватися один раз і мати висновок, використовуваний для визначення предиката для кожного рядка основного запиту, a EXISTS вимагає, щоб весь подзапрос повторно виконувався для кожного рядка основного запиту.
Оператор ALL працює таким чином, що предикат є вірним, якщо кожне значення, обране подзапросом, задовольняє умові в предикаті зовнішнього запиту.
Наприклад, якщо необхідно вивести тільки тих студентів, чиї оцінки вище або рівні отриманими 10/06/1999, то можна скористатися наступним запитом:
SELECT *
FROM USP
WHERE OCENKA >= ALL
(SELECT OCENKA
FROM USP
WHERE UDATE = 10/06/1999);
Висновок цього запиту такий:
UNUM OCENKA UDATE SNUM PNUM
--------------------------------------------
1001 5 10/06/1999 3412 2001
1005 5 12/06/1999 3416 2004
Запит працює так: подзапрос перевіряє значення оцінок за 10/06/1999 для всіх студентів. Потім він знаходить студентів з оцінкою більшої або рівної в порівнянні з оцінками за 10/06/1999. Оскільки найвища оцінка в цей день - відмінно, те вибираються тільки записи з оцінкою 5.
Як й у випадку з ANY, допускається використати EXISTS для альтернативного формулювання такого ж запиту:
SELECT *
FROM USP FIRST
WHERE EXISTS
(SELECT *
FROM USP SECOND
WHERE FIRST.OCENKA >= SECOND.OCENKA
AND SECOND.UDATE = 10/06/1999);
Основне застосування ALL знаходить зі знаком нерівності, тому що предикат може бути вірним, якщо порівнюване значення дорівнює для всіх, тобто всі записи, фактично, ідентичні. Наприклад, запит
SELECT *
FROM USP
WHERE OCENKA = ALL
(SELECT OCENKA
FROM USP
WHERE UDATE = 10/06/1999);
є припустимим, але висновок буде тільки у випадку, якщо всі оцінки за 10/06/1999 виявляться ідентичними. Імовірно, не дуже добре використати запити, які працюють тільки в певних ситуаціях.
Однак ALL набагато більш ефективно використається зі знаком нерівності, тобто з оператором < >. При цьому обов'язково треба враховувати, що якщо подзапрос повертає багато різних значень, те це означає нерівність будь-якому результату. Інакше кажучи, предикат вірний, якщо дане значення не знайдене серед результатів подзапроса. Розглянемо такий запит:
SELECT *
FROM USP
WHERE OCENKA < > ALL
(SELECT OCENKA
FROM USP
WHERE UDATE = 10/06/1999);
Висновок цього запиту такий:
UNUM OCENKA UDATE SNUM PNUM
--------------------------------------
1003 3 11/06/1999 3414 2005
Розглянутий запит зробить наступне: подзапрос вибере всі оцінки за 10/06/1999 - це будуть 5 й 4. Після цього, основний запит виведе всі записи з оцінкою, що не збігається з жодною з них. Аналогічний запит можна сформулювати з використання оператора NOT IN:
SELECT *
FROM USP
WHERE OCENKA NOT IN
(SELECT OCENKA
FROM USP
WHERE UDATE = 10/06/1999);
або використовуючи, оператор ANY:
SELECT *
FROM USP
WHERE NOT OCENKA = ANY
(SELECT OCENKA
FROM USP
WHERE UDATE = 10/06/1999);
Висновок цих запитів такий же, як і першого. Таким чином, в SQL дати запит на порівняння з використанням команди ANY для набору значень - те ж саме, що зробити порівняння з будь-яким окремим значенням з набору. Якщо запросити значення за допомогою команди ALL, не рівні набору значень, то це т же саме, що визначити факт відсутності цього значення у всьому наборі.
Як уже було сказано, є деякі розходження між EXISTS й операторами ANY й ALL з погляду роботи з невідомими й відсутніми даними. Крім того, важливе розходження між ALL й ANY - це спосіб дії в ситуації, коли подзапрос не повертає ніяких значень. Загалом кажучи, у всіх випадках, коли припустимий подзапрос не робить висновку, ALL автоматично повертає значення ІСТИНА, a ANY -НЕПРАВДА. Наприклад, це означає, що запит:
SELECT *
FROM USP
WHERE OCENKA >= ANY
(SELECT OCENKA
FROM USP
WHERE UDATE = 10/06/2000);
не зробить ніякого висновку, у той час як запит
SELECT *
FROM USP
WHERE OCENKA >= ALL
(SELECT OCENKA
FROM USP
WHERE UDATE = 10/06/2000) ;
виведе всю таблицю USP. Дійсно, якщо немає ніяких оцінок за 10/06/2000 те, природно, жодне із цих порівнянь не має значення.
NULL значення мають свої особливості при їхній обробці цими операторами. Коли SQL порівнює два значення в предикаті, одне йз яких NULL, мабуть, результат цього невідомий. Невідомий предикат, аналогічно невірному, є причиною того, що запис не вибирається, однак працювати він буде трохи інакше, залежно від того, використається ALL або ANY. Розглянемо попередній приклад з ANY і такий запит:
SELECT *
FROM USP FIRST
WHERE EXISTS
(SELECT *
FROM USP SECOND
WHERE FIRST.OCENKA > = SECOND.OCENKA
AND SECOND.UDATE = 10/06/1999);
Поки NULL значень у таблиці ні, обидва запити будуть поводитися однаково. Але у випадку появи NULL значення в поле OCENKA таблиці успішності, у варіанті з ANY значення предиката при обробці цього рядка стане невідомим і висновку для неї не буде в кожному разі, однак інші рядки будуть оброблятися звичайним образом. У варіанті з EXISTS, значення NULL використається в предикаті подзапроса, роблячи його невідомим у кожному випадку, а це означає, що подзапрос не буде робити ніяких значень, і запит не зробить висновку взагалі. Це є важливим чинником на користь використання варіанта з ANY.
Таким чином, подзапросы не найпростішому, що є в SQL, однак вони є одним з наймогутніших засобів цього язика. Як правило, допускається виконувати той же запит з подзапросами декількома способами, і, в остаточному підсумку, користувачеві вирішувати, яким варіантом скористатися.
