Intersect SQL приклади. Оператор наборів даних INTERSECT. Приклад з одним виразом

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. Так, деякі СУБД замість фрази

Існує лише одне важливе правило використання інструкції 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.

Оператор 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.

В цьому навчальному посібникуви дізнаєтесь, як використовувати оператор EXCEPT в SQL Server (Transact-SQL) із синтаксисом та прикладами.

Опис

Оператор EXCEPT SQL Server(Transact-SQL) використовується для повернення всіх рядків у першому операторі SELECT, які не повертаються другим оператором SELECT. Кожна інструкція SELECT визначатиме набір даних. Оператор EXCEPT витягуватиме всі записи з першого набору даних, а потім видалятиме з результатів усі записи з другого набору даних.

Запит Except

Пояснення: Запит EXCEPT поверне записи в сірому затінку. Це записи, які існують у SELECT 1, а не в SELECT 2.
Кожен оператор SELECT у запиті EXCEPT повинен мати однакову кількість полів у наборах результатів зі схожими типами даних.

Синтаксис

Синтаксис оператора EXCEPT в SQL Server (Transact-SQL):

Параметри чи аргументи

expressions — стовпці або обчислення, які потрібно порівняти між двома операторами SELECT. Вони не повинні бути однаковими полями в кожному операторі SELECT, але відповідні стовпці повинні бути з схожими типами даних.
tables — таблиці, з яких потрібно отримати записи. Має бути хоча б одна таблиця, перелічена у реченні FROM.
WHERE conditions – необов'язковий. Умови, які потрібно виконати для вибраних записів.

Примітка

В обох операторах SELECT має бути однакова кількість виразів.
Відповідні стовпці в кожному операторі SELECT повинні мати схожі типи даних.
Оператор EXCEPT повертає всі записи з першого оператора SELECT, що не входить до другого оператора SELECT.
Оператор EXCEPT в SQL Server еквівалентний оператору MINUS в Oracle.

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

Давайте розглянемо приклад оператора EXCEPT в SQL Server (Transact-SQL), який повертає одне поле з тим самим типом даних.
Наприклад:

Transact-SQL

SELECT product_id FROM products EXCEPT SELECT product_id FROM inventory;

SELECT product_id

FROM products

SELECT product_id

FROM inventory;

Цей приклад оператора EXCEPT повертає всі значення product_id , що у таблиці products , а чи не в таблиці inventory . Це означає, що якщо значення product_id існує в таблиці products і існує в таблиці inventory , значення product_id не відображатиметься в результатах запиту EXCEPT.

Приклад з декількома виразами

Потім давайте розглянемо приклад запиту EXCEPT SQL Server (Transact-SQL), який повертає більше одного стовпця.
Наприклад:

Transact-SQL

У цьому прикладі запит EXCEPT повертає записи в таблиці contacts з ім'ям contact_id, last_name та first_name, яке не відповідає значенням employee_id, last_name та first_name у таблиці employees.