Різниця між EXISTS і IN в SQL? (14)

У чому різниця між пропозицією EXISTS і IN в SQL?

Коли ми повинні використовувати EXISTS, і коли ми повинні використовувати IN?

EXISTS набагато швидше, ніж IN коли результати підзапиту дуже великі.
IN швидше, ніж EXISTS коли результати підзапиту дуже малі.

CREATE TABLE t1 (id INT, title VARCHAR (20), someIntCol INT) GO CREATE TABLE t2 (id INT, t1Id INT, someData VARCHAR (20)) GO INSERT INTO t1 SELECT 1, "title 1", 5 UNION ALL SELECT 2 , "title 2", 5 UNION ALL SELECT 3, "title 3", 5 UNION ALL SELECT 4, "title 4", 5 UNION ALL SELECT , "title 5", 5 UNION ALL SELECT , "title 6", 5 INSERT INTO t2 SELECT 1, 1, "data 1" UNION ALL SELECT 2, 1, "data 2" UNION ALL SELECT 3, 2, "data 3" UNION ALL SELECT 4, 3, "data 4" UNION ALL SELECT 5 , 3, "data 5" UNION ALL SELECT 6, 3, "data 6" UNION ALL SELECT 7, 4, "data 7" UNION ALL SELECT 8, , "data 8" UNION ALL SELECT 9, 6, "data 9 "UNION ALL SELECT 10, 6," data 10 "UNION ALL SELECT 11, 8," data 11 "

запит 1

SELECT FROM t1 WHERE not EXISTS (SELECT * FROM t2 WHERE t1.id \u003d t2.t1id)

запит 2

SELECT t1. * FROM t1 WHERE t1.id not in (SELECT t2.t1id FROM t2)

Якщо в t1 ваш ідентифікатор має нульове значення, Query 1 знайде їх, але Query 2 не зможе знайти нульові параметри.

Я маю на увазі, що IN не може порівнювати нічого з нульовим, тому він не має результату для , але EXISTS може порівнювати все з нульовим.

EXISTS швидше, ніж IN. Якщо більшість критеріїв фільтра знаходяться в підзапиті, тоді краще використовувати IN і якщо більшість критеріїв фільтра знаходиться в основному запиті, то краще використовувати EXISTS.

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

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

Ви можете використовувати підзапит, щоб перевірити, чи існує набір записів. Для цього вам потрібно використовувати пропозицію exists з підзапитом. Ключове слово exists завжди повертає значення true або false.

Який з них швидше залежить від кількості запитів, отриманих внутрішнім запитом:

• Коли ваш внутрішній запит вибере тисячу рядків, тоді EXIST буде кращим вибором
• Коли ваш внутрішній запит вибирає кілька рядків, тоді IN буде швидше

EXIST оцінює значення true або false, але IN порівнює множинне значення. Коли ви не знаєте, що запис існує чи ні, ви повинні вибрати EXIST

Ключове слово Exists оцінює значення true або false, але ключове слово IN порівнює все значення у відповідному стовпці підзапиту. Інший Select 1 можна використовувати з командою Exists. приклад:

SELECT * FROM Temp1 where exists (select 1 from Temp2 where conditions ...)

Але IN менш ефективний, тому Exists швидше.

На основі оптимізатора правил :

• EXISTS набагато швидше, ніж IN, коли результати підзапиту дуже великі.
• IN швидше, ніж EXISTS, коли результати суб-запиту дуже малі.

На основі оптимізатора витрат :

• Нема ніякої різниці.

За моїми відомостями, коли підзапит повертає значення NULL весь оператор стає NULL. В цьому випадку ми використовуємо ключове слово EXITS. Якщо ми хочемо порівняти певні значення в підзапитах, то ми використовуємо ключове слово IN.

EXISTS - це коли вам потрібно зіставити результати запиту з іншим підзапитом. Результати запиту # 1 повинні бути отримані, коли результати SubQuery збігаються. Вид Приєднатися .. Наприклад, виберіть таблицю клієнтів №1, які також розмістили таблицю заявок # 2

IN слід витягувати, якщо значення певного стовпця знаходиться в списку (1,2,3,4,5). Наприклад, виберіть клієнтів, які лежать в наступних zipcodes, тобто значення zip_code знаходяться в списку (....).

Коли використовувати один над іншим ... коли ви відчуваєте, що він читає відповідним чином (спілкується з кращими намірами).

