Інтернет

Оператор для наборів даних EXCEPT - загальні правила. Виконання складних SQL запитів Intersect sql приклади

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

Нюанси

Мене часто питають, а для кого ця стаття? Але, повірте, не завжди легко дати відповісти: з одного боку, є ніндзя розробники, яких складно чимось здивувати, а з іншого - молоді падовані. Але одне точно можу сказати - для читача, якого цікавить SQL, здатний доповнювати свою багату картину дрібними, але дуже цікавими деталями. У цій статті не буде кілометрових сторінок SQL-запиту, максимум 1, 2 рядки і тільки те, що зустрічається на мій погляд рідко. Але так як я хочу бути до кінця відвертим, якщо Ви з SQL на ти, стаття здасться нудною. Усі приклади у статті, крім першого і четвертого можна віднести до стандарту SQL-92.

Дані

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

Скрипти та таблиця з даними

CREATE TABLE goods(id bigint NOT NULL, name character varying(127) NOT NULL, description character varying(255) NOT NULL, price numeric(16,2) NOT NULL, article character varying(20) NOT NU , availability boolean NOT NULL, CONSTRAINT pk_goods PRIMARY KEY (id)); INSERT INTO хороші (id, name, description, price, articul, act_time, availability) VALUES (1, "Тапочки", "М'які", 100.00, "TR-75", (ts "2017-01-01 01:01: 01.01"), TRUE); INSERT INTO хороші (id, name, description, price, articul, act_time, availability) VALUES (2, "Подушка", "Біла", 200.00, "PR-75", (ts "2017-01-02 02:02: 02.02 "), TRUE); INSERT INTO хороші (id, name, description, price, articul, act_time, availability) VALUES (3, "Ковдра", "Пухова", 300.00, "ZR-75", (ts "2017-01-03 03:03: 03.03 "), TRUE); INSERT INTO хороші (id, name, description, price, articul, act_time, availability) VALUES (4, "Наволочка", "Сіра", 400.00, "AR-75", (ts "2017-01-04 04:04: 04.04 "), FALSE); INSERT INTO хороші (id, name, description, price, articul, act_time, availability) VALUES (5, "Простирадло", "Шовкова", 500.00, "BR-75", (ts "2017-01-05 05:05: 05.05 "), FALSE);

Запити

1. Подвійні лапки

І перше що у мене є – це просте питання: Чи змогли б Ви навести приклад sql-запиту з використанням подвійнихлапок?Так, не з одинарними, подвійними?

Приклад із подвійними лапками

SELECT name "Ім'я товару" FROM goods

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

Може здатися, що це не дуже корисний приклад реальної розробки. Для мене це не так. Тепер я його активно використовую у всіх своїх sql-заготовках. Суть проста, коли повертаєшся через півроку до sql-запиту з 40 колонок, ой як рятує "нашську" їхню назву. Незважаючи на те, що я не вказав про SQL-92, в останній редакції згадка про подвійні лапки є.

2. Псевдо таблиця. SQL-92

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

Псевдо таблиця

SELECT mock.nickname "Прізвисько", (CASE WHEN mock.huff THEN "Так" ELSE "Ні" END) "Ображається?" FROM (SELECT name AS nickname, availability AS huff FROM goods) mock

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

3. Конструктор блоку даних. SQL-92

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

Приклад конструктора блоку даних

SELECT name "Ім'я товару", price "Ціна" FROM (VALUES ("Тапочки", 100.00), ("Подушка", 200.00)) AS goods(name, price)

Ім'я товару	Ціна
Капці	100.00
Подушка	200.00

У секції FROMвикористовується ключове слово VALUES, за яким у дужках дані, рядок за рядком. Суть у тому, що ми взагалі не вибираємо дані з якоїсь таблиці, а просто створюємо їх нальоту, "називаємо" таблицею, називаємо колонки і далі використовуємо на власний розсуд. Ця штука виявилася вкрай корисною при тестуванні різних кейсів sql-запиту, коли даних для деяких таблиць немає (у Вашій локальній БД), а писати в insert ліньки або іноді дуже складно, зважаючи на пов'язаність таблиць і обмежень.

4. Час, Дата та Час-і-Дата

