Transact-sql, пов’язані підзапити

Підзапити ми коротко розглянули в статті Підзапити і тимчасові таблиці. Тут ми більш детально розглянемо пов'язаний тип підзапитів. Підзапит називається пов'язаним (correlated). якщо будь-які значення вкладеного запиту залежать від зовнішнього запиту. У прикладі нижче показано використання пов'язаного підзапит:

У цьому прикладі вкладений запит повинен логічно виконатися кілька разів, оскільки він містить стовпець Id, який належить таблиці Employee в зовнішньому запиті, і значення стовпця Id змінюється кожен раз, коли перевіряється інший рядок таблиці Employee в зовнішньому запиті.

Давайте простежимо, як система може виконувати запит в цьому прикладі. Спочатку система вибирає перший рядок таблиці Employee (для зовнішнього запиту) і порівнює табельний номер співробітника в цьому стовпці (25348) зі значеннями стовпчика Works_on.EmpId вкладеного запиту. Оскільки для цього співробітника є тільки одне значення ProjectNumber рівне p2, вкладений запит повертає значення p2. Це єдине значення результуючого набору вкладеного запиту не дорівнює значенню p3 зовнішнього запиту, умова зовнішнього запиту (WHERE 'p3' IN.) Не задовольняється і, отже, зовнішній запит не повертає ніяких рядків для цього співробітника.

Далі система бере наступний рядок таблиці Employee і знову порівнює номера співробітників в обох таблицях. Для цього рядка в таблиці Works_on є два рядки, для яких значення ProjectNumber одно p1 і p3 відповідно. Отже, вкладений запит повертає результат p1 і p3. Значення одного з елементів цього результуючого набору одно константі p3, тому умова задовольняється, і відображається відповідне значення другого рядка стовпця LastName ( 'Фролов'). Такий же обробці піддаються всі інші рядки таблиці Employee, і в кінцевому результаті повертається набір з трьох рядків.

У наступній таблиці наведені додаткові приклади по зв'язаних підзапитів.

Підзапити і функція EXISTS

Функція EXISTS приймає вкладений запит в якості аргументу і повертає значення false, якщо вкладений запит не повертає рядків і значення true в іншому випадку. Розглянемо роботу цієї функції на декількох прикладах, починаючи з наступного прикладу:

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

Давайте простежимо, як Database Engine може обробляти запит в цьому прикладі. Спочатку зовнішній запит розглядає перший рядок таблиці Employee (співробітник Фролов). Далі функція EXISTS визначає, чи є в таблиці Works_on рядки, чиї номери співробітників збігаються з номером співробітника в поточному рядку в зовнішньому запиті і чий ProjectNumber дорівнює p1. Оскільки співробітник Фролов не працює над проектом p1, вкладений запит повертає порожній набір, внаслідок чого функція EXISTS повертає значення false. Таким чином, співробітник Фролов не включається в кінцевий результуючий набір. Цьому процесу піддаються всі рядки таблиці Employee, після чого виводиться кінцевий результуючий набір.

У прикладі нижче показано використання функції NOT EXISTS.

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

Список вибору інструкції SELECT в зовнішньому запиті з функцією EXISTS не обов'язково повинен бути в формі SELECT *, як в попередньому прикладі. Можна використовувати альтернативну форму SELECT colum_list, де column_list представляє список з одного або декількох стовпців таблиці. Обидві форми рівнозначні, бо функція EXISTS тільки перевіряє на наявність (або відсутність) рядків в результуючому наборі. З цієї причини в даному випадку правильніше використовувати форму SELECT *.

Що використовувати, з'єднання або підзапити?

Майже всі інструкції SELECT для з'єднання таблиці за допомогою оператора з'єднання JOIN можна замінити інструкціями підзапиту і навпаки. Конструкція інструкції SELECT з використанням оператора з'єднання часто більш зручно Новомосковскемая і легше розуміється, а також може допомогти компоненту Database Engine знайти більш ефективну стратегію для вибірки необхідних даних. Але деякі завдання легше піддаються вирішенню за допомогою підзапитів, а інші за допомогою з'єднань.

переваги підзапитів

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

У цьому прикладі відбувається вибірка табельних номерів працівників і дат початку їх роботи над проектом (EnterDate) для всіх співробітників, у яких дата початку роботи дорівнює самій ранній даті. Вирішити цю задачу за допомогою з'єднання буде нелегко, оскільки для цього потрібно помістити агрегатную функцію в реченні WHERE, а це не дозволяється. (Це завдання можна вирішити, використовуючи два окремих запиту по відношенні до таблиці Works_on.)

переваги з'єднань

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