Вкладені та пов'язані підзапити в SQL, предикат EXISTS. Використання оператора EXISTS Запити, які використовують функцію exists

WHERE EXISTS

Підзапит перевіряється на наявність одного або декількох рядків. Якщо хоча б один рядок задовольняє запит, то повертається булеве значення ІСТИНА. При вказанні додаткового ключового слова NOT булеве значення ІСТИНА повертається, якщо підзапит не повертає відповідних рядків.

підзапит

На основі повністю сформованого підзапиту виходить результуючий набір даних.

Загальні правила

Оператор EXISTS перевіряє існування одного або декількох рядків у запиті батьківського запиту.

SELECT * FROM jobs WHERE NOT EXISTS (SELECT * FROM employee WHERE jobs.job_id=employye. job_id);

У цьому прикладі перевіряється в підзапит записів за допомогою додаткового ключового слова NOT. У наступному прикладі для отримання основного результуючого набору даних проводиться пошук специфічних записів у підзапиті.

SELECT au_lname FROM authors WHERE EXISTS (SELECT * FROM publishers WHERE authors.city=publishers.city);

Цей запит повертає прізвища авторів (au_lname), які мешкають у тому самому місті, що й видавці (publishers). Зверніть увагу, що можна використовувати в підзапиті зірочку, оскільки підзапит повинен повернути лише один запис з булевим значенням ІСТИНА. У таких випадках стовпці не відіграють ролі. Ключовий момент – це існування рядка.

У багатьох запитах оператор EXISTS виконує таку ж функцію, як і ANY. Оператор EXISTS є найефективнішим при використанні з корельованими запитами.

Оператор EXISTS семантично еквівалентний оператору ANY.

Підзапит в операторі EXISTS зазвичай робить один із двох видів пошуку. Перший варіант - це використання групового символу - зірочки (наприклад, SELECT * FROM ...), і в цьому випадку ви не отримуєте якийсь конкретний стовпець або значення. Зірочка тут означає «будь-який стовпець». Другий варіант - вибір у підзапит лише одного конкретного стовпця (наприклад, SELECT aujd FROM). Деякі окремі платформи дозволяють виконувати підзапити кількома стовпцями (наприклад, SELECT aujd, aujname FROM…). Однак ця можливість є досить рідкісною і її слід уникати в коді, який потрібно переносити на інші платформи.

Відмінності між платформами

Усі платформи підтримують оператор EXISTS у вигляді, що ми описали вище.

«Раніше було простіше» — подумав я, сідаючи за оптимізацію чергового запиту в SQL management studio. Коли я писав під MySQL, реально все було простіше – чи працює, чи ні. Або гальмує чи ні. Explain вирішував усі мої проблеми, більше нічого не потрібно. Зараз у мене є потужне середовище розробки, налагодження та оптимізації запитів та процедур/функцій, і все це нагромадження створює тільки більше проблем. А чому? Бо вбудований оптимізатор запитів – зло. Якщо MySQL і PostgreSQL я напишу

Select * from a, b, c де a.id = b.id, b.id = c.id

і в кожній з табличок буде хоча б по 5к рядків - все зависне. І слава Богу! Тому що інакше в розробнику, у кращому випадку, виробляється лінощі писати правильно, а в гіршому він взагалі не розуміє що робить! Адже цей запит у MSSQL пройде аналогічно

Select * з join b on a.id = b.id join c on b.id = c.id

Вбудований оптимізатор причеше бидлозапрос і все буде окей.

Він так само вирішить, що краще робити — exist або join і ще багато чого. І все працюватиме максимально оптимально.

Тільки є одне АЛЕ. Одного разу оптимізатор спіткнеться про складний запиті рятує, і тоді ви отримаєте величезну проблему. І отримаєте ви її, можливо, не відразу, а коли вага таблиць досягне критичної маси.

Так ось до суті статті. exists та in - дуже важкі операції. Фактично це окреме підзапит. для кожногоРядки результату. А якщо ще й є вкладеність, то це взагалі туши світло. Все буде окей, коли повертається 1, 10, 50 рядків. Ви не відчуєте різниці, а можливо join буде навіть повільніше. Але коли витягується 500 – почнуться проблеми. 500 підзапитів у рамках одного запиту – це серйозно.

Нехай з погляду людського розуміння in і exists краще, але з погляду тимчасових витрат на запити, що повертають 50+ рядків — вони не допустимі.

Потрібно зазначити, що природно, якщо десь зменшується — десь має прибувати. Так, join більш ресурсомісткий по пам'яті, адже тримати одночасно всю таблицю значень і оперувати нею - накладніше, ніж смикати підзапити для кожного рядка, швидко звільняючи пам'ять. Потрібно дивитися безпосередньо за запитом і заміряти - чи критично буде використання зайвої пам'яті для часу чи ні.

Наведу приклади повних аналогій. Загалом кажучи, я не зустрічав ще запитів такого ступеня складності, які не могли б бути розкручені в каскад join'ів. Нехай на це піде день, але все можна розкрити.

Select * from a where a.id in (select id from b) select * from a where exists (select top 1 1 from b where b.id = a.id) select * from a join b on a.id = b. id select * from a where a.id no in (select id from b) select * from a where not exists (select top 1 1 from b where b.id = a.id) select * from a left join b on a. id = b.id where b.id is null

Повторюся - дані приклади MSSQL оптимізатор оптимізує під максимальну продуктивністьі на таких найпростіших запитах дурнів не буде ніколи.

Розглянемо тепер приклад реального запиту, який довелося переписувати через те, що на деяких вибірках він просто намертво зависав (структура дуже спрощена і поняття замінені, не потрібно лякатися якоїсь не оптимальності структури бд).

Потрібно витягнути всі дублікати «продуктів» у різних акаунтах, орієнтуючись на параметри продукту, його групи, та групи-батька, якщо така є.

Select d.PRODUCT_ID from PRODUCT s, PRODUCT_GROUP sg left join M_PG_DEPENDENCY sd on (sg.PRODUCT_GROUP_ID = sd.M_PG_DEPENDENCY_CHILD_ID), PRODUCT d, PRODUCT_GROUP dg left join M_PG_DEPENDENCY dd on (dg.PRODUCT_GROUP_ID = dd.M_PG_DEPENDENCY_CHILD_ID) where s.PRODUCT_GROUP_ID=sg .PRODUCT_GROUP_ID and d.PRODUCT_GROUP_ID=dg.PRODUCT_GROUP_ID and sg.PRODUCT_GROUP_PERSPEC=dg.PRODUCT_GROUP_PERSPEC and sg.PRODUCT_GROUP_NAME=dg.PRODUCT_GROUP_NAME and s.PRODUCT_NAME=d.PRODUCT_NAME and s.PRODUCT_TYPE=d.PRODUCT_TYPE and s.PRODUCT_IS_SECURE=d.PRODUCT_IS_SECURE and s.PRODUCT_MULTISELECT=d.PRODUCT_MULTISELECT and dg.PRODUCT_GROUP_IS_TMPL=0 and ((sd.M_PG_DEPENDENCY_CHILD_ID is null and dd.M_PG_DEPENDENCY_CHILD_ID is null) or exists (select 1 from PRODUCT_GROUP sg1, PRODUCT_GROUP dg1 where sd.M_PG_DEPENDENCY_PARENT_ID = sg1.PRODUCT_GROUP_ID and dd .M_PG_DEPENDENCY_PARENT_ID = dg1.PRODUCT_GROUP_ID and sg1.PRODUCT_GROUP_PERSPEC=dg1.PRODUCT_GROUP_PERSPEC and sg1.PRODUCT_GROUP_NAME=dg1.PRODUCT_GROUP_N AME and))

