В кінці Глави 9 ми говорили, що запити можуть управляти іншими запитами. У цьому розділі ви дізнаєтеся, як це робиться (здебільшого) шляхом приміщення запиту всередину предиката іншого запиту і використання виведення внутрішнього запиту в правильному чи неправильному умови предиката.
Ви зможете з'ясувати, які види операторів можуть використовувати підзапити, і подивитися, як підзапити працюють із засобами SQL, такими як DISTINCT, з складовими функціями і виведеними виразами.
Ви дізнаєтеся, як використовувати підзапити з пропозицією HAVING, і отримаєте деякі настанови, як правильно використовувати підзапити.
До АК ПРАЦЮЄ підзапитів?
За допомогою SQL ви можете вкладати запити одного в одного. Зазвичай внутрішній запит генерує значення, яке перевіряється в предикате зовнішнього запиту, визначає, вірно воно чи ні. Наприклад, припустимо, що ми знаємо ім'я продавця: Motika, але не знаємо значення його поля snum і хочемо отримати всі замовлення з таблиці Замовлень. Ось спосіб зробити це (висновок показаний на Рис. 10.1):
Щоб оцінити зовнішній (основний) запит, SQL спочатку повинен оцінити внутрішній запит (або підзапит) всередині пропозиції WHERE. Він робить це так, як і повинен робити запит, який має єдину мету - відшукати через таблицю Продавців всі рядки, де поле sname дорівнює значенню Motika, а потім витягти значення поля snum цих рядків.
Єдиною знайденої рядком, природно, буде snum = 1004. Однак SQL не просто видає це значення, а поміщає його в предикат основного запиту замість самого підзапиту, так щоб предикат прочитав, що
Основний запит потім виконується як зазвичай з вищезгаданими результатами. Зрозуміло, підзапит повинен вибрати один, і тільки один, стовпець, а тип даних цього стовпця повинен збігатися з тим значенням, з яким він буде порівнюватися в предикате.
Часто, як показано вище, вибране поле і його значення матимуть однакові імена (в даному випадку snum), але це не обов'язково. Звичайно, якби ми вже знали номер продавця Motika, ми могли б просто надрукувати WHERE snum = 1004 і працювати далі з підзапитом в цілому, але це було б не так універсально. Цей же запит буде продовжувати працювати, навіть якщо номер Motika змінився, а за допомогою простої зміни імені в підзапиті ви можете використовувати його для чого завгодно.
З чення, ЯКІ підзапитів може виводити
Швидше за все, було б зручніше, щоб наш підзапит в попередньому прикладі повертав одне, і тільки одне, значення.
Маючи вибране поле snum "WHERE city =" London "замість" WHERE sname = 'Motika ", можна отримати кілька різних значень. Це може зробити в предикате основного запиту неможливим оцінку вірності чи невірності, і команда видасть помилку.
При використанні підзапитів в предиката, заснованих на реляційних операціях (рівняннях або нерівностях, як пояснено в Главі 4), ви повинні переконатися, що використовували підзапит, який буде видавати одну, і тільки одну, рядок виводу. Якщо ви використовуєте підзапит, який не виводить ніяких значень взагалі, команда не потерпить невдачі, але основний запит не виведе ніяких значень. Підзапити, які не виробляють ніякого висновку (або нульовий висновок), змушують розглядати предикат ні як вірний, ні як невірний, а як невідомий. Однак невідомий предикат має той же самий ефект, що і невірний: ніякі рядки не вибираються основним запитом (дивись в Главі 5 детальну інформацію про невідомого предикате).
Ось приклад поганий стратегії:
Оскільки ми маємо лише одного продавця в Barcelona - Rifkin, то підзапит буде вибирати одиночне значення snum, і, отже, буде прийнятий. Але це тільки в даному випадку. Більшість БД SQL мають численних користувачів, і, якщо інший користувач додасть нового продавця з Barcelona в таблицю, підзапит вибере два значення, і ваша команда зазнає невдачі.
D ISTINCT З підзапитів
Ось спосіб зробити це (висновок показаний на рисунку 10.2):
Підзапит встановив, що значення поля snum збіглося з Hoffman - 1001, а потім основний запит виділив всі замовлення з цим значенням snum з таблиці Замовлень (не розбираючи, відносяться вони до Hoffman чи ні). Так як кожен замовник призначений продавцеві, ми знаємо, що кожен рядок в таблиці Замовлень з даними значенням cnum повинна мати таке ж значення snum. Однак, оскільки там може бути будь-яке число таких рядків, підзапит міг би вивести багато (хоча і ідентичних) значень snum для даного поля cnum. Аргумент DISTINCT запобігає цьому. Якщо наш підзапит поверне більше одного значення, це буде означати помилку в наших даних - хороша річ для знаючих про це.
Повинен бути і альтернативний підхід, щоб посилатися до таблиці Замовників, а не до таблиці Замовлень в підзапиті. Так як поле cnum це первинний ключ таблиці Замовників, запит, що вибирає його, повинен видати тільки одне значення. Це раціонально, тільки якщо ви як користувач маєте доступ до таблиці Замовлень, але не до таблиці Замовників. В цьому випадку ви можете використовувати рішення, яке ми показали вище. (SQL має механізми, які визначають, хто має привілеї на виконання дій в певній таблиці. Це буде пояснюватися в Главі 22.)
Будь ласка, майте на увазі, що методика, яка використовується в попередньому прикладі, можна використовувати лише коли ви знаєте, що два різних поля в таблиці повинні завжди збігатися, як в нашому випадку. Ця ситуація не є типовою в реляційних базах даних (РБД), вона є винятком з правил.
П РЕДІКАТИ З підзапитів є незворотнім
Ви повинні звернути увагу що предикати, що включають підзапити, використовують вираз
Іншими словами, ви не повинні записувати попередній приклад так:
В суворої ANSI-продажу це призведе до невдачі, хоча деякі програми і дозволяють робити такі речі. ANSI також охороняє від появи у висновку підзапиту обох значень при порівнянні.
І спользование АГРЕГАТНИХ ФУНКЦІЙ В підзапитів
Тип функцій, який автоматично може виробляти одиночне значення для будь-якого числа рядків, звичайно ж - агрегатна функція.
Наприклад, наступний запит який повинен знайти середнє значення комісійних продавця в Лондоні,
не може використовуватися в підзапиті! У всякому разі, це не найкращий спосіб формувати запит.
Іншим способом може бути
І спользование підзапитів, ЯКІ ВИДАЮТЬ БАГАТО СТРОК ЗА ДОПОМОГОЮ ОПЕРАТОРА IN
Ви можете використовувати вкладені запити, які виробляють будь-яке число рядків, якщо ви застосовуєте спеціальний оператор IN (оператори BETWEEN, LIKE і IS NULL не можуть використовуватися з підзапитах). Як ви пам'ятаєте, IN визначає набір значень, одне з яких має збігатися з іншим терміном рівняння предиката в замовленні, щоб предикат був вірним.
Коли ви використовуєте IN з підзапитом, SQL просто формує цей набір з виведення підзапиту. Ми можемо, отже, використовувати IN щоб виконати такий підзапит, який не працюватиме з реляційним оператором, і знайти всі атрибути таблиці Замовлень для продавця в Лондоні (висновок показаний на рисунку 10.4):
У ситуації, подібної до цієї, підзапит простіше для розуміння користувачем і простіше для виконання комп'ютером, ніж якби ви використовували об'єднання:
Ви можете усунути потребу в DISTINCT, використовуючи IN замість (=):
Що трапиться, якщо є помилка і один з замовлень був акредитований різним продавцям? Версія, яка використовує IN, видаватиме вам все замовлення для обох продавців. Немає ніякого очевидного способу спостереження за помилкою, і тому згенеровані звіти чи рішення, зроблені на основі цього запиту, не міститимуть помилки. Варіант, який використовує (=), просто зазнає невдачі. Це, по крайней мере, дозволило вам дізнатися, що є така проблема. Ви повинні потім виконувати пошук несправності, виконавши цей підзапит окремо і спостерігаючи значення, які він виробляє. В принципі, якщо ви знаєте, що підзапит повинен (за логікою) вивести тільки одне значення, ви повинні використовувати =.
IN є гідною кандидатурою, якщо запит може обмежено виробляти одне або більше значень, незалежно від того, чи очікуєте ви їх чи ні. Припустимо, ми хочемо знати комісійні всіх продавців, які обслуговують замовників у Лондоні:
Виведеними для цього запиту, показаного в рисунку 10.5, є значення комісійних продавця Peel (snum = 1001), який має обох замовників в Лондоні. Але це тільки для даного випадку. Немає ніякої причини, щоб деякі замовники в Лондоні не могли бути призначені комусь ще. Отже, IN - це найбільш логічна форма для використання в запиті.
Підзапитів ВИБИРАЮТЬ одиночному стовпців
Сенс всіх підзапитів, обговорено в цьому розділі, в тому, що всі вони вибирають одиночний стовпець. Це обов'язково, оскільки отриманий висновок порівнюється з одиночним значенням. Підтвердженням цьому є те, що SELECT * не може використовуватися в підзапиті. Є виключення з цього, коли підзапити використовуються з оператором EXISTS, про який ми будемо говорити в Главі 12.
ВИКОРИСТАННЯ ВИСЛОВІВ В підзапитів
Ви можете використовувати вираз, засноване на стовпці, а не просто сам стовпець, в реченні SELECT підзапиту. Це може бути виконано або за допомогою реляційних операторів, або з IN. Наприклад, наступний запит використовує реляційний оператор = (висновок показаний на рисунку 10.6):
Він знаходить всіх замовників, чиє значення поля cnum, дорівнює 1000, вище поля snum Serres. Ми припускаємо, що стовпець sname не має ніяких подвійних значень (це може бути наказано або UNIQUE INDEX, обговорюваних в Главі 17. або обмеженням UNIQUE, обговорюваних в Главі 18); інакше
підзапит може призвести кілька значень. Коли поля snum і сnum не мають такого простого функціонального значення як, наприклад, первинний ключ, що не завжди добре, запит типу вищезгаданого неймовірно корисний.
П ОДЗАПРОСИ У ПРОПОЗИЦІЇ HAVING
Ви можете також використовувати підзапити всередині пропозиції HAVING. Ці підзапити можуть використовувати свої власні агрегатні функції, якщо вони не виробляють кількох значень, або використовувати GROUP BY або HAVING. Наступний запит є прикладом цього (висновок показаний на рисунку 10.7):
Ця команда підраховує замовників в San Jose з рейтингами вище середнього. Так як є інші оцінки, відмінні від 300, вони повинні бути виведені з числом номерів замовників, які мали цю оцінку.
Тепер ви використовуєте запити в ієрархічній манері. Ви бачили, що використання результату одного запиту для управління іншим розширює можливості, дозволяючи виконати більшу кількість функцій.
Ви тепер розумієте, як використовувати підзапити з реляційними операціями і зі спеціальним оператором IN, або в реченні WHERE, або в пропозиції HAVING зовнішнього запиту.
У наступних розділах ми будемо розглядати підзапити. Спочатку, в Розділі 11. ми обговоримо інший вид підзапиту, який виконується окремо для кожного рядка таблиці, що викликається в зовнішньому запиті. Потім, в главах 12 і 13. ми представимо вам кілька спеціальних операторів, які функціонують на всіх підзапитах, як це робить IN, за винятком випадків, коли ці оператори можуть використовуватися тільки в підзапитах.