Використання підзапитів, які видають багато рядків За допомогою оператора in

Ви можете використовувати підзапити, які проводять будь-яке число рядків, якщо ви застосовуєте спеціальний оператор IN (оператори BETWEEN, LIKE і IS NULL не можуть використовуватися з підзапитами). Як ви пам'ятаєте, IN визначає набір значень, одно з яких повинне співпадати з іншим терміном рівняння предиката в замовленні, щоб предикат був вірним.

Коли ви використовуєте IN з підзапитом, SQL просто формує цей набір з виведення підзапиту. Ми можемо, отже, використовувати IN щоб виконати такий підзапит, який не працюватиме з реляційним оператором, і не знайде усі атрибути таблиці Замовлень для продавця в Лондоні (висновок показаний на Малюнку 10.4) :

SELECT *

FROM Orders

WHERE snum IN

(SELECT snum

FROM Salespeople

WHERE city = "LONDON")

=============== SQL Execution Log ==============

| |

| SELECT * |

| FROM Orders |

| WHERE snum IN |

| (SELECT snum |

| FROM Salespeople |

| WHERE city = 'London'); |

| =============================================== |

| onum amt odate cnum snum |

| ----- -------- ---------- ----- ------ |

| 3003 767.19 10/03/1990 2001 1001 |

| 3002 1900.10 10/03/1990 2007 1004 |

| 3006 1098.19 10/03/1990 2008 1007 |

| 3008 4723.00 10/05/1990 2006 1001 |

| 3011 9891.88 10/06/1990 2006 1001 |

================================================

Малюнок 10.4 Використання підзапиту з IN

У ситуації, подібній до цієї, підзапит простіше для розуміння користувачем і простіше для виконання комп'ютером, чим якби ви використовували об'єднання:

SELECT onum, amt, odate, cnum, Orders.snum

FROM Orders, Salespeople

WHERE Orders.snum = Salespeople.snum

AND Salespeople.city = "London";

Хоча це і проведе той же самий вивід, що в прикладі з підзапитом, SQL повинен буде проглянути кожну можливу комбінацію рядків з двох таблиць і перевірити їх знову по складеному предикату. Простіше і ефективніше витягати з таблиці Продавців значення поля snum, де city = "London", а потім шукати ці значення в таблиці Замовлень, як це робиться у варіанті з підзапитом. Внутрішній запит дає нам snums=1001 і snum=1004. Зовнішній запит потім дає нам рядки з таблиці Замовлень, де ці поля snum знайдені.

Строго кажучи, то, швидше або ні працює варіант підзапиту, практично залежить від реалізації - в якій програмі ви це використовуєте. Частина вашої програми, що називається оптимізатор, намагається знайти найбільш ефективний спосіб виконання ваших запитів. Хороший оптимізатор у будь-якому випадку перетворить варіант об'єднання в підзапит, але немає досить простого способу, щоб з'ясувати, виконано це або ні. Краще зберегти ваші запити в пам'яті, ніж покладатися повністю на оптимізатор.

Звичайно, ви можете також використовувати оператор IN, навіть коли ви упевнені, що підзапит проведе поодиноке значення. У будь-якій ситуації, де ви можете використовувати реляційний оператор порівняння (=), ви можете використовувати IN. На відміну від реляційних операторів, IN не може змусити команду потерпіти невдачу, якщо більше ніж одно значення вибране підзапитом. Це може бути або перевагою або недоліком. Ви не побачите безпосередньо виводу з підзапитів, якщо ви вважаєте, що підзапит збирається провести тільки одно значення, а він проводить декілька. Ви не зможете пояснити відмінності у виведенні основного запиту. Наприклад, розглянемо команду, яка схожа на попередню :

SELECT onum, amt, odate

FROM Orders

WHERE snum =

(SELECT snum

FROM Orders

WHERE cnum = 2001);

Ви можете усунути потребу в DISTINCT, використовуючи IN замість (=) :

SELECT onum, amt, odate

FROM Orders

WHERE snum IN

(SELECT snum

FROM Orders

WHERE cnum = 2001);

Що станеться, якщо є помилка і одне із замовлень було акредитоване різним продавцям? Версія, використовуюча IN, видаватиме вам усі замовлення для обох продавців. Немає ніякого очевидного способу спостереження за помилкою, і тому згенеровані звіти або рішення, зроблені на основі цього запиту, не міститимуть помилки. Варіант, що використовує (=), просто потерпить невдачу. Це, принаймні, дозволило вам дізнатися, що є така проблема. Ви повинні потім виконувати пошук несправності, виконавши цей підзапит окремо і спостерігаючи значення, які він проводить. В принципі, якщо ви знаєте, що підзапит повинен (по логіці) вивести тільки одно значення, ви повинні використовувати =.

IN є відповідним, якщо запит може обмежено проводити одно або більше за значення, незалежно від того, чекаєте ви їх або ні. Припустимо, ми хочемо знати комісійні усіх продавців, обслуговуючих замовників в Лондоні:

SELECT comm

FROM Salespeople

WHERE snum IN

(SELECT snum

FROM Customers

WHERE city = "London");

Що виводяться для цього запиту, показаного в Малюнку 10.5, являються значення комісійних продавця Peel (snum = 1001), який має обох замовників в Лондоні. Але це тільки для даного випадку. Немає ніякої причини, щоб деякі замовники в Лондоні не могли бути призначені комусь ще. Отже, IN - це найбільш логічна форма для використання в запиті.

=============== SQL Execution Log ==============

| |

| SELECT comm |

| FROM Salespeople |

| WHERE snum IN |

| (SELECT snum |

| FROM Customers |

| WHERE city = 'London'); |

| =============================================== |

| comm |

| ------- |

| 0.12 |

| |

| |

===============================================

Малюнок 10.5 Використання IN з підзапитом для виведення одного значення

Між іншим, префікс таблиці для поля city в попередньому прикладі не обов'язковий, незважаючи на можливу неоднозначність між полями city таблиці Замовника і таблиці Продавців. SQL завжди шукає перше поле в таблиці, позначеній в пропозиції FROM поточного підзапиту. Якщо поле з цим ім'ям там не знайдене, перевіряються зовнішні запити. У вищезгаданому прикладі, "city" в пропозиції WHERE означає, що є посилання на Customer.city (поле city таблиці Замовників). Оскільки таблиця Замовників вказана в пропозиції FROM поточного запиту, SQL припускає що це правильно. Це припущення може бути скасоване повним ім'ям таблиці або префіксом псевдоніма, про яких ми поговоримо пізніше, коли говоритимемо про співвіднесені підзапити. Якщо можливий безлад, звичайно ж, краще всього використовувати префікси.