Отож це той випадок, коли оптимізатор спасував. І для кожного рядка виконувався важкий exists, що вбивало основу.

Виберіть d.PRODUCT_ID від PRODUCT з join PRODUCT d on s.PRODUCT_TYPE=d.PRODUCT_TYPE і s.PRODUCT_NAME=d.PRODUCT_NAME і s.PRODUCT_IS_SECURE=d.PRODUCT_IS_SECURE and s.PRODUCT_MULTISELECT=d.PRODUCT sg.PRODUCT_GROUP_ID join PRODUCT_GROUP dg on d.PRODUCT_GROUP_ID=dg.PRODUCT_GROUP_ID and sg.PRODUCT_GROUP_NAME=dg.PRODUCT_GROUP_NAME and sg.PRODUCT_GROUP_PERSPEC=dg.PRODUCT_GROUP_PERSPEC left join M_PG_DEPENDENCY sd on sg.PRODUCT_GROUP_ID = sd.M_PG_DEPENDENCY_CHILD_ID left join M_PG_DEPENDENCY dd on dg.PRODUCT_GROUP_ID = dd.M_PG_DEPENDENCY_CHILD_ID left join PRODUCT_GROUP sgp on sgp.PRODUCT_GROUP_ID = sd.M_PG_DEPENDENCY_PARENT_ID left join PRODUCT_GROUP dgp on dgp.PRODUCT_GROUP_ID = dd.M_PG_DEPENDENCY_PARENT_ID and sgp.PRODUCT_GROUP_NAME = dgp.PRODUCT_GROUP_NAME and isnull(sgp.PRODUCT_GROUP_IS_TMPL, 0) = isnull(dgp. PRODUCT_GROUP_IS_TMPL, 0) where (sd.M_PG_DEPENDENCY_CHILD_ID is null and dd.M_PG_DEPENDENCY_CHILD_ID is null) or (sgp.PRODUCT_GROUP_NAME is not null and dgp.PRODUCT_GROUP_NAME is not null) go

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

Це наочний приклад того, як довіра MSSQL оптимізатору може зіграти злий жарт. Не довіряйте йому, не лінуйтеся, join'те ручками, щоразу думайте що краще в цій ситуації — exists, in або join.

Предикат мови SQL EXISTS виконує логічне завдання. У запити SQLцей предикат використовується у виразах виду