Напевно, кожен стикався в запитах, з необхідністю вказівки часу, дати або дати-і-часу. Багато СУБД підтримуються літерали t, d і ts відповідно до роботи з цими типами. Але простіше пояснити з прикладу: Для літералів d і t все аналогічно.
Перепрошую у читача, що ввів в оману, але все, що сказано в пункті 4 не відноситься до мови SQL, а відноситься до можливостей попередньої обробки запитів в JDBC.

5. Заперечення. SQL-92

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

6. Порівняння блоків даних. SQL-92

Вкотре перепрошую за термінологію. Це один із улюблених моїх прикладів

Приклад порівняння блоків даних

SELECT * FROM goods WHERE (name, price, availability) = ("Наволочка", 400.00, FALSE) - або його аналог SELECT * FROM goods WHERE name = "Наволочка" AND price = 400.00 AND availability = FALSE

Як видно з прикладу, порівняння блоків даних аналогічно порівнянню поелементно значення_ 1 _block_1 = значення_ 1 _block_2, значення_ 2 _block_1 = значення_ 2 _block_2, значення_ 3 _block_1 = значення_ 3 _block_2з використанням ANDміж ними.

7. Оператори порівняння з модифікаторами ANY, SOME чи ALL. SQL-92

Ось тут потрібне пояснення. Але як завжди, спочатку приклад Що означає ALLв даному випадку? А означає він те, що умовою вибірки задовольняють ті рядки, ідентифікатори яких (у нашому випадку це 4 і 5), більше будь-якогоіз знайдених значень у підзапиті (1, 2 та 3). 4 більше ніж 1 і 2 і 3. 5 аналогічно. Що буде, якщо ми замінимо ALLна ANY?
Що означає ANYв даному випадку? А означає він те, що умовою вибірки задовольняють ті рядки, ідентифікатори яких (у нашому випадку це 2, 3, 4 і 5), більше хоча б одногоіз знайдених значень у підзапиті (1, 2 та 3). Для себе я асоціював ALLз AND, а ANYз OR. SOMEі ANYаналоги між собою.

8. Оператори роботи із запитами/під запитами. SQL-92

Досить відомо, що можна поєднати 2 запити між собою за допомогою операторів UNIONабо UNION ALL. Цим користуються часто. Але існують ще 2 оператори EXCEPTі INTERSECT.

Приклад із EXCEPT

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

Редакція

N1. Дякуємо streetflush за конструктивну критику. Вніс статтю інформацію про те, що є стандартом мови, а що ні.
N2. Виправлено пункт 4, з поясненням про те, що ts/d/t не є частиною мови SQL. Дякуємо за уважність Melkij.

Існує лише одне важливе правиловикористання інструкції EXCEPT, яку потрібно запам'ятати.

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

Відповідно до стандарту ANSI оператори роботи з наборами UNION та EXCEPT мають однаковий пріоритет, проте оператор INTERSECT виконується перед іншими операторами для наборів. Ми рекомендуємо явно керувати пріоритетом операторів, використовуючи дужки. Це взагалі є дуже гарною практикою.

Відповідно до стандарту ANSI у запиті можна використовувати лише одну пропозицію ORDER BY. Вставляйте його в кінець останньої інструкції SELECT. Щоб уникнути двозначності у вказівці стовпців і таблиць, обов'язково привласнюйте той самий псевдонім усім відповідним один одному стовпцям таблиць. Наприклад:

SELECT au_lname AS "lastname", au_fname AS "firstname" FROM authors EXCEPT SELECT emp_lname AS "lastname", emp_fname AS "firstname" FROM employees ORDER BY lastname, firstname;

Крім того, оскільки в кожному списку стовпців стовпці можуть вказуватися відповідно сумісними типами даних, на різних платформах СУРБД можуть зустрічатися різні варіанти роботи зі стовпцями різної довжини. Наприклад, якщо стовпець au_lname з першого запиту в попередньому прикладі значно довше, ніж стовпець emp_lname з другого запиту, різні платформи можуть застосовувати різні правила визначення довжини кінцевого результату. Але, взагалі кажучи, платформи вибиратимуть для результату довший (і менш обмежений) розмір.

Кожна СУРБД може застосовувати свої правила використання імені стовпця у разі, якщо імена у списках стовпців різняться. У випадку використовуються імена стовпців першого запиту.

