Except sql: SQL оператор EXCEPT — Oracle PL/SQL •MySQL •MariaDB •SQL Server •SQLite

Вы, вероятно, не используете SQL INTERSECT или EXCEPT часто достаточно

Когда люди говорят о SQL JOIN, они часто используют диаграммы Венна, чтобы проиллюстрировать включение и исключение двух объединенных наборов:

Хотя эти диаграммы Венна, безусловно, полезны для понимания (и запоминания) синтаксиса SQL JOIN, они не совсем точны, поскольку SQL JOIN – это особый тип декартового произведения, CROSS JOIN.

Иллюстрация пользователя Википедии Quartl

В декартовом произведении между двумя наборами A и B результатом является умножение каждого множества, означающее, что каждый элемент a ∈ A объединяется с каждым элементом b ∈ B образуя набор кортежей (a, b) .

Обычные соединения SQL делают именно это. Когда вы присоединитесь BOOK к AUTHOR, вы, вероятно, получите комбинацию каждого author ∈ AUTHOR с каждой book ∈ BOOK , так что для каждой комбинации (author, book) автор фактически написал книгу.

Истинный смысл диаграмм Венна

Истинный смысл диаграмм Венна гораздо лучше описывается операциями

• UNION
• INTERSECT
• EXCEPT (или MINUS в Oracle)

В следующих разделах мы увидим, что эти операции точно соответствуют семантике операций, которая может быть проиллюстрирована диаграммами Венна, даже если вы сможете «злоупотреблять» операциями JOIN для достижения того же результата.

UNION

Операция UNION является наиболее известной среди этих операций с множествами. Это часто также называют «объединением» двух наборов кортежей, где результатом является объединение набора B с набором A

В следующем примере мы увидим, что нас могут заинтересовать разные люди из нашей базы данных, учитывая их имена и фамилии, независимо от того, являются ли они customer или staff :

Исходные диаграммы Венна использовали FULL OUTER JOIN для моделирования «одной и той же» концепции, хотя эти две вещи не являются строго одинаковыми. Рассмотрим следующий запрос, который мы запустим для базы данных Sakila :

Результат выглядит так:

Теперь запустите следующий «эквивалентный» запрос:

Результат снова даст:

Это работает только потому, что мы используем предложение USING , которое изначально поддерживается не каждой базой данных. Если бы мы сделали JOIN с более часто используемым предложением ON, нам пришлось бы написать более утомительное:

В этом случае большинство людей, вероятно, по умолчанию уже используют UNION , поскольку это гораздо более известная операция, чем FULL OUTER JOIN .

Все поддерживаемые в настоящее время СУБД jOOQ поддерживают UNION и UNION ALL (последняя не удаляет дубликаты).

Далее мы увидим, что эквивалентные сравнения могут быть сделаны с другими операциями над множествами:

ПЕРЕСЕЧЕНИЕ

Операция INTERSECT действительно полезна, когда вы хотите сохранить только те кортежи, которые присутствуют в обоих наборах, которые объединены с использованием INTERSECT :

Как видите, мы можем пожелать сохранить только тех клиентов, которые также являются действующими лицами. Давайте запустим этот запрос:

Один из наших клиентов также актер. Тот же самый запрос мог быть написан с INNER JOIN как таковой:

… или с синтаксисом ON

На этот раз COALESCE не требуется, поскольку INNER JOIN сохраняет только те кортежи из декартового произведения, которые присутствуют на «обеих сторонах» JOIN , поэтому мы можем выбрать любую из таблиц для префикса наших столбцов.

Вы можете даже решить использовать полусоединение вместо этого, что даст те же результаты:

или, используя более подробный, но эквивалентный предикат EXISTS :

Все вышеперечисленное, опять же, дает:

КРОМЕ

Операция EXCEPT полезна, когда вы хотите сохранить только те кортежи, которые присутствуют в одном наборе, но не в другом:

Выполнение этого запроса:

… даст:

Согласно исходным диаграммам Венна, это можно настроить с помощью LEFT JOIN и предиката IS NULL :

или с предложением ON :

Это совершенно нечитаемо и не говорит о том, что мы удаляем кортежи из набора CUSTOMER , учитывая их присутствие в другом наборе STAFF .

Эквивалентная версия, использующая анти-объединение, может быть более читабельной ( хотя NULLs внимание на NULLs в предикатах NOT IN ! ):

… или, используя NOT EXISTS :

1 2 3 4 5 6 7 8 9