Я виявив, що використання ключового слова EXISTS часто дуже повільне (це дуже вірно в Microsoft Access). Замість цього я використовую оператор об'єднання наступним чином:

Я припускаю, що ви знаєте, що вони роблять, і тому використовуються по-різному, тому я буду розуміти ваше запитання таким чином: Коли було б гарною ідеєю переписати SQL для використання IN замість EXISTS або навпаки.

Це справедливе припущення?

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

наприклад:

SELECT * FROM Customers WHERE EXISTS (SELECT * FROM Orders WHERE Orders.CustomerID \u003d Customers.ID)

можна переписати в:

SELECT * FROM Customers WHERE ID IN (SELECT CustomerID FROM Orders)

або з з'єднанням:

SELECT Customers. * FROM Customers INNER JOIN Orders ON Customers.ID \u003d Orders.CustomerID

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

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

Якщо ви розробник MS SQL, ось відповідь від Microsoft.

Визначає, чи відповідає вказаному ступені будь-якому значенню в підзапиті або списку.

Задає підзапит для перевірки існування рядків.

In certain circumstances, it is better to use IN rather than EXISTS. In general, if the selective predicate is in the subquery, then use IN. If the selective predicate is in the parent query, then use EXISTS.

IN підтримує тільки відносини рівності (або нерівності, коли передує NOT).
це синонім \u003d any / \u003d деякі , наприклад

Select * from t1 where x in (select x from t2);

EXISTS підтримує варіанти типів відносин, які не можуть бути виражені за допомогою IN , Наприклад -

Select * from t1 where exists (select null from t2 where t2.x \u003d t1.x and t2.y\u003e t1.y and t2.z like "℅" || t1.z || "℅");

І на іншу ноту -

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

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

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

Select * from a, b, c where a.id \u003d b.id, b.id \u003d c.id

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

Select * from a join b on a.id \u003d b.id join c on b.id \u003d 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 \u003d a.id) select * from a join b on a.id \u003d b. id select * from a where a.id not in (select id from b) select * from a where not exists (select top 1 + 1 from b where b.id \u003d a.id) select * from a left join b on a. id \u003d 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 \u003d sd.M_PG_DEPENDENCY_CHILD_ID), PRODUCT d, PRODUCT_GROUP dg left join M_PG_DEPENDENCY dd on (dg.PRODUCT_GROUP_ID \u003d dd.M_PG_DEPENDENCY_CHILD_ID) where s.PRODUCT_GROUP_ID \u003d sg .PRODUCT_GROUP_ID and d.PRODUCT_GROUP_ID \u003d dg.PRODUCT_GROUP_ID and sg.PRODUCT_GROUP_PERSPEC \u003d dg.PRODUCT_GROUP_PERSPEC and sg.PRODUCT_GROUP_NAME \u003d dg.PRODUCT_GROUP_NAME and s.PRODUCT_NAME \u003d d.PRODUCT_NAME and s.PRODUCT_TYPE \u003d d.PRODUCT_TYPE and s.PRODUCT_IS_SECURE \u003d d.PRODUCT_IS_SECURE and s.PRODUCT_MULTISELECT \u003d d.PRODUCT_MULTISELECT and dg.PRODUCT_GROUP_IS_TMPL \u003d 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 \u003d sg1.PRODUCT_GROUP_ID and dd .M_PG_DEPENDENCY_PARENT_ID \u003d dg1.PRODUCT_GROUP_ID and sg1.PRODUCT_GROUP_PERSPEC \u003d dg1.PRODUCT_GROUP_PERSPEC and sg1.PRODUCT_GROUP_NAME \u003d dg1.PRODUCT_GROUP_N AME and))

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