EXISTS (SELECT * FROM ІМ'Я_ТАБЛИЦІ...).

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

Для NOT EXISTS все навпаки. Вираз

NOT EXISTS (SELECT * FROM ІМ'Я_ТАБЛИЦІ ...)

повертає істину, коли на запит не знайдено жодного рядка, і брехня, коли знайдено хоча б один рядок.

Найбільш прості запити із предикатом SQL EXISTS

У прикладах працюємо з базою даних бібліотеки та її таблицями "Книга у користуванні" (BOOKINUSE) та "Користувач" (USER). Поки нам знадобиться лише таблиця "Книга у користуванні" (BOOKINUSE).

Author	Title	Pubyear	Inv_No	User_ID
Толстой	Війна і мир	2005	28	65
Чехів	Вишневий сад	2000	17	31
Чехів	Вибрані оповідання	2011	19	120
Чехів	Вишневий сад	1991	5	65
Ільф та Петров	Дванадцять стільців	1985	3	31
Маяковський	Поеми	1983	2	120
Пастернак	Доктор Живаго	2006	69	120
Толстой	Неділя	2006	77	47
Толстой	Анна Кареніна	1989	7	205
Пушкін	Капітанська донька	2004	25	47
Гоголь	П'єси	2007	81	47
Чехів	Вибрані оповідання	1987	4	205
Пастернак	Вибране	2000	137	18

приклад 1.Визначити ID користувачів, яким видано книги Толстого, яким також видано книги Чехова. У зовнішньому запиті відбираються дані про користувачів, яким видано книги Толстого, а предикат EXISTS задає додаткову умову, яка перевіряється у внутрішньому запиті - користувачі, яким видано книги Чехова. Додатковою умовою у внутрішньому запиті є збіг ідентифікаторів користувачів із зовнішнього та внутрішнього запитів: User_ID=tols_user.user_id. Запит буде наступним:

Цей запит поверне наступний результат:

Відмінності предикатів EXISTS та IN

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

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

User_ID

120

65

205

Внутрішній запит (після IN) вибирає авторів: Чехов; Ільф та Петров. Зовнішній запит обирає всіх користувачів, яким видано книжки цих авторів. Бачимо, що, на відміну від предикату EXISTS, предикат IN випереджається ім'ям стовпця, у разі - Author.

Запити з предикатом EXISTS та додатковими умовами

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

Приклад 5.Визначити ID користувачів, яким видано хоча б одну книгу Пастернака, та яким при цьому видано понад 2 книги. Пишемо наступний запит, у якому перша умова задається предикатом EXISTS із вкладеним запитом, а друга умова з оператором HAVING завжди має бути після вкладеного запиту:

Результат виконання запиту:

User_ID

120

Як видно з таблиці BOOKINUSE, книга Пастернака видана також користувачеві з ID 18, але йому видана лише одна книга і він не потрапляє у вибірку. Якщо застосувати до подібного запиту ще раз функцію COUNT, але вже для підрахунку вибраних рядків (потренуйтеся в цьому самостійно), можна отримати інформацію про те, скільки користувачів, які читають книги Пастернака, при цьому читають також книги інших авторів. Це вже із сфери аналізу даних.

Запити з предикатом EXISTS до двох таблиць

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

У наступному прикладі з тієї ж бази даних, крім таблиці BOOKINUSE, буде потрібно також таблиця "Користувач" (USER).

Результатом виконання запиту буде наступна таблиця:

Author

Чехів

Маяковський

Пастернак

Як і у випадку використання оператора JOIN, у випадках більш ніж однієї таблиці слід використовувати псевдоніми таблиць для перевірки відповідності значень ключів, що з'єднують таблиці. У прикладі псевдоніми таблиць - bk і us, а ключ, що з'єднує таблиці - User_ID.

Предикат EXISTS у з'єднаннях понад дві таблиці

Зараз ми побачимо предметніше, чому використовувати EXISTS краще в тих випадках, коли в результуючу таблицю потраплять стовпці лише з однієї таблиці.

Працюємо з базою даних "Нерухомість". Таблиця Deal містить дані про угоди. Для наших завдань у цій таблиці буде важливим є стовпець Type з даними про тип угоди - продаж або оренда. Таблиця Object містить дані про об'єкти. У цій таблиці нам знадобляться значення стовпців Rooms (число кімнат) та LogBalc, що містить дані про наявність лоджії або балкона у булевому форматі: 1 (так) або 0 (ні). Таблиці Client, Manager і Owner містять дані відповідно про клієнтів, менеджерів фірми та власників об'єктів нерухомості. У цих таблицях FName та LName відповідно ім'я та прізвище.

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

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

Написати запит SQL з предикатом EXISTS самостійно, а потім переглянути рішення

Продовжуємо писати разом запити SQL із предикатом EXISTS

Приклад 9.Визначити власників об'єктів, які було взято в оренду. Пишемо наступний запит, у якому предикатом EXISTS також подано звернення до результату з'єднання двох таблиць:

Як і в попередньому прикладі, з таблиці, до якої звернено зовнішній запит, буде виведено всі поля.

приклад 10.Визначити кількість власників, з об'єктами яких провів менеджер Савельєва. Пишемо запит, у якому зовнішній запит звертається до з'єднання трьох таблиць, а предикатом EXISTS задано звернення лише до таблиці:

Всі запити перевірені на базі даних. Успішного використання!

Реляційні бази даних та мова SQL

Новосибірська державна академія економіки та управління

ЛАБОРАТОРНИЙ ПРАКТИКУМ З ДИСЦИПЛІНИ

"БАЗИ ДАНИХ"

Лабораторна робота N 7

«Мова баз даних SQL: команди маніпуляції даними»

НОВОСИБІРСЬК 2000

SQL – це скорочена назва мови структурованих запитів (Structured Query Language). З назви мови відомо, що його основне призначення полягає у формуванні запитів на отримання інформації з бази даних. Команди на вибірку даних становлять основу мови маніпулювання даними DML - складової мови SQL. Однак DML складається не лише з команд вибірки даних із бази. Існують також команди модифікації даними, управління даними та інші.

У лабораторній роботі розглядаються базові засоби мови DML. В процесі виконання лабораторної роботими дотримуватимемося стандарту SQL2.

У зв'язку з тим, що SQL є об'ємною мовою, розглядатимемо лише основні команди. Різні специфічні засоби SQL розглядаються у наступних лабораторних роботах.

Для виконання лабораторної роботи потрібне знання основ реляційної моделі даних, основ реляційної алгебри та реляційного обчислення, принципів роботи з СУБД MS SQL Server.

В результаті виконання лабораторної роботи Ви опануєте способи маніпулювання даними за допомогою команд мови SQL, розгляньте діалект мови, реалізований у СУБД MS SQL Server.

ВСТУП

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

Спочатку структура синтаксису SQL була заснована (або принаймні здавалася заснованою) на реляційному обчисленні Кодда. Єдиною підтримуваною операцією реляційної алгебри було об'єднання.

SQL2 на додаток до аналогічного реляційного обчислення синтаксису, розробленого в попередньому стандарті, безпосередньо реалізовані операції об'єднання, перетин, різниця і з'єднання. Операції вибору, проектування та твори підтримувалися (і продовжують підтримуватися) практично безпосередньо, тоді як операції поділу та присвоєння підтримуються у більш громіздкій формі.

Спочатку ми опишемо мову запитів SQL, а потім його операції введення та зміни даних. Операції зміни даних будуть описані в останню чергу, оскільки їхня структура певною мірою спирається на структуру мови запитів.

Прості запити

Для нас простим запитомбуде запит, який звертається лише до однієї таблиці бази даних. Прості запити допоможуть проілюструвати основну структуру SQL.

Простий запит.Запит, який звертається лише до однієї таблиці бази даних.

Запит:Хто працює штукатурами?

WHERE SKILL_TYPE = "Штукатури"

Результат:

Г.Ріковер

Цей запит ілюструє три найпоширеніші фрази SQL: SELECT, FROM та WHERE. Хоча в нашому прикладі ми помістили їх на різні рядки, вони можуть стояти в одному рядку. Вони також можуть розміщуватися з різними відступами, а слова всередині фраз можуть розділятися довільним числом прогалин. Розглянемо параметри кожної фрази.

Select. Фраза SELECT перераховує стовпці, які мають увійти до результуючої таблиці. Це завжди стовпці деякої реляційної таблиці. У прикладі результуюча таблиця складається з одного стовпця (NAME), але у випадку вона може містити кілька стовпців; вона також може містити обчислені значення чи константи. Ми наведемо приклади кожного з цих варіантів. Якщо результуюча таблиця повинна містити більше одного стовпця, всі потрібні стовпці перераховуються після команди SELECTчерез кому. Наприклад, фраза SELECT WORKER_ID, NAME видасть у результаті таблицю, що складається зі стовпців WORKER_ID та NAME.

Фраза SELECT.Вказує стовпці результуючої таблиці.

від. Фраза FROM визначає одну або більше таблиць, до яких звертається запит. Усі стовпці, перелічені у фразах SELECT і WHERE, повинні існувати у одній із таблиць, перелічених у команді FROM. У SQL2 ці таблиці можуть бути визначені в схемі як базові таблиці або представлення даних, або ж вони самі можуть бути не мають імен таблицями, отриманими в результаті запитів SQL. У разі запит явно наводиться у команді FROM.

Фраза FROM.Задає таблиці, до яких звертається запит.

Where. Фраза WHERE містить умову. на підставі якого вибираються рядки таблиці (таблиць). У нашому прикладі умова полягає в тому, що стовпець SKILL_TYPE повинен містити константу "Штукатур", укладену в апострофи, як це завжди робиться з текстовими константами SQL. Фраза WHERE – найбільш мінлива команда SQL; вона може містити безліч різноманітних умов. Більшість нашого викладу буде присвячено ілюстрації різних конструкцій, дозволених у команді WHERE.

Фраза WHERE.Задає умову, виходячи з рядка із заданих таблиць.

Наведений вище запит SQL обробляється системою у порядку: FROM, WHERE, SELECT. Це рядки таблиці, зазначеної в команді FROM, поміщаються в робочу область для обробки. Потім до кожного рядка послідовно застосовується фраза WHERE. Усі рядки, які не задовольняють умові WHERE, виключаються із розгляду. Потім рядки, які задовольняють умові WHERE, обробляються командою SELECT. У нашому прикладі з кожного такого рядка вибирається NAME, і всі вибрані значення виводяться як результати запиту.

Запит:Навести всі дані про будівлі офісів.

WHERE TYPE = "Офіс"

Результат:

BLDG IDАДРЕСTYPEQLTY LEVELSTATUS

312 Вул.В'язів, 123 Офіс 2 2

210 Березова вул. 1011 Офіс З 1

111 Осинова вул. 1213 Офіс 4 1

Зірочка (*) у команді SELECT означає «рядок цілком». Це зручне скорочення, яким ми часто користуватимемося.

Запит:Яка тижнева зарплата кожного електрика?

SELECT NAME, "Тижня зарплата = ", 40 * HRLY_RATE

WHERE SKILL_TYPE = "Електрик"

Результат:

М.Фарадей Тижнева зарплата = 500.00

Х.Колумб Тижнева зарплата = 620.00

Цей запит ілюструє вживання і символьних констант (у нашому прикладі "Тижня зарплата = "), і обчислень у команді SELECT. *, /), згруповані за необхідності за допомогою дужок. Ми також включили новий команду ORDER BY, яка сортує результат запиту у зростаючому алфавітно-числовому порядку за вказаним стовпцем. Якщо ви хочете впорядковувати результати спадання, то до команди потрібно додати DESC. Фраза ORDER BY може сортувати результати за кількома стовпцями, за одним - у порядку зростання, за іншими - у порядку зменшення. Першим указується стовпець первинного ключа сортування.

Символьна константа.Константа, що складається з букв, цифр та «спеціальних» символів.

Запит:Хто має погодинну ставку від 10 до 12 доларів?

WHERE HRLY_RATE > = 10 AND HRLY_RATE< - 12

Результат:

WORKER ID NAME HRLY_RATE SKILL_TYPE SUPV_ID

Цей запит ілюструє деякі додаткові можливості команди WHERE: оператори порівняння та булеву операцію AND(І). Для порівняння стовпців з іншими стовпцями або константами можуть використовуватися шість операторів порівняння (=,<>(не дорівнює),<, >, <=, >=). Для створення складових умов або заперечення умови можуть використовуватися булеві операції AND (І), OR (АБО) і NOT (HE). Для угруповання умов, як завжди в мовах програмування, можуть використовуватися дужки.

Оператори порівняння =,<>, <, >, <=, >=.

Бульові операції AND (І), OR (АБО) та NOT (HE) .

Для формулювання цього запиту також можна було використовувати оператор BETWEEN (між):

WHERE HRLY_RATE BETWEEN 10 AND 12

BETWEEN може використовуватися для порівняння деякої величини з двома іншими величинами, перша з яких менша за другу, якщо порівнювана величина може дорівнювати кожній з даних величин або будь-якому значенню між ними.

Запит: Перерахувати штукатурів, покрівельників та електриків.

WHERE SKILL_TYPE IN ("Штукатур", "Покрівельник", "Електрик")

Результат:

WORKER_ID NAME HRLY_RATE SKILL_TYPE SUPV_ID

1412 К.Немо 13.75 Штукатури 1520

2920 Р.Гаррет 10.00 Покрівля 2920

1520 р. Ріковер 11.75 штукатур 1520

Цей запит пояснює використання оператора порівняння IN (B). Умова WHERE вважається істинною, якщо тип спеціальності рядка розташований усередині множини, вказаної в дужках, тобто якщо тип спеціальності - штукатур, покрівельник або електрик. Ми ще зустрінемося з оператором IN у підзапитах.

Припустимо, що ми можемо точно згадати написання спеціальності: «електрик» чи «електронник» чи ще якось. Символи шаблону, які замінюють невизначені рядки символів, полегшують пошук неточного написання запиту.

Шаблон символів.Символи, які замінюють невизначені рядки символів.

Запит:Перерахувати працівників, чий тип фаху починається з «Елек».

WHERE SKILL_TYPE LIKE ("Елек%")

Результат:

WORKER ID NAME HRLY_RATE SKILL_TYPE SUPV_ID

1235 М.Фарадей 12.50 Електрик 1311

1311 Х.Колумб 15.50 Електрик 1311

SQL має два символи шаблону: % (відсоток) і _ (підкреслення). Підкреслення замінює рівно один невизначений символ. Відсоток замінює довільну кількість символів, починаючи з нуля. Коли використовуються символи шаблону, для порівняння символьних змінних із константами потрібен оператор LIKE (як). Інші приклади:

NAME LIKE "__Колумб"

NAME LIKE "__K%"

Умова в першому прикладі є істинною, якщо NAME складається з двох символів, за якими слідує "Колумб". У таблиці WORKER усі імена починаються з першого ініціалу та точки. Таким чином, за допомогою цієї умови ми. знайдемо всіх працівників на прізвище «Колумб». Умова другого прикладу дозволяє знайти всіх працівників, чиї прізвища починаються на літеру «К».

Запит:Знайти всі роботи, які розпочинаються протягом найближчих двох тижнів.

WHERE START _DATE BETWEEN CURRENT_DATE AND

Результат:(Припустимо, що поточна дата CURRENT DATE = 10.10)

WORKER_ID BLDG_ID START_DATE NUM_DAYS

1235 312 10.10 5

1235 515 17.10 22

3231 111 10.10 8

1412 435 15.10 15

3231 312 24.10 20

1311 460 23.10 24

Цей запит ілюструє використання оператора BETWEEN (між) зі значеннями типу date (дата) та interval (проміжок). CURRENT_DATE - це функція, яка завжди повертає значення сьогоднішньої дати. Вираз

CURRENT_DATE + INTERVAL "14" DAY

додає двотижневий проміжок до поточної дати. Таким чином, ASSIGNMENT вибирається (припущення, що сьогодні 10.10) у тому випадку, якщо в ній значення стовпця START_DATE лежить між 10.10 та 24.10. На цьому видно, що ми можемо додавати до полів дат величини типу interval. Понад те, ми можемо множити значення проміжків на цілі величини. Наприклад, припустимо, що ми хочемо з'ясувати, яке число буде через певну кількість тижнів (позначене змінною NUM_WEEKS (ЧИСЛО ТИЖДЕНЬ)). Ми можемо це зробити так:

CURRENT_DATE + INTERVAL "7" DAY * NUM_WEEKS

2. Багатотабличні запити

Можливість зв'язувати елементи даних поза межами однієї таблиці є важливою для будь-якої мови баз даних. У реляційній алгебрі цю функцію виконує операція з'єднання. Хоча значна частина SQL заснована безпосередньо на реляційному обчисленні, SQL пов'язує дані різних таблиць аналогічно до того, як це робить операція з'єднання реляційної алгебри. Нині ми покажемо, як це робиться. Розглянемо запит:

Запит:

Дані, необхідні відповіді, перебувають у двох таблицях: WORKER і ASSIGNMENT. Для вирішення SQL потрібно перерахувати обидві таблиці в команді FROM і задати спеціальний тип умови WHERE:

SELECT SKILL_TYPE

FROM WORKER, ASSIGNMENT

WHERE WORKER.WORKER_ID = ASSIGNMENT.WORKER_ID

AND BLDG_ID = 435

Що тут відбувається? Ми повинні розглянути два етапи обробки системою цього запиту.

1. Як завжди, спочатку обробляється фраза FROM. Однак у цьому випадку, оскільки в команді вказано дві таблиці, система створює декартове добуток рядків цих таблиць. Це означає, що створюється (логічно) одна велика таблиця, що складається зі стовпців обох таблиць, у якій кожен рядок однієї таблиці спарена з кожним рядком іншої таблиці. У нашому прикладі, оскільки в таблиці WORKER п'ять стовпців, а в таблиці ASSIGNMENT чотири стовпці, у декартовому творі, створеному командою FROM, буде дев'ять стовпців. Загальна кількість рядків декартового добутку дорівнює m * n, де m - число рядків таблиці WORKER; а n – число рядків таблиці ASSIGNMENT. Оскільки в таблиці WORKER 7 рядків, а в таблиці ASSIGNMENT 19 рядків, то декартове твір міститиме 7х19 або 133 рядки. Якщо команді FROM перераховано понад дві таблиці, то створюється декартово добуток всіх таблиць, зазначених у команді.

Декартове твір. Результат об'єднання кожного рядка однієї таблиці з кожноюрядком іншої таблиці.

2. Після створення гігантської реляційної таблиці система як і раніше застосовує команду WHERE. Кожен рядок таблиці створений командою FROM. перевіряється виконання умов WHERE. Рядки, які задовольняють умові, виключаються з розгляду. Потім до рядків, що залишилися, застосовується фраза SELECT.

Фраза WHERE у нашому запиті містить дві умови:

1. WORKER. WORKER_ID = ASSIGNMENT.WORKER_ID

2. BLDG_ID = 435

Перша з цих умов – умова з'єднання. Зверніть увагу, що оскільки обидві таблиці WORKER і ASSIGNMENT містять стовпець з ім'ям WORKER_ID, їх декартовий твір міститиме два стовпці з таким ім'ям. Щоб розрізняти їх, ми поміщаємо перед ім'ям стовпця ім'я вихідної таблиці, відокремлюючи його точкою.

Перша умова означає, що у будь-якому вибраному рядку значення стовпця WORKER_ID з таблиці WORKER має збігатися зі значенням стовпця WORKER_ID з таблиці ASSIGNMENT. Насправді ми з'єднуємо дві таблиці WORKER_ID. Усі рядки, у яких значення цих двох стовпців не рівні, виключаються із таблиці твору. В точності те саме відбувається при виконанні операції природного з'єднання реляційної алгебри. (Однак, деяка відмінність від природного з'єднання все ж таки є: мова SQL автоматично не видаляє зайвий стовпець WORKER_ID). Повне з'єднання цих двох таблиць з додатковою умовою BLDG_ID = 435 представлено на рис. 1. Застосування команди SELECT дасть нарешті наступний результат запиту:

SKILL TYPE

Штукатур

Покрівля

Електрик

Мал. 1. З'єднання таблиць WORKER та ASSIGNMENT

Тепер ми покажемо, як до SQL приєднати таблицю до неї самої.

Запит:Перерахувати працівників, вказавши імена їхніх менеджерів.

SELECT А.WORKER_NAME, B.WORKER_NAME

FROM WORKER A, WORKER В

WHERE B.WORKER_ID = A.SUPV_ID

Фраза FROM у цьому прикладі створює дві «копії» таблиці WORKER, даючи їм псевдоніми А і У. Псевдонім - це альтернативне ім'я, дане таблиці. Потім копії А і В таблиці WORKER з'єднуються командою WHERE на підставі умови рівності WORKER_ID в і SUPV_ID в А. Таким чином, кожен рядок з А приєднується до рядка, що містить інформацію про менеджера рядка А (рис.2).

Мал. 2. З'єднання двох копій таблиці WORKER

Вибираючи з кожного рядка два імені працівника, ми отримаємо потрібний список:

А.NAMEВ.NAME

М.Фарадей Х.Колумб

К.Немо Г.Ріковер Р.Гаррет Р.Гаррет

П.Мейсон П.Мейсон Г.Ріковер Г.Ріковер Х.Колумб Х.Колумб Дж.Баррістер П.Мейсон

Псевдонім.Альтернативне ім'я дане таблиці.

A.WORKER_NAME представляє працівника, а B.WORKER_NAME представляє менеджера. Зверніть увагу, що деякі працівники – самі собі менеджери, що випливає з виконаної в їх рядках рівності WORKER_ID – SUPV_ID.

SQL можна за один раз пов'язати більше двох таблиць:

Запит

SELECT WORKER_NAME

FROM WORKER, ASSIGNMENT, BUILDING

WHERE WORKER.WORKER_ID = ASSIGNMENT.WORKER_ID AND ASSIGNMENT.BLDG_ID = BUILDING.BLDG_ID AND

TYPE = "Офіс"

Результат:

М.Фарадей

Г.Ріковер

Дж.Баррістер

Зверніть увагу, що якщо ім'я стовпця (наприклад, WORKER_ID або BLDG_ID) зустрічається більше, ніж в одній таблиці, для уникнення невизначеності ми повинні перед ім'ям стовпця вказати ім'я вихідної таблиці. Але якщо ім'я стовпця зустрічається лише в одній таблиці, як TYPE у нашому прикладі, то жодної невизначеності немає, тому ім'я таблиці вказувати не потрібно.

Команди SQL цього запиту створюють одну таблицю із трьох реляційних таблиць бази даних. Перші дві таблиці з'єднуються WORKER_ID, після чого до отриманої таблиці приєднується третя таблиця по BLDG_ID. Умова

TYPE = "Офіс"

команди WHERE призводить до виключення всіх рядків, крім рядків, що належать до офісних будівель. Це відповідає вимогам запиту.

3. Підзапити

Підзапит.Запит всередині запиту

Підзапит може поміщатись у команду WHERE запиту, внаслідок чого можливості команди WHERE розширюються. Розглянемо приклад.

Запит:Які спеціальності робітників, призначених на будівлю 435?

SELECT SKTLL_TYPE

FROM WORKER WHERE WORKER_ID IN

(SELECT WORKER_ID

WHERE BLDG_ID = 435)

Підзапит у цьому прикладі

(SELECT WORKER_ID

WHERE BLDG_ID = 435)

Запит, в якому міститься підзапит, називається зовнішнім запитомабо головним запитом. Підзапит призводить до створення наступної множини ВД (ідентифікаторів) працівників:

WORKER ID

Зовнішній запит.Головний запит, який містить усі підзапити.

Потім ця безліч ВД займає місце підзапиту у зовнішньому запиті. З цього моменту виконується зовнішній запит, який використовує безліч, створене підзапитом. Зовнішній запит обробляє кожен рядок таблиці WORKER відповідно до умови WHERE. Якщо WORKER_ID рядка лежить у (IN) множині, створеному підзапитом, то SKILL_TYPE рядки вибирається та виводиться у результуючій таблиці:

SKILL TYPE

Штукатур

Покрівля

Електрик

Дуже важливо, що фраза SELECT підзапит містить WORKER_ID і тільки WORKER_ID. В іншому випадку фраза WHERE зовнішнього запиту, що означає, що WORKER_ID лежить у безлічі ВД працівників, не мала б сенсу.

Зверніть увагу, що підзапит може логічно виконуватися перш ніж хоча б один рядок розглядається головним запитом. У певному сенсі підзапит незалежно від головного запиту. Він може виконуватись як повноцінний запит. Ми говоримо, що такий підзапит не корелюваний із головним запитом. Як незабаром побачимо, підзапити можуть бути корельованими.

Некорельований підзапит.Підзапит, значення якого не залежить від жодного зовнішнього запиту.

Наведемо приклад підзапиту всередині підзапиту.

Запит: Перерахувати працівників, призначених на будівлі офісів

Знову ми розглядаємо запит, за допомогою якого вивчали з'єднання.

SELECT WORKER_MAME

WHERE WORKER_ID IN

(SELECT WORKER_ID

WHERE BLDG_ID IN

WHERE TYPE = "Офіс"))

Результат:

М.Фарадей

Г.Ріковер

Дж.Баррістер

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

Виконання запиту відбувається у порядку зсередини назовні. Тобто самий внутрішній запит (або «найнижній») виконується першим, потім виконується підзапит, що містить його, а потім зовнішній запит.

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

Корельований підзапит. Підзапит, результат якого залежить від рядка, що розглядається головним запитом.

Запит:Перерахувати працівників, чиї погодинні ставки вищі, ніж ставки їхніх менеджерів.

SELECT WORKER_NAME

WHERE A.HRLY_RATE >

(SELECT B.HRLY_RATE

WHERE B.WORKER_ID = A.SUPV_ID)

Результат:

Логічні етапи виконання цього запиту такі:

1. Система створює дві копії таблиці WORKER: копію А та копію В. Відповідно до того, як ми їх визначили, А відноситься до працівника, В - до менеджера.

2. Потім система розглядає кожен рядок А. Цей рядок вибирається, якщо він задовольняє умову WHERE. Ця умова означає, що рядок буде обраний, якщо величина HRLY_RATE у ньому більша, ніж HRLY_RATE, породжена підзапитом.

3. Підзапит вибирає величину HRLY_RATE з рядка В, WORKER_ID якого дорівнює SUPV_ID рядка А, Наразіаналізованої основним запитом. Це менеджер HRLY_RATE.

Зверніть увагу, що оскільки A.HRLY_RATE може порівнюватись тільки з однією величиною, підзапит має видавати тільки одну величину. Ця величина змінюється залежно від того, який рядок розглядається. Таким чином, підзапит корелює з головним запитом. Ми зустрінемося з іншими прикладами корелованих підзапитів пізніше, коли вивчатимемо вбудовані функції.

Оператори EXISTS та NOT EXISTS

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

SELECT WORKER_ID

WHERE BLDG_ID NOT 435

На жаль, це неправильне формулювання рішення. Запит просто видасть нам ВД працівників, які працюють на інших будівлях. Очевидно, що деякі з них можуть бути призначені і на будівлю 435.

У правильно сформульованому рішенні використовується оператор NOT EXISTS (не існує):

SELECT WORKER_ID

WHERE NOT EXISTS

WHERE ASSIGNMENT.WORKER_ID = WORKER.WORKER_ID AND

Результат:

WORKER_ID

Оператори EXISTS і NOT EXISTS завжди розміщуються перед підзапитом. EXISTS набуває значення «істина», якщо множина, породжена підзапитом, не пуста. Якщо породжене підзапитом безліч порожньо, EXISTS набуває значення «брехня». Оператор NOT EXISTS, звичайно, працює в точності навпаки. Він дійсний, якщо результат підзапиту порожній, і кладуть інакше.

Оператор EXISTS. Приймає значення «істина», якщо результуюча множина не порожня.

Оператор NOT EXISTS. Приймає значення «істина», якщо результуюча множина порожня.

У цьому прикладі ми користувалися оператором NOT EXISTS. Підзапит вибирає всі такі рядки таблиці ASSIGNMENT, в яких WORKER_ID має те ж значення, що й у рядку, що розглядається головним запитом, а BLDG_ID дорівнює 435. Якщо це безліч порожньо, тоді рядок працівника, що розглядається головним запитом, вибирається, оскільки це означає, що цей працівник не працює на будівлі 435.

У наведеному нами рішенні використавши корельований підзапит. Якщо ми скористаємося замість NOT EXISTS оператором IN, то можемо обійтися некорельованим підзапитом:

SELECT WORKER_ID

WHERE WORKER_ID NOT IN

(SELECT WORKER_ID

WHERE BLDG_ID = 435)

Це рішення простіше, ніж рішення з оператором NOT EXISTS. Виникає природне питання, навіщо нам взагалі потрібні EXISTS та NOT EXISTS. Відповідь полягає в тому, що NOT EXISTS є єдиним засобом для вирішення запитів, що містять в умові слово «кожен». Такі запити вирішуються у реляційній алгебрі з допомогою операції поділу, а реляційному обчисленні - з допомогою квантора загальності. Наведемо приклад запиту, за умови якого є слово «кожен»:

Запит:Перерахувати працівників, призначених на кожну будівлю.

Це питання може бути реалізовано SQL за допомогою подвійного заперечення. Ми переформулюємо запит, включивши до нього подвійне заперечення:

Запит:Перерахувати таких працівників, для яких неіснує будівлі, на яку вони не призначені.

Ми виділили подвійне заперечення. Зрозуміло, що цей запит логічно еквівалентний попередньому.

Тепер ми хочемо сформулювати рішення SQL. Щоб спростити розуміння остаточного рішення, ми спочатку дамо рішення попереднього завдання: завдання ідентифікації всіх будівель, на які гіпотетичний працівник, «1234» непризначений.

(I) SELECT BLDG_ID

WHERE NOT EXISTS

ASSIGNMENT.WORKER_ID = 1234)