SELECTc. first_name, c.last_name FROMcustomer c WHERENOTEXISTS (   SELECT1   FROMstaff s   WHERE(c.first_name, c.last_name)       = (s.first_name, s.last_name) ) ORDERBY1, 2

Вывод

UNION , INTERSECT и EXCEPT – это очень простые, но очень полезные операции, которые могут время от времени приносить большую пользу в ваших ежедневных задачах SQL. Хотя операции JOIN гораздо более универсальны, они также более сложны для простых задач, которые могут быть решены с помощью UNION , INTERSECT и EXCEPT

Ссылка:

Возможно, вы не используете SQL INTERSECT или EXCEPT, часто достаточно от нашего партнера по JCG Лукаса Эдера в блоге JAVA, SQL и AND JOOQ .

SQL EXCEPT — Разделы масштабирования

Обзор

EXCEPT — это оператор набора в SQL, который возвращает отдельные строки, присутствующие в наборе результатов первого запроса, но не в наборе результатов второго запроса. Он также известен как оператор разности множеств. EXCEPT используется в сочетании с оператором SELECT для сравнения наборов результатов двух или более запросов.

В этой статье мы обсудим синтаксис и использование оператора EXCEPT в SQL, а также приведем примеры, демонстрирующие его функциональность.

Что такое КРОМЕ SQL?

Предложение EXCEPT в SQL помогает пользователям комбинировать две инструкции SELECT и возвращает отдельные строки из первой инструкции SELECT, недоступные во второй инструкции SELECT.

• Его правила аналогичны оператору UNION, и его можно сравнить с оператором вычитания в реляционной алгебре.
• Все версии SQL Server поддерживают предложение EXCEPT. MySQL не поддерживает оператор EXCEPT.
• Условия для пункта EXCEPT:
  • В обоих операторах SELECT должно быть одинаковое количество выражений.
  • Типы данных соответствующих столбцов должны быть одинаковыми или совместимыми.

Запрос EXCEPT вернет записи в заштрихованной области. Это записи, которые присутствуют в наборе данных1 и отсутствуют в наборе данных2.

Синтаксис

Команда SQL EXCEPT работает со следующим синтаксисом:

столбец1, столбец2 — извлекаемые столбцы.
table_name — таблицы, из которых будут извлекаться записи.
ГДЕ — [необязательно] Эти условия должны быть выполнены для выбора записей.

Примеры

Пример 1

Предположим, что у нас есть два отношения, Customers и New_Stores. Мы хотим вернуть всех тех клиентов, которые не являются частью new_stores.

Таблица клиентов

Таблица New_Stores

Код

9003 9 Выходные данные

Пример 2

Предположим, что у нас есть два отношения, Books1 и Books2. Мы хотим удалить все те записи из Книги1, которые присутствуют в Книгах2.

Книги1 Стол

Книги2 Стол

Код

Вывод

EXCEPT с оператором BETWEEN

В SQL оператор EXCEPT может использоваться для возврата отдельных строк. из левой таблицы, которых нет в правой таблице. Мы также можем использовать оператор BETWEEN для указания диапазона значений в запросе. Когда мы объединяем оператор EXCEPT с оператором BETWEEN, он возвращает строки, которые существуют в левой таблице, но не в правой таблице, и удовлетворяют указанному диапазону значений.

Вот пример, который показывает, как использовать EXCEPT с оператором BETWEEN:

В приведенном выше примере имя_столбца (имена) относится к столбцам, которые мы хотим выбрать из таблиц table1 и table2. значение1 и значение2 представляют нижний и верхний пределы диапазона значений для указанного столбца.

Этот запрос вернет все отдельные строки из таблицы 1, которые не существуют в таблице 2 и удовлетворяют указанному диапазону значений.

ЗА ИСКЛЮЧЕНИЕМ оператора IN

Оператор EXCEPT в SQL можно также использовать с оператором IN для получения разницы между двумя наборами значений. Оператор IN используется для сравнения значения с набором значений.

Вот пример, иллюстрирующий использование оператора EXCEPT с оператором IN:

Этот запрос выберет все строки из таблицы 1, которых нет в таблице 2 и значения которых не входят в заданный набор значений.

Обратите внимание, что оператор EXCEPT возвращает только уникальные значения. Если вы хотите включить дубликаты, вы можете использовать оператор UNION ALL.

EXCEPT с оператором LIKE