Типи даних не обов'язково мають бути ідентичними, але вони повинні бути сумісні. Наприклад, типи CHAR та VARCHAR сумісні. За замовчуванням для результуючого набору в кожному стовпчику буде використовуватися розмір, що відповідає найбільшому типу кожного конкретного положення. Наприклад, запит, який отримує дані зі стовпців, що містять значення типу VARCHAR(IO) і VARCHAR(15), буде використовувати тип і розмір VARCHAR(15).

Жодна з платформ не підтримує пропозицію CORRESPONDING )) EXCEPT

(SELECT statemenr.2 | VALUES (expressionl, expression2 [, …])) EXCEPT

Дозволяє вказувати один або кілька стовпців, що задаються вручну, які включаються в остаточний результуючий набір. (Це називається конструктором рядків.) У пропозиції VALUES має бути вказано рівно стільки стовпців, скільки їх вказується у запитах оператора EXCEPT. Хоча інструкція EXCEPT DISTINCT не підтримується, функціональним еквівалентом є EXCEPT. Пропозиція CORRESPONDING не підтримується. Крім того, типи даних LONG VARCHAR, LONG VARGRAPHIC, BLOB, CLOB, DBCLOB, DATALINK та структурні типине застосовуються у реченні EXCEPT, але їх можна використовувати у реченні EXCEPT ALL.

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

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

SELECT empno FROM employee WHERE workdept LIKE "E%" EXCEPT SELECT empno FROM emp_act WHERE projno IN (TF1000", TF2000", -AD3110") UNION VALUES ("AA0001"), ("AB0002" ;

У наведеному вище прикладі з таблиці employee вилучаються ідентифікатори (ID) всіх службовців, що працюють у департаменті, назва якого починається з "Е", потім з таблиці облікових записів службовців (emp_act) виключаються ID тих, хто зайнятий у проектах IF1000, IF200" та AD31 І нарешті, додається три додаткові ID - АА0001, AB0002 і AC0003 за допомогою оператора роботи з наборами UNION.

MySQL

У MySQL оператор EXCEPT не підтримується. В якості альтернативи можна використовувати операції NOT IN або NOT EXISTS.

SQL надає два способи об'єднання таблиць:

вказуючи таблиці, що з'єднуються (у тому числі підзапити) у фразі FROM оператора SELECT . Спочатку виконується з'єднання таблиць, а вже потім до отриманої множини застосовуються зазначені фразою WHERE умови, що визначається фразою GROUP BY агрегування, впорядкування даних тощо;
визначаючи поєднання результуючих наборів, отриманих при обробці оператора SELECT . У цьому випадку два оператори SELECT з'єднуються фразою UNION, INTERSECT, EXCEPTабо CORRESPONDING.

UNION-об'єднання

Фраза UNIONоб'єднує результати двох запитів за такими правилами:

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

Для того, щоб явно вказати необхідний порядок сортування, слід використати фразу ORDER BY . При цьому можна використовувати ім'я стовпця, так і його номер (рис. 4.3).

Рис. 4.3.

Фраза UNION ALL виконує об'єднання двох підзапитів аналогічно до фрази UNION з наступними винятками:

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

При об'єднанні більше двох запитів зміни порядку виконання операції об'єднання можна використовувати дужки (рис. 4.4).

Рис. 4.4.

INTERSECT-об'єднання

Фраза INTERSECTдозволяє вибрати тільки ті рядки, які присутні в кожному результуючому наборі, що об'єднується. На рис. 4.5 наведено приклад об'єднання запитів як множин, що перетинаються.

Рис. 4.5.

EXCEPT-об'єднання

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

Фрази INTERSECTі EXCEPTповинні підтримуватися лише за повного рівня відповідності стандарту SQL-92. Так, деякі СУБД замість фрази

Оператор INTERSECT витягує ідентичні рядки з результуючих наборів одного або кількох запитів. У певному відношенні оператор INTERSECT дуже скидається на INNER JOIN.

INTERSECT належить до класу операторів роботи з наборами даних (set operator). До інших таких операторів відносяться EXCEPT та UNION. Усі оператори для наборів даних використовуються для одночасного маніпулювання результуючими наборами двох і більше запитів, звідси їх назва.

Синтаксис SQL2003

Технічних обмежень на кількість запитів в операторі INTERSECT немає. Загальний синтаксис є наступним.

INTERSECT

] INTERSECT