Select d.PRODUCT_ID from PRODUCT s join PRODUCT d on s.PRODUCT_TYPE \u003d d.PRODUCT_TYPE and s.PRODUCT_NAME \u003d d.PRODUCT_NAME and s.PRODUCT_IS_SECURE \u003d d.PRODUCT_IS_SECURE and s.PRODUCT_MULTISELECT \u003d d.PRODUCT_MULTISELECT join PRODUCT_GROUP sg on s.PRODUCT_GROUP_ID \u003d sg.PRODUCT_GROUP_ID join PRODUCT_GROUP dg on d.PRODUCT_GROUP_ID \u003d dg.PRODUCT_GROUP_ID and sg.PRODUCT_GROUP_NAME \u003d dg.PRODUCT_GROUP_NAME and sg.PRODUCT_GROUP_PERSPEC \u003d dg.PRODUCT_GROUP_PERSPEC left join M_PG_DEPENDENCY sd on sg.PRODUCT_GROUP_ID \u003d sd.M_PG_DEPENDENCY_CHILD_ID left join M_PG_DEPENDENCY dd on dg.PRODUCT_GROUP_ID \u003d dd.M_PG_DEPENDENCY_CHILD_ID left join PRODUCT_GROUP sgp on sgp.PRODUCT_GROUP_ID \u003d sd.M_PG_DEPENDENCY_PARENT_ID left join PRODUCT_GROUP dgp on dgp.PRODUCT_GROUP_ID \u003d dd.M_PG_DEPENDENCY_PARENT_ID and sgp.PRODUCT_GROUP_NAME \u003d dgp.PRODUCT_GROUP_NAME and isnull (sgp.PRODUCT_GROUP_IS_TMPL, 0) \u003d 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 \u003d 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 \u003d "Штукатур"

результат:

Г.Ріковер

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

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

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

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

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

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

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

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

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

WHERE TYPE \u003d "Офіс"

результат:

BLDG IDАДРЕСTYPEQLTY LEVELSTATUS

312 Ул.Вязов, 123 Офіс 2 + 2

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

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

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

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

SELECT NAME, "Недільна зарплата = ", 40 * HRLY_RATE

WHERE SKILL_TYPE \u003d "Електрик"

результат:

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

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

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

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

запит: У кого погодинна ставка від 10 до 12 доларів?

WHERE HRLY_RATE\u003e \u003d 10 AND HRLY_RATE< - 12

результат:

WORKER ID NAME HRLY_RATE SKILL_TYPE SUPV_ID

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

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

булеві операції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 (В). Умова 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 \u003d 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 \u003d ASSIGNMENT.WORKER_ID

AND BLDG_ID \u003d 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 \u003d ASSIGNMENT.WORKER_ID

2. BLDG_ID \u003d 435

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

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

SKILL TYPE

штукатур

покрівельник

Електрик

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

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

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

SELECT А.WORKER_NAME, B.WORKER_NAME

FROM WORKER A, WORKER В

WHERE B.WORKER_ID \u003d A.SUPV_ID

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

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

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

А.NAMEВ.NAME

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

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

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

Ім'я користувача. Альтернативне ім'я, дане таблиці.

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

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

запит

SELECT WORKER_NAME

FROM WORKER, ASSIGNMENT, BUILDING

WHERE WORKER.WORKER_ID \u003d ASSIGNMENT.WORKER_ID AND ASSIGNMENT.BLDG_ID \u003d BUILDING.BLDG_ID AND

TYPE \u003d "Офіс"

результат:

М.Фарадей

Г.Ріковер

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

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

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

TYPE \u003d "Офіс"

команди 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 \u003d "Офіс"))

результат:

М.Фарадей

Г.Ріковер

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

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

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

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

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

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

SELECT WORKER_NAME

WHERE A.HRLY_RATE\u003e

(SELECT B.HRLY_RATE

WHERE B.WORKER_ID \u003d 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 \u003d 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 \u003d 435)

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

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

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

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

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

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

(I) SELECT BLDG_ID

WHERE NOT EXISTS

ASSIGNMENT.WORKER_ID \u003d-1234)

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

(II) SELECT WORKER_ID

WHERE NOT EXISTS

WHERE NOT EXISTS

WHERE ASSIGNMENT.BLDG_ID \u003d BUILDING.BLDG_ID AND

ASSIGNMENT.WORKER_ID \u003d 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 \u003d 435

результат: 12.33

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

SELECT SUM (NUM_DAYS)

FROM ASSIGNMENT, WORKER

WHERE WORKER.WORKER_ID \u003d ASSIGNMENT.WORKER_ID AND

SKILL_TYPE \u003d "Штукатур" AND

результат: 27

У рішенні використовується з'єднання таблиць ASSIGNMENT і WORKER. Це необхідно, тому що SKILL_TYPE знаходиться в таблиці WORKER, a 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, a також обчислює і виводить значення 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 \u003d 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 (*)\u003e 1

результат:

SUPV_ID MAX (HRLY_RATE)

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

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

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

SELECT TYPE, AVG (QLTY_JLEVEL)

WHERE STATUS \u003d 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\u003e

(SELECT AVG (HRLY_RATE)

результат:

Х. Колумб

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

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

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

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

SELECT A. WORKER_NAME