Оператор EXCEPT также можно использовать в сочетании с оператором LIKE для исключения определенных шаблонов из результатов запроса. Это может быть полезно, если вы хотите выбрать все строки, которые не соответствуют определенному шаблону.

Например, у вас есть таблица имен клиентов, и вы хотите выбрать всех клиентов, имена которых не начинаются с буквы «А». Вы можете использовать следующий запрос:

Это вернет все имена клиентов, кроме тех, которые начинаются с буквы «А». В приведенном выше примере оператор LIKE используется в подзапросе для выбора всех имен клиентов, начинающихся с буквы «А». Затем оператор EXCEPT используется для удаления этих имен из основного результата запроса.

EXCEPT с предложением ORDER BY

При использовании EXCEPT вы также можете отсортировать результирующий набор с помощью предложения ORDER BY. Предложение ORDER BY сортирует результирующий набор в порядке возрастания или убывания на основе одного или нескольких столбцов. Синтаксис использования EXCEPT с предложением ORDER BY следующий:

Здесь предложение ORDER BY используется для сортировки результирующего набора в порядке возрастания или убывания. ASC используется для возрастания, а DESC — для убывания.

Оператор EXCEPT можно использовать для поиска записей в одном запросе, которых нет в другом запросе. При использовании EXCEPT в одной таблице мы можем использовать его для сравнения результатов двух разных запросов к одной и той же таблице.

Например, предположим, что у нас есть таблица с именем сотрудников со столбцами идентификатора, имени и зарплаты. Мы можем использовать оператор EXCEPT, чтобы найти сотрудников, чьи зарплаты не находятся в диапазоне от 30000 до 40000. Ниже приведен пример запроса:

Этот запрос выбирает все столбцы из таблицы сотрудников, где зарплаты не находятся в указанном диапазоне. . Используя EXCEPT в одной таблице, мы можем быстро сравнить результаты двух разных запросов к одной и той же таблице.

Чем пункт EXCEPT отличается от пункта NOT IN?

Функциональность обоих предложений одинакова, поскольку они определяют записи, которые не должны включаться в окончательный набор результатов запроса. Однако между ними есть пункт различия.

• Предложение EXCEPT используется для автоматического удаления всех дубликатов в конечном результате, тогда как NOT IN сохраняет повторяющиеся записи.
• Предложение EXCEPT может выполнять сравнение в одном или нескольких столбцах. В то время как предложение NOT IN может выполнять сравнение только в одном столбце.

Узнать больше

• Дополнительные понятия SQL
• Команды SQL: DDL, DML, DCL, TCL, DQL
• Как избежать ошибок SQL?

Заключение

• Предложение EXCEPT в SQL возвращает отдельные кортежи, которые возвращаются первой операцией SELECT и не возвращаются второй операцией SELECT.
• EXCEPT в SQL широко используется для фильтрации записей из более чем одной таблицы. Он ведет себя так же, как оператор минус в математике для операций над множествами.

Оператор EXCEPT в SQL Server с примерами

Вернуться к: Учебное пособие по SQL Server для начинающих и профессионалов

В этой статье я собираюсь обсудить оператор EXCEPT в SQL Server с примерами. Оператор EXCEPT относится к категории операторов SET. Пожалуйста, прочитайте нашу предыдущую статью, в которой мы обсудили основы операторов SET в SQL Server , а также подробно обсудили операторы UNION и UNION ALL SET .

Что такое оператор EXCEPT в SQL Server?

Оператор EXCEPT в SQL Server используется для возврата уникальных строк из левого запроса, которых нет в результатах правого запроса. Если на данный момент это неясно, не волнуйтесь, мы постараемся понять это на нескольких примерах.

Понимание оператора EXCEPT с примерами.

Давайте разберемся с оператором EXCEPT на примере. Мы собираемся использовать следующие «Таблицы EmployeeIndia ” и “ EmployeeUK ” для понимания этого оператора.

Используйте приведенный ниже сценарий SQL для создания базы данных EmployeeDB, таблиц EmployeeIndia и EmployeeUK и заполните эти две таблицы необходимыми тестовыми данными.

 СОЗДАНИЕ БАЗЫ ДАННЫХ EmployeeDB