Ключові слова

Включаються рядки, що дублюються, з усіх результуючих наборів.

DISTINCT

Рядки, що дублюються, видаляються з усіх результуючих наборів перед порівнянням, проведеним оператором INTERSECT. Стовпці з порожніми (NULL) значеннями вважаються такими, що дублюються. Якщо не вказано ні ключове слово ALL, ні DISTINCT, то за умовчанням мається на увазі DISTINCT.

CORRESPONDING

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

Вказується, що повертатимуться лише названі стовпці, навіть якщо запити виявляють інші стовпці з відповідними іменами. Ця пропозиція повинна використовуватись разом із ключовим словом CORRESPONDING.

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

Є лише одне важливе правило, яке необхідно пам'ятати під час роботи з оператором INTERSECT.

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

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

Жодна з платформ не підтримує пропозицію CORRESPONDING.

Відповідно до стандарту ANSI оператор INTERSECT має вищий пріоритет у порівнянні з іншими операторами для роботи з наборами, хоча на різних платформах пріоритет таких операторів обробляється по-різному. Ви можете явно керувати пріоритетом операторів, використовуючи дужки. В інакшеСУБД може виконувати їх у порядку зліва направо або від першого до останнього.

На платформах, які не підтримують оператор INTERSECT, можна замінити його підзапитом FULL JOIN.

SELECT a.au_lname AS "lastname", a.au_fname AS "firstname" FROM authors AS a INTERSECT SELECT e.emp_lname AS "lastname", e.emp_fname AS "firstname" FROM employees AS e ORDER BY lastname, fir

Оскільки типи даних стовпців у різних запитах оператора INTERSECT можуть бути сумісними, різних платформах СУРБД можуть зустрічатися різні варіанти роботи зі стовпцями різної довжини. Наприклад, якщо стовпець aujname з першого запиту в попередньому прикладі значно довше, ніж стовпець empjname з другого запиту, різні платформи можуть застосовувати різні правила визначення довжини кінцевого результату. Але, взагалі кажучи, платформи вибиратимуть для результату довший (і менш обмежений) розмір.

Кожна СУРБД може застосовувати свої правила використання імені стовпця у разі, якщо імена у списках стовпців різняться. Зазвичай використовуються імена стовпчиків першого запиту.

DB2

Платформа DB2 підтримує ключові слова INTERSECT та INTERSECT ALL стандарту ANSI плюс додаткова пропозиція VALUES.

(інструкція._SELECT_7 | VALUES (вираж7 [, …])) INTERSECT

] (інструкція_SCJ_2 | VALUES (вираж2 [, …])) INTERSECT

Хоча інструкція INTERSECT DISTINCT не підтримується, функціональним еквівалентом є INTERSECT. Пропозиція CORRESPONDING не підтримується.

Крім того, типи даних LONG VARCHAR, LONG VARGRAPHIC, BLOB, CLOB, DBCLOB, DATALINK та структурні типи не застосовуються у реченні INTERSECT, але їх можна використовувати у реченні INTERSECT ALL.

Якщо в результуючому наборі даних є стовпець, що має одне й те саме ім'я у всіх інструкціях SELECT, то це ім'я використовується як остаточне ім'я для стовпця, що повертається інструкцією. Якщо ж у запитах для шпальти використовуються різні імена, то платформа DB2 згенерує для результуючого шпальти нове ім'я. Після цього стає непридатним для використання у пропозиціях ORDER BY та FOR UPDATE.

SELECT empno FROM employee WHERE workdept LIKE "E%" INTERSECT (SELECT empno FROM emp_act WHERE projno IN ("IF1000", "IF2000", "AD3110") UNION VALUES ("AA0001"), ("AB00) "))

У наведеному вище прикладі з таблиці employee вилучаються ідентифікатори (ID) всіх службовців, що працюють у департаменті, назва якого починається з "Е". Однак ідентифікатори витягуються лише в тому випадку, якщо вони також існують у таблиці облікових записів службовців з ім'ям emp_act та беруть участь у проектах IF1000, IF200" та AD3110.