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