Почему EXCEPT существует в T-SQL?

Я просто читал об EXCEPT и INTERSECT в библиотеке MSDN и наткнулся на этот пример использования INTERSECT:

USE AdventureWorks2008R2 GO 
SELECT ProductID 
FROM Production.Product
INTERSECT
SELECT ProductID 
FROM Production.WorkOrder ;
--Result: 238 Rows (products that have work orders)

Возможно, я старомодный, но я обычно использовал следующий код для достижения того же результата:

SELECT P.ProductID 
FROM Production.Product P 
INNER JOIN Production.WorkOrder W ON W.ProductID = P.ProductID

Я что-то упустил, или INTERSECT так же, как INNER JOIN? Есть ли преимущество в производительности для использования одного над другим?

Тот же вопрос для КРОМЕ. Как это:

USE AdventureWorks2008R2;
GO
SELECT ProductID 
FROM Production.Product
EXCEPT
SELECT ProductID 
FROM Production.WorkOrder ;
--Result: 266 Rows (products without work orders)

отличается от этого:

SELECT P.ProductID 
FROM Production.Product P 
LEFT JOIN Production.WorkOrder W ON W.ProductID = P.ProductID
WHERE W.ProductID IS NULL

Ответ 1

Я собираюсь сосредоточиться на EXCEPT только потому, что я больше знаком с ним. Кроме того, как отказ от ответственности, мои примеры будут в Sqlite, так как я нахожусь в ящике Linux. Тем не менее, Sqlite и SQL Server должны поддерживать функциональность.

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

Ваш пример упрощен. Я дам контрпример, используя версию Sqlite

Ответ 2

INTERSECT и EXCEPT являются полу-соединениями
JOIN равно equial-join

Итак, когда вы присоединяетесь к двум таблицам, которые соответствуют, скажем, 5 строкам и 3 строкам

JOIN дает 15 строк
INTERSECT дает 3 строки

EXCEPT похож на OUTER JOIN по той же причине

Пока мы говорим о полу-соединениях, в основном

INTERSECT дает те же результаты, что и EXISTS
EXCEPT дает те же результаты, что и НЕ СУЩЕСТВУЕТ

"В основном" происходит потому, что и INTERSECT, и EXCEPT

относиться к NULL по-разному: для полностью обработанного примера см. это Paul White aka SQL Kiwi
применить DISTINCT

Изменить, Быстрая демонстрация всего этого

DECLARE @t1 TABLE (t1col INT);
INSERT @t1 VALUES (1), (2), (2), (3), (3), (5), (5);

DECLARE @t2 TABLE (t2col INT);
INSERT @t2 VALUES (1), (2), (3), (4);

SELECT 'INNER JOIN', * FROM @t1 t1 JOIN @t2 t2 ON t1.t1col = t2.t2col -- same both ways

SELECT 't1 INTERSECT t2', * FROM @t1 INTERSECT SELECT 't1 INTERSECT t2', * FROM @t2;

SELECT 't2 INTERSECT t1', * FROM @t2 INTERSECT SELECT 't2 INTERSECT t1', * FROM @t1;

SELECT 't1 EXISTS t2', * FROM @t1 t1
WHERE EXISTS (SELECT * FROM @t2 t2 WHERE t1.t1col = t2.t2col);

SELECT 't2 EXISTS t1', * FROM @t2 t2
WHERE EXISTS (SELECT * FROM @t1 t1 WHERE t1.t1col = t2.t2col);

SELECT 't1 LEFT JOIN t2, IS NULL', * FROM @t1 t1 LEFT JOIN @t2 t2 ON t1.t1col = t2.t2col WHERE t2.t2col IS NULL
SELECT 't2 LEFT JOIN t1, IS NULL', * FROM @t2 t2 LEFT JOIN @t1 t1 ON t1.t1col = t2.t2col WHERE t1.t1col IS NULL

SELECT 't1 EXCEPT t2', * FROM @t1 EXCEPT SELECT 't1 EXCEPT t2', * FROM @t2;

SELECT 't2 EXCEPT t1', * FROM @t2 EXCEPT SELECT 't2 EXCEPT t1', * FROM @t1;

SELECT 't1 NOT EXISTS t2', * FROM @t1 t1
WHERE NOT EXISTS (SELECT * FROM @t2 t2 WHERE t1.t1col = t2.t2col);

SELECT 't2 NOT EXISTS t1', * FROM @t2 t2
WHERE NOT EXISTS (SELECT * FROM @t1 t1 WHERE t1.t1col = t2.t2col);

Обновление: февраль 2013 г. Добавлен дополнительный столбец для описания операции

Ответ 3

Примеры ваших "эквивалентных" запросов неверны - запрос с INTERSECT не всегда возвращает тот же результат, что и INNER JOIN, и тот же для EXCEPT и LEFT JOIN.

Посмотрите конкретный пример о INTERSECT:

DECLARE @t TABLE(t INT NOT NULL)
DECLARE @x TABLE(x INT NOT NULL)

INSERT @t
VALUES (1), (2), (3)

INSERT @x VALUES(1), (1), (1)

SELECT t FROM @t
INTERSECT SELECT x FROM @x

SELECT t FROM @t
INNER JOIN @x ON x = t

INTERSECT больше похож на (, но не тот же) как IN:

SELECT t FROM @t
WHERE t IN (select x FROM @x)

или как EXISTS

SELECT t FROM @t
WHERE EXISTS (select * FROM @x WHERE x = t)

Те же примеры, которые вы можете адаптировать к предложению EXCEPT.

Ответ 4

По-моему EXCEPT и INTERSECT используются для выполнения тех же действий, что и команда JOIN, но проще с таблицами, которые не имеют первичных ключей, например:

с INTERSECT:

SELECT FIRSTNAME,
       LASTNAME,
       ADDRESSLINE1,
       CITY,
       STATEPROVINCECODE,
       POSTALCODE
FROM   MANAGER 
EXCEPT
SELECT FIRSTNAME,
       LASTNAME,
       ADDRESSLINE1,
       CITY,
       STATEPROVINCECODE,
       POSTALCODE
FROM   CUSTOMER

И чтобы иметь те же результаты с JOIN, вы должны сделать:

SELECT   M.FIRSTNAME,
         M.LASTNAME,
         M.ADDRESSLINE1,
         M.CITY,
         M.STATEPROVINCECODE,
         M.POSTALCODE
FROM     MANAGER M
WHERE    NOT EXISTS (SELECT *
                     FROM   CUSTOMER C
                     WHERE  M.FIRSTNAME = C.FIRSTNAME
                            AND M.LASTNAME = C.LASTNAME
                            AND M.ADDRESSLINE1 = C.ADDRESSLINE1
                            AND M.CITY = C.CITY
                            AND M.POSTALCODE = C.POSTALCODE)
GROUP BY M.FIRSTNAME,M.LASTNAME,M.ADDRESSLINE1,M.CITY,
         M.STATEPROVINCECODE,M.POSTALCODE

Подробнее здесь.