ИДТИ
ИСПОЛЬЗОВАТЬ базу данных сотрудников
ИДТИ
СОЗДАТЬ ТАБЛИЦУ
(
  ID INT ПЕРВИЧНЫЙ КЛЮЧ,
  Имя VARCHAR(50),
  Пол VARCHAR(10),
  Кафедра ВАРЧАР(50)
)
ИДТИ
ВСТАВЬТЕ В ЗНАЧЕНИЯ EmployeeIndia (1, «Праная», «Мужской», «ЭТО»)
INSERT INTO EmployeeIndia VALUES(2, 'Приянка', 'Женщина', 'ЭТО')
ВСТАВЬТЕ В ЗНАЧЕНИЯ EmployeeIndia (3, 'Красавица', 'Женщина', 'HR')
INSERT INTO EmployeeIndia VALUES(4, 'Subrat', 'Мужчина', 'HR')
INSERT INTO EmployeeIndia VALUES(5, 'Анураг', 'Мужчина', 'ИТ')
ИДТИ
СОЗДАТЬ ТАБЛИЦУ
(
  ID INT ПЕРВИЧНЫЙ КЛЮЧ,
  Имя VARCHAR(50),
  Пол VARCHAR(10),
  Кафедра ВАРЧАР(50)
)
ИДТИ
INSERT INTO EmployeeUK VALUES(1, 'Джеймс', 'Мужчина', 'ИТ')
INSERT INTO EmployeeUK VALUES(2, 'Приянка', 'Женщина', 'ЭТО')
INSERT INTO EmployeeUK VALUES(3, 'Сара', 'Женщина', 'HR')
INSERT INTO EmployeeUK VALUES(4, 'Subrat', 'Мужчина', 'HR')
INSERT INTO EmployeeUK VALUES(5, 'Pam', 'Female', 'HR')
ВПЕРЕД 

Пример:

Следующий запрос SQL вернет уникальные строки из левого запроса (оператор select перед оператором EXCEPT), которых нет в правом запросе (оператор select после оператора EXCEPT).

ВЫБЕРИТЕ ID, имя, пол, отдел ОТ EmployeeIndia
, КРОМЕ
ВЫБЕРИТЕ ID, имя, пол, отдел ОТ EmployeeUK

Результаты: 902 01

Чтобы получить все строки из таблицы EmployeeUK которые не существуют в таблице EmployeeIndia, поменяйте местами два запроса, как показано ниже.

ВЫБЕРИТЕ ID, Имя, Пол, Отдел ИЗ EmployeeUK
ЗА ИСКЛЮЧЕНИЕМ
ВЫБЕРИТЕ ID, Имя, Пол, Отдел ИЗ EmployeeIndia

Результат: 902 01

ЗА ИСКЛЮЧЕНИЕМ Оператор в SQL Server на одном таблица:

Вы также можете использовать оператор EXCEPT для одной таблицы. Давайте использовать следующую таблицу сотрудников для этого примера.

Используйте приведенный ниже сценарий SQL, чтобы создать и заполнить таблицу «Сотрудники» необходимыми тестовыми данными.

 -- SQL-скрипт для создания таблицы "Сотрудники"
Создать таблицу Сотрудники
(
    Id первичный ключ идентификации,
    Имя nvarchar(100),
    Пол nvarchar(10),
    Заработная плата
)
Идти
Вставить в значения «Сотрудники» («Отметка», «Мужчина», 52000)
Вставьте в значения «Сотрудники» («Мария», «Женщина», 55000)
Вставьте в значения «Сотрудники» («Стив», «Мужчина», 45000)
Вставить в значения «Сотрудники» («Джон», «Мужчина», 40000)
Вставьте в значения «Сотрудники» («Сара», «Женщина», 48000)
Вставьте в значения «Сотрудники» («Пэм», «Женщина», 60000)
Вставьте в значения сотрудников («Том», «Мужчина», 58000)
Вставьте в значения «Сотрудники» («Джордж», «Мужчина», 65000)
Вставьте в значения «Сотрудники» («Тина», «Женщина», 67000)
Вставьте в значения «Сотрудники» («Бен», «Мужчина», 80000)
Идти
 

Предложение Order By следует использовать только один раз после последнего оператора select

 Select Id, Name, Gender, Salary
От сотрудников
Где Зарплата >= 50000
Кроме
Выберите идентификатор, имя, пол, зарплату
От сотрудников
Где Зарплата >= 60000
порядок По имени 

Результат:

Разница между оператором EXCEPT и NOT IN SQL Server

Теперь у вас может возникнуть один вопрос: в чем разница между оператором EXCEPT и NOT IN в SQL Server . Давайте поймем разницу между операторами EXCEPT и NOT IN в SQL Server на одном примере. Мы собираемся использовать следующие две таблицы (TableA и TableB).