Ми помітили цей запит (I), оскільки ми посилатимемося на нього пізніше. Якщо не існує будівлі, яка задовольняє цей запит, то тоді працівник 1234 призначений на кожну будівлю і, отже, задовольняє умови вихідного запиту. Для того, щоб отримати рішення вихідного запиту, ми повинні узагальнити запит (I) з конкретного робочого 1234 на змінну WORKER_ID і перетворити цей модифікований запит на запит більшого запиту. Наведемо рішення:

(II) SELECT WORKER_ID

WHERE NOT EXISTS

WHERE NOT EXISTS

WHERE ASSIGNMENT.BLDG_ID = BUILDING.BLDG_ID AND

ASSIGNMENT.WORKER_ID = WORKER.WORKER_ID)

Результат:

WORKER ID

Зверніть увагу, що підзапит, що починається з четвертого рядка запиту (II), ідентичний запиту (I), у якому "1234" замінено WORKER.WORKER_ID. Запит (II) можна прочитати так:

Вибрати WORKER_ID з WORKER, якщо немає будівлі, на яку WORKER_ID не призначений.

Це відповідає умовам вихідного запиту.

Ми бачимо, що оператор NOT EXISTS може використовуватися для формулювання тих запитів, при вирішенні яких в реляційній алгебрі була потрібна операція поділу, а в реляційному обчисленні - квантор загальності. З точки зору простоти використання оператор NOT EXISTS не дає ніяких особливих переваг, тобто в запитах SQL, в яких двічі використовується NOT EXISTS, нітрохи не простіше розібратися, ніж у рішеннях алгебри реляційної з операцією поділу або рішеннях реляційного обчислення з квантором загальності. Для створення мовних конструкцій, що дозволяють більш природно вирішувати подібні запити, будуть потрібні додаткові дослідження.

