SQL оператор EXCEPT: синтаксис и примеры использования

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

Общие сведения об операторе EXCEPT

EXCEPT - это оператор в SQL, который возвращает уникальные строки из результатов первого запроса, отсутствующие во втором запросе. Он работает как операция вычитания в математике:

• Запрос1 EXCEPT Запрос2 = строки только из Запрос1, которых нет в Запрос2

Основные отличия EXCEPT от других операторов фильтрации:

• INTERSECT - возвращает общие строки в обоих запросах
• NOT IN - фильтрует строки в одном запросе по условию
• MINUS в Oracle - аналог EXCEPT из SQL Server

Для работы оператора EXCEPT есть несколько основных требований:

1. Одинаковое количество столбцов в запросах
2. Совместимые типы данных столбцов
3. Сортировка результатов по столбцам

Поддержка EXCEPT в разных СУБД:

Синтаксис оператора EXCEPT

Синтаксис EXCEPT в SQL стандартный и выглядит так:

 SELECT столбцы FROM таблица1 EXCEPT SELECT столбцы FROM таблица2; 

Основные компоненты запроса:

• столбцы - список столбцов для вывода, их должно быть одинаковое количество в обоих запросах;
• таблица1 и таблица2 - таблицы источники данных;
• условия - дополнительные условия фильтрации по столбцам.

Можно использовать вложенные запросы вместо простого указания таблиц:

 SELECT столбцы FROM (Запрос1) EXCEPT SELECT столбцы FROM (Запрос2); 

Также работают дополнительные предложения, например:

 SELECT столбцы FROM таблицы EXCEPT SELECT столбцы FROM таблицы ORDER BY столбец ASC; 

Примеры базового применения

Давайте рассмотрим примеры использования оператора EXCEPT в SQL на практике. У нас есть таблица с данными о продуктах:

CREATE TABLE products ( id INTEGER PRIMARY KEY, name VARCHAR(50), category VARCHAR(50), price INTEGER ); INSERT INTO products (id, name, category, price) VALUES (1, 'Product 1', 'Category 1', 100), (2, 'Product 2', 'Category 1', 200), (3, 'Product 3', 'Category 2', 150); 

Найдем продукты категории 1, которых нет в списке продуктов подороже 150:

 SELECT name, category, price FROM products WHERE category = 'Category 1' EXCEPT SELECT name, category, price FROM products WHERE price > 150; 

Результат:

 name category price Product 1 Category 1 100 

Здесь продукт 2 не попал в результат, т.к. его цена больше 150.

Еще пример, находим продукты которые есть в списке 1, но отсутствуют в списке 2:

 SELECT name FROM (SELECT * FROM products WHERE category = 'Category 1') list1 EXCEPT SELECT name FROM (SELECT * FROM products WHERE price > 150) list2; 

Это более гибкий подход через вложенные запросы. Также можно фильтровать данные по нескольким столбцам:

SELECT id, name FROM products EXCEPT SELECT id, name FROM products WHERE price < 120; 

Здесь мы получим продукты с ценой от 120 и выше.

Рассмотрим более сложные варианты использования оператора EXCEPT в SQL запросах.

Объединение нескольких операторов EXCEPT

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

 SELECT id, name FROM products EXCEPT SELECT id, name FROM product_list1 EXCEPT SELECT id, name FROM product_list2; 

Здесь из первого списка продуктов исключаются общие с product_list1, а затем общие с product_list2. Таким образом получаем уникальные значения.

Использование предложения DISTINCT

Можно добавить DISTINCT в оператор EXCEPT, чтобы исключить дубликаты:

 SELECT DISTINCT name FROM products EXCEPT SELECT name FROM product_list; 

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

Фильтрация данных во вложенных запросах

Рассмотрим сложный пример с вложенными запросами и фильтрацией по цене:

 SELECT name, price FROM (SELECT * FROM products) AS t1 EXCEPT SELECT name, price FROM (SELECT * FROM products WHERE price > 150) AS t2; 

Здесь во вложенном запросе мы фильтруем данные по цене, а затем применяем EXCEPT для окончательной фильтрации.

Оптимизация запросов с EXCEPT

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

• Помещать более "тяжелый" запрос слева от EXCEPT
• Использовать индексы для ускорения запросов
• Применять партиционирование больших таблиц

Например, есть большая факт таблица (1млн строк) и небольшая таблица исключений (1к строк):

 SELECT col1, col2 FROM big_table EXCEPT SELECT col1, col2 FROM small_exceptions_table; 

Здесь запрос к большой таблице выполнится быстрее.

Пример с ms SQL Server

Рассмотрим конкретный пример использования EXCEPT в ms SQL Server для фильтрации данных из двух таблиц:

 SELECT product_name, category FROM production.products EXCEPT SELECT product_name, category FROM staging.products; 

Здесь из таблицы production.products мы получим уникальные продукты, которых нет в другой таблице staging.products. Это позволяет эффективно выявить различия в наборах данных из разных источников.

Объединение с другими операторами

Оператор EXCEPT можно комбинировать с UNION в SQL:

 SELECT col1 FROM table1 UNION SELECT col1 FROM table2 EXCEPT SELECT col1 FROM table3; 

Сначала произойдет объединение данных из table1 и table2, а затем из результата будут удалены строки из table3.

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

Дополнительные условия фильтрации с EXCEPT

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

 SELECT id, name, price FROM products WHERE price > 100 EXCEPT SELECT id, name, price FROM product_list WHERE category = 'electronics'; 

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

Использование параметров и переменных

Также вместо конкретных значений можно подставлять параметры или переменные:

 DECLARE @price INT = 100 DECLARE @category VARCHAR(100) = 'electronics' SELECT id, name, price FROM products WHERE price > @price EXCEPT SELECT id, name, price FROM product_list WHERE category = @category 

Это дает дополнительную гибкость и позволяет повторно использовать запрос с разными значениями.

Фильтрация NULL значений

Зачастую нужно исключать или наоборот выбирать только строки со значениями NULL. Это легко сделать через EXCEPT:

 SELECT col1 FROM table1 EXCEPT SELECT col1 FROM table2 WHERE col2 IS NULL; 

Здесь из таблицы 1 будут возвращены строки, где значение col2 не NULL в таблице 2.

Задачи и применение EXCEPT

Рассмотрим типовые задачи, которые можно решить с помощью оператора EXCEPT:

• Сравнение данных из разных таблиц/баз
• Поиск дубликатов и недостающих значений
• Фильтрация отчетов и выгрузок
• Анализ изменений (к примеру, обновленные значения)

Пример сравнения таблиц из разных баз данных:

 SELECT customer_id, order_date FROM db1.orders EXCEPT SELECT customer_id, order_date FROM db2.orders; 

Это позволит выявить различающиеся или отсутствующие заказы в разных СУБД.