Используйте приведенный ниже сценарий SQL для создания необходимых таблиц с необходимыми тестовыми данными.

 СОЗДАТЬ ТАБЛИЦУ TableA
(
  ID ИНТ,
  Имя VARCHAR(50),
  Пол VARCHAR(10),
  Кафедра ВАРЧАР(50)
)
ИДТИ
INSERT INTO TableA VALUES(1, 'Праная', 'Мужской', 'ЭТО')
INSERT INTO TableA VALUES(2, 'Приянка', 'Женщина', 'ОНО')
INSERT INTO TableA VALUES(3, 'Preety', 'Female', 'HR')
ИДТИ
СОЗДАТЬ ТАБЛИЦУ TableB
(
  ID ИНТ,
  Имя VARCHAR(50),
  Пол VARCHAR(10),
  Кафедра ВАРЧАР(50)
)
ИДТИ
INSERT INTO TableB VALUES(2, 'Приянка', 'Женщина', 'ОНО')
INSERT INTO TableB VALUES(3, 'Preety', 'Female', 'HR')
ВПЕРЕД 

Пример: использование оператора EXCEPT

Следующий запрос возвращает строки из левого запроса, которых нет в результатах правого запроса.

ВЫБЕРИТЕ ID, Имя, Пол, Отдел ИЗ Таблицы A
ЗА ИСКЛЮЧЕНИЕМ
ВЫБЕРИТЕ ID, Имя, Пол, Отдел ИЗ Таблицы B

Результат: 902 01

Пример: использование оператора NOT IN

Такого же результата можно добиться с помощью оператора NOT IN, используя следующий запрос.

ВЫБЕРИТЕ ID, Имя, Пол, Отдел ИЗ Таблицы A Где ID НЕ В (Выберите ID из Таблицы B)

Результат:

Итак, что же такое разница между операторами EXCEPT и NOT IN в SQL-сервер?

Оператор EXCEPT фильтрует повторяющиеся строки и возвращает только строки DISTINCT из левого запроса, которых нет в результатах правого запроса, тогда как NOT IN не фильтрует повторяющиеся строки. Чтобы понять это, вставьте следующую строку в таблицу A 9.0005

INSERT INTO TableA VALUES(1, «Pranaya», «Мужской», «IT»)

Теперь выполните следующий запрос EXCEPT. Обратите внимание, что в результирующем наборе мы получаем только строки DISTINCT.

ВЫБЕРИТЕ ID, Имя, Пол, Отдел ИЗ Таблицы A
ЗА ИСКЛЮЧЕНИЕМ
ВЫБЕРИТЕ ID, Имя, Пол, Отдел ИЗ Таблицы B

Результат: 902 01

Теперь выполните следующий запрос. Обратите внимание, что повторяющиеся строки не фильтруются в результирующем наборе.

ВЫБЕРИТЕ ID, Имя, Пол, Отдел ИЗ Таблицы A Где ID НЕ В (Выберите ID из Таблицы B)

Результат:

Оператор EXCEPT ожидает тот же номер столбцов в обоих запросах, тогда как NOT IN сравнивает один столбец из внешнего запроса с одним столбцом из подзапроса. В следующем примере количество столбцов отличается.

ВЫБЕРИТЕ ID, имя, пол, отдел из таблицы A
ЗА ИСКЛЮЧЕНИЕМ
ВЫБЕРИТЕ ID, имя, пол из таблицы B

Приведенный выше запрос выдаст следующую ошибку.

Сообщение 205, уровень 16, состояние 1, строка 1

Все запросы, объединенные с использованием оператора UNION, INTERSECT или EXCEPT, должны иметь одинаковое количество выражений в своих целевых списках.

Оператор NOT IN сравнивает один столбец из внешнего запроса с одним столбцом из подзапроса. В следующем примере подзапрос возвращает несколько столбцов

ВЫБЕРИТЕ ID, Имя, Пол, Отдел ИЗ Таблицы A Где ID НЕ В (Выберите ID, Имя из Таблицы B)

Приведенный выше запрос выдаст следующую ошибку.

Сообщение 116, Уровень 16, Состояние 1, Строка 2

В списке выбора можно указать только одно выражение, если подзапрос не вводится с EXISTS.

В следующей статье я собираюсь обсудить INTERSECT Operator в SQL Server с некоторыми примерами. Здесь, в этой статье, я пытаюсь объяснить оператор EXCEPT в SQL Server на примерах.