Вбудовані функції

Розглянемо питання такого типу:

Які максимальна та мінімальна погодинні ставки? Яка середня кількість днів роботи службовців на будівлі 435? Яка загальна кількість днів, відведених на штукатурні роботи на будівлі 312? Скільки всього різних спеціальностей?

Для відповіді на ці питання потрібні статистичні функції, які розглядають безліч рядків таблиці та видають одне значення. У SQL є п'ять таких функцій, які називаються вбудованими функціями або функціями множини. Це функції SUM (сума), AVG (середня), COUNT (кількість), МАХ (максимум) та MIN (мінімум).

Вбудована функція (функція множини). Статистична функція, що оперує безліччю рядків: SUM (сума), AVG (середня), COUNT (кількість), МАХ (максимум), MIN (мінімум).

Запит:Які максимальна та мінімальна погодинні ставки?

SELECT MAX(HRLY_RATE), MIN(HRLY_RATE)

Результат: 17.40, 8.20

Функції MAXта MIN оперують одним стовпцем таблиці. Вони вибирають максимальне чи мінімальне значення, відповідно, із цього стовпця. Формулювання нашого запиту не містить команди WHERE. Більшість запитів може бути негаразд, як показує наш наступний приклад.

Запит:Яка середня кількість днів роботи службовців на будівлі 435?

SELECT AVG(NUM_DAYS)

WHERE BLDG_ID =435

Результат: 12.33

Запит:Яка загальна кількість днів, відведених на штукатурні роботи на будівлі 312?

SELECT SUM(NUM_DAYS)

FROM ASSIGNMENT, WORKER

WHERE WORKER.WORKER_ID = ASSIGNMENT.WORKER_ID AND

SKILL_TYPE = "Штукатури" AND

Результат: 27

У рішенні використовується з'єднання таблиць ASSIGNMENT та WORKER. Це необхідно, оскільки SKILL_TYPE знаходиться у таблиці WORKER, а BLDG_ID - у таблиці ASSIGNMENT.

Запит:Скільки всього різних спеціальностей?

SELECT COUNT (DISTINCT SKILL_TYPE)

Результат: 4

Оскільки один і той самий фах може повторюватися у кількох різних рядках, у цьому запиті необхідно використовувати ключове слово DISTINCT (різний), щоб система не порахувала той самий тип спеціальності більше разу. Оператор DISTINCT може використовуватися з будь-якою із вбудованих функцій, хоча, зрозуміло, з функціями МАХ та MIN він надмірний.

DISTINCT. Оператор, що виключає рядки, що повторюються.

Функції SUM та AVG повинні використовуватись лише з числовими стовпцями. Інші функції можуть використовуватися і з числовими і символьними даними. Усі функції, крім COUNT, можна використовувати з обчислюваним виразом. Наприклад:

