Корельовані підзапити

У пропозиціях FROM або WHERE зовнішнього запиту можливе звернення до внутрішнього підзапиту. Такі запити прийнято називати корельованими. У корельованому запиті підзапит виконується неодноразово, по одному разу для кожного рядка таблиці основного запиту.

Приклад використання оператора порівняння спільно з корельованим підзапитом.

Запит 2: Визначити П.І.Б. студентів, які здали іспити не гірше своїх консультантів.

Рішення (3):

SELECT DISTINCT № Студента = СТУДЕНТ.ID_Stud, ПІБ = СПрізв,

FROM СТУДЕНТ, УСПІШНІСТЬ AS У1

WHERE № Студента = У1.ID_Stud AND У1.Оцінка> =

(SELECT У2.Оцінка FROM УСПІШНІСТЬ AS У2

WHERE У2.ID_Stud = Консультант AND У1.Семестр = У2.Семестр)

Рішення (4):

SELECT DISTINCT № Студента = СТУДЕНТ.ID_Stud, ПІБ = СПрізв,

FROM СТУДЕНТ WHERE № Студента IN

(SELECT У1.ID_Stud FROM УСПІШНІСТЬ AS У1

WHERE У1.Оцінка> =

(SELECT У2.Оцінка FROM УСПІЩНІСТЬ AS У2

WHERE У2.ID_Stud = Консультант AND У1. Семестр = У2. Семестр)) Зверніть увагу, що в останньому запиті самий вкладений підзапит використовує посилання на поле самого зовнішнього підзапиту.

Наступний приклад демонструє використання групових операцій в корельованих ПЗ.

Запит: У кого зі студентів оцінка хоча б з одного предмета вище середньої оцінки з цього предмету.

Рішення:

SELECT У1.ID_Stud FROM УСПІШНІСТЬ AS У1

WHERE У1.Оцінка>

(SELECT AVG ^ 2. Оцінка)

FROM УСПІШНІСТЬ AS У2

WHERE У1.ID_Subj = У2.ID_Subj)

Квантор існування в підзапитах

У реляційному обчисленні квантор існування був введений для операції з'єднання. Як ми вже переконалися, SQL дозволяє виконувати з'єднання таблиць декількома більш зручними способами. Однак є ряд запитів, в яких використання квантора загальності здається очевидним. Ці запити зазвичай містять заперечення. Більшість з них має еквівалентне рішення за допомогою оператора IN. Розглянемо наступний запит.

Запит 4: Визначити номери студентів, які не здавали курс КЗ.

Рішення - НЕ РІШЕННЯ:

SELECT DISTINCT ID_Stud FROM УСПІШНІСТЬ WHERE ID_Subj <> 'КЗ'

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

Запит 4-1: Визначити номери студентів, для яких НЕ ІСНУЄ запису в таблиці успішності про здачу курсу КЗ.

Таке формулювання природним чином вводить квантор існування [NOT] EXIST (SubQuery). У цьому випадку результат підзапиту (SubQuery) – множина кортежів. Якщо ця множина не порожня, то значення предиката EXISTS стає TRUE, а предиката NOT EXISTS - FALSE, і навпаки, якщо результат підзапиту - порожня множина.

Рішення:

SELECT CT1. ID_Stud FROM СТУДЕНТ AS СТ1

WHERE NOT EXISTS

(SELECT * FROM УСПІШНІСТЬAS У1 WHERE CT1.ID_Stud = У1.ID_Stud AND ID_Subj = 'К3')

Оскільки нас цікавить тільки, чи містить підзапит записи чи ні, то в цільовому списку оператора SELECT підзапиту вказано «*», а не імена конкретних полів. Таким чином, для кожного студента з таблиці СТУДЕНТ проглядається таблиця успішності і визначається, чи зустрічається ID_Stud цього студента спільно зі значенням поля ID_Subj рівним курсу 'КЗ'. Якщо такого запису в таблиці успішності немає, то ID_Stud вноситься в результуючу таблицю. Таким чином, треба виконати перегляд таблиці успішності стільки разів, скільки зареєстровано студентів у таблиці СТУДЕНТ. Дуже витратне рішення. Відповідь на запит можна отримати і більш оптимальним способом за допомогою некорельованого підзапиту. Обчислимо множину ID_Stud студентів, які здавали курс 'КЗ'. Для кожного студента з таблиці СТУДЕНТ з'ясуємо, чи належить його ID_Stud цій множині.

Рішення (2):

SELECT ID_Stud FROM СТУДЕНТ

WHERE.ID_Stud NOT IN (SELECT ID_Stud

FROM УСПІШНІСТЬ WHERE ID_Subj = 'K3')

Однак існують запити, які можуть бути виконані ТІЛЬКИ за допомогою квантора існування. Як не дивно, це запити на розподіл (або квантор загальності). Справа в тому, що в перших реалізаціях SQL і навіть стандарті SQL-89 не передбачалося квантора загальності FORALL і запит на квантор загальності «виконано для всіх (FOR ALL) P (a)» замінювався згідно з правилами логіки квантором існування і двома запереченнями - «не існує (NOT EXISTS) не P (a) ». Проілюструємо сказане на наступному прикладі.

Запит 5: Перерахувати П.І.Б. таких студентів, які здали всі іспити.

Переформулюємо питання з квантором загальності в більш строгу формі.

Запит 5-1: Перерахувати студентів таких, що для ВСІХ дисциплін з таблиці КУРС існують відповідні записи в таблицю успішності з даними студентом.

Переформулюємо питання з квантором існування.

Запит 5-2: Перерахувати П.І.Б. таких студентів, що НЕ ІСНУЄ дисципліни в таблиці КУРС, для якої НЕ існує відповідного запису в таблиці успішності з даним студентом.

Рішення:

SELECT ID_Stud, СПрізв FROM СТУДЕНТ AS СТ

WHERE NOT EXISTS (SELECT ID_Subj FROM КУРС

WHERE NOT EXISTS (SELECT *

FROM УСПІШНІСТЬ AS У

WHERE У.ID_Subj = KУРС.ID_Subj AND У.ID_Stud = СТ.ІD_Stud))