Запит:Яка середня тижнева зарплата?

SELECT AVG (40*HRLY_RATE)

Результат: 509.14

COUNT може посилатися на рядок повністю, а не на окремий стовпець :

Запит: Скільки будівель мають рівень якості З?

SELECT COUNT (*)

FROM BUILDING WHERE

Результат: 3

Як показують усі ці приклади, якщо у команді SELECT стоїть вбудована функція, то більше у цій команді SELECT нічого не може стояти. Єдиний виняток із цього правила пов'язаний із фразою GROUP BY, яку ми зараз розглянемо.

Фрази GROUP BY та HAVING

У менеджменті часто потрібна статистична інформація про кожну групу в багатьох групах. Наприклад, розглянемо наступний запит:

Запит:Для кожного менеджера з'ясувати максимальну погодинну ставку серед його підлеглих.

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

GROUP BY SUPV_ID

Результат:

SUPV_IDMAX(HRLY RATE)

Під час обробки цього запиту система спочатку розбиває рядки таблиці WORKER на групи за таким правилом. Рядки поміщаються в одну групу тоді і тільки тоді, коли вони збігаються SUPV_ID. Потім фраза SELECT застосовується до кожної групи. Оскільки у цій групі лише одне значення SUPV_ID, то жодної невизначеності SUPV_ID у групі немає. Для кожної групи фраза SELECT виводить SUPV_ID, а також обчислює та виводить значення MAX(HRLY_RATE). Результат наведено вище.

У команді SELECT із вбудованими функціями можуть зустрічатися лише стовпці, які входять у фразу GROUP BY. Зауважте, що SUPV_ID може використовуватися в команді SELECT, оскільки він входить у фразу GROUP BY.

Фраза GROUP BY. Означає, що рядки мають бути розбиті на групи із загальними значеннями вказаного стовпця (стовпців).

Фраза GROUP BY дозволяє виконувати певні складні обчислення. Наприклад, нам може знадобитися з'ясувати середнє значення цих максимальних ставок. Однак, обчислення з вбудованими функціями обмежені в тому сенсі, що не дозволяється використання вбудованих функцій всередині інших вбудованих функцій. Таким чином, вираз типу

AVG(MAX(HRLY_RATE))

заборонено. Реалізація такого запиту складатиметься з двох етапів. Спочатку ми повинні помістити максимальні ставки в нову таблицю, а на другому етапі обчислити їхнє середнє.

З командою GROUP BY можна використовувати команду WHERE:

Запит:Для кожного типу будівель з'ясувати середній рівеньЯкість серед будівель статусу 1.

SELECT TYPE, AVG(QLTY_LEVEL)

WHERE STATUS = 1

Результат:

TYPEAVG(QLTY_LEVEL)

Магазин 1

Житловий будинок 3

Фраза WHERE виконується перед командою GROUP BY. Таким чином, жодна група не може містити рядок, в якому статус відмінний від 1. Рядки статусу 1 групуються за значенням TYPE, а потім до кожної групи застосовується фраза SELECT.

Фраза HAVING. Накладає умови на групи.

Ми також можемо застосовувати умови до груп, створених фразою GROUP BY. Це робиться за допомогою фрази HAVING. Припустимо, наприклад, що ми вирішили конкретизувати один із попередніх запитів:

Запит: Для кожного менеджера, у якого більше одного підлеглого, з'ясувати максимальну погодинну ставку серед його підлеглих

Ми можемо відобразити цю умову відповідною командою HAVING:

SELECT SUPV_ID, MAX(HRLY_RATE)

FROM WORKER GROUP BY SUPV_ID

HAVING COUNT(*) > 1

Результат:

SUPV_ID MAX(HRLY_RATE)

Різниця між фразами WHERE та HAVING полягає в тому, що WHERE застосовується до рядків, у той час як HAVING застосовується до груп.

Запит може містити і команду WHERE, і HAVING. У цьому випадку першою виконується фраза WHERE, оскільки вона виконується до розбиття на групи. Наприклад, розглянемо наступну модифікацію наведеного раніше запиту:

Запит: Для кожного типу будівель з'ясувати середній рівень якості серед будівель статусу 1. Розглядати тільки типи будівель, максимальний рівень якості яких не перевищує 3.

SELECT TYPE, AVG (QLTY_JLEVEL)

WHERE STATUS = 1

HAVING MAX(QLTY_LEVEL)<= 3

Результат:

TYPE AVG(QLTY_LEVEL)

Магазин 1

Житловий будинок 3

Зверніть увагу, що починаючи з фрази FROM, фрази виконуються по порядку, а потім застосовується фраза SELECT. Так, до таблиці BUILDING застосовується фраза WHERE, і всі рядки, у яких STATUS відмінний від 1, видаляються. рядки, що залишилися, групуються за TYPE; усі рядки з однаковим значенням TYPE виявляються в одній групі. Таким чином, створюється кілька груп, по одній для кожного значення TYPE. Потім до кожної групи застосовується фраза HAVING, і групи, у яких максимальне значення рівня якості перевищує 3, видаляються. Нарешті, до груп, що залишилися, застосовується фраза SELECT.

7. Вбудовані функції та підзапити

Вбудовані функції можуть використовуватися лише у фразі SELECT або команді HAVING. Однак фраза SELECT, що містить вбудовану функцію, може бути частиною підзапиту. Розглянемо приклад такого підзапиту:

Запит:У кого з працівників погодинна ставка вища за середню?

SELECT WORKER_NAME

WHERE HRLY_RATE >

(SELECT AVG(HRLY_RATE)

Результат:

Х. Колумб

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

Кореляційні запити також можуть використовувати вбудовані функції:

Запит: У кого з працівників погодинна ставка вища за середню погодинну ставку серед підлеглих того ж менеджера?

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

SELECT A. WORKER_NAME

SQL дозволяє вкладати запити один одного. Зазвичай підзапит повертає одне значення, яке перевіряється щодо істинності предиката.

Види умов пошуку:
. Порівняння з результатом вкладеного запиту (=, >=)
. Перевірка на належність результатам підзапиту (IN)
. Перевірка на існування (EXISTS)
. Багаторазове (кількісне) порівняння (ANY, ALL)

Примітки щодо вкладених запитів:
. Підзапит має вибирати лише один стовпець (за винятком підзапиту з предикатом EXISTS), і тип даних його результату повинен відповідати типу даних значення, вказаному у предикаті.
. У ряді випадків можна використовувати ключове слово DISTINCT для забезпечення єдиного значення.
. У вкладеному запиті не можна вмикати розділ ORDER BY та UNION.
. Підзапит може знаходитися і ліворуч і праворуч від умови пошуку.
. У підзапитах можуть використовуватися функції агрегування без розділу GROUP BY, які автоматично видають спеціальне значення для будь-якої кількості рядків, спеціальний предикат IN, а також вирази, що базуються на стовпцях.
. За можливості слід замість підзапитів використовувати з'єднання таблиць JOIN.

Приклади на вкладені запити:

SELECT * FROM Orders WHERE SNum = (SELECT SNum FROM SalesPeople WHERE SName = 'Motika')
SELECT * FROM Orders WHERE SNum IN (SELECT SNum FROM SalesPeople WHERE City='London')
SELECT * FROM Orders WHERE SNum = (SELECT DISTINCT SNum FROM Orders WHERE CNum = 2001)
SELECT * FROM Orders WHERE Amt> (SELECT AVG (Amt) FROM Orders WHERE Odate = 10/04/1990)
SELECT * FROM Customer WHERE CNum=(SELECT SNum+1000 FROM SalesPeople WHERE SName='Serres')

2) Пов'язані підзапити

SQL можна створювати підзапити з посиланням на таблицю із зовнішнього запиту. У цьому випадку підзапит виконується багаторазово, по одному разу для кожного рядка таблиці із зовнішнього запиту. Тому важливо, щоб підзапит використовував індекс. Підзапит може звертатися до тієї ж таблиці, щоб і зовнішній. Якщо зовнішній запит повертає відносно невелику кількість рядків, то пов'язаний підзапит працюватиме швидше за незв'язаний. Якщо підзапит повертає невелику кількість рядків, то пов'язаний запит виконається повільніше незв'язаного.

Приклади на пов'язані підзапити:

SELECT * FROM SalesPeople Main WHERE 1(SELECT AVG(Amt) FROM Orders O2 WHERE O2.CNum=O1.CNum) //повертає всі замовлення, величина яких перевищує середню величину замовлення даного покупця

3) Предикат EXISTS

Синтаксична форма: EXISTS ()

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

Примітки щодо предикату EXISTS:
. EXISTS - предикат, що повертає значення TRUE або FALSE, і його можна застосовувати окремо або разом з іншими булевими виразами.
. EXISTS не може використовувати функції агрегування у своєму підзапиті.
. У корелюючих (пов'язаних, залежних – Correlated) підзапитах предикат EXISTS виконується для кожного рядка зовнішньої таблиці.
. Можна комбінувати предикат EXISTS із з'єднаннями таблиць.

Приклади на предикат EXISTS:

SELECT * FROM Customer WHERE EXISTS (SELECT * FROM Customer WHERE City = San Jose) - повертає всіх покупців, якщо хтось з них проживає в San Jose.
SELECT DISTINCT SNum FROM Customer First WHERE NOT EXISTS (SELECT * FROM Customer Send WHERE Send. SNum = First. SNum AND Send.
SELECT DISTINCT F.SNum, SName, F.City FROM SalesPeople F, Customer S WHERE EXISTS (SELECT * FROM Customer T WHERE S.SNum=T.SNum AND S.CNumT.CNum AND F.SNum=S.SNum) – повертає номери, імена та міста проживання всіх продавців, які обслужили кількох покупців.
SELECT * FROM SalesPeople Frst WHERE EXISTS (SELECT * FROM Customer Send WHERE Frst.SNum=Send.SNum AND 1

4) Предикати кількісного порівняння

Синтаксична форма: (=|>|=|) ANY|ALL ()

Ці предикати використовують як аргумент підзапит, однак, порівняно з предикатом EXISTS, вони застосовуються в кон'юнкції з предикатами відносини (=,>=). У цьому сенсі вони подібні до предикату IN, але застосовуються тільки з підзапитами. Стандарт дозволяє використовувати замість ANY ключове слово SOME, проте не всі СУБД його підтримують.

Примітки щодо предикатів порівняння:
. Предикат ALL приймає значення TRUE, якщо кожне значення, вибране під час виконання підзапиту, задовольняє умові, заданому в предикаті зовнішнього запиту. Найчастіше він використовується з нерівностями.
. Предикат ANY приймає значення TRUE, якщо хоча б одне значення, вибране у процесі виконання підзапиту, задовольняє умову, задану в предикаті зовнішнього запиту. Найчастіше він використовується з нерівностями.
. Якщо підзапит не повертає рядків, то ALL автоматично набуває значення TRUE (вважається, що умова порівняння виконується), а ANY – FALSE.
. Якщо порівняння не має значення TRUE для жодного рядка і є один або кілька рядків з NULL значенням, то ANY повертає UNKNOWN.
. Якщо порівняння не має значення FALSE для жодного рядка і є один або кілька рядків з NULL значенням, то ALL повертає UNKNOWN.

Приклади на предикат кількісного порівняння:

SELECT * FROM SalesPeople WHERE City = ANY (SELECT City FROM Customer)
SELECT * FROM Ордери WHERE Amt ALL (SELECT Rating FROM Customer WHERE City = 'Rome')

5) Предикат унікальності

UNIQUE | DISTINCT ()

Предикат служить для перевірки унікальності (відсутності дублів) у вихідних даних підзапиту. Причому в предикаті UNIQUT рядки з значеннями NULL вважаються унікальними, а в предикаті DISTINCT два невизначені значення вважаються рівними один одному.

6) Предикат збігів

MATCH ()

Предикат MATCH перевіряє, чи значення рядка запиту співпадатиме зі значенням будь-якого рядка, отриманого в результаті підзапиту. Від предикатів IN і ANY такий підзапит відрізняється тим, що дозволяє обробляти «часткові» (PARTIAL) збіги, які можуть зустрічатися серед рядків, що мають частину NULL-значень.

7) Запити у розділі FROM

Фактично допустимо використовувати підзапит скрізь, де допускається посилання таблицю.

SELECT CName, Tot_Amt FROM Customer, (SELECT CNum, SUM(Amt) AS Tot_Amt FROM Ордени GROUP BY CNum) WHERE City='London' AND Customer.CNum=Orders.CNum
//Підзапит повертає сумарну величину замовлень, зроблених кожним покупцем з Лондона.

8) Рекурсивні запити

WITH RECURSIVE
Q1 AS SELECT … FROM … WHERE …
Q2 AS SELECT … FROM … WHERE …