tsql - Why does EXCEPT exist in T-SQL?

Question

I was just reading about EXCEPT and INTERSECT in the MSDN Library and came across this example of how to use INTERSECT:

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

Maybe I'm old-fashioned, but I typically would use the following code to achieve the same result:

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

Am I missing something, or is INTERSECT the same as INNER JOIN? Is there a performance benefit to using one over the other?

Same question for EXCEPT. How is this:

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

different from this:

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

?

Answer 1

I'm going to focus on EXCEPT just because I'm more familiar with it. Also, as a disclaimer, my examples will be in Sqlite, since I'm on a Linux box. However, both Sqlite and SQL Server should support the functionality.

Both INTERSECT and EXCEPT are set operators, stemming from the underlying ideas in relational algebra. They operate on distinct values, being set operators.

Your example is simplistic. I'll give a counterexample, using a Sqlite version of the Northwind sample database.

Let's say that you want to get the CustomerIDs of all customers who made an order with EmployeeID of 5, but NOT those who also made an order with EmployeeID of 6. This is simple and natural with an EXCEPT.

SELECT CustomerID FROM orders
WHERE EmployeeID = 5
EXCEPT
SELECT CustomerID FROM orders
WHERE EmployeeID = 6

This returns 14 rows on my version of Northwind.

Suppose you decide to rewrite this using JOINs. Maybe something like this?

SELECT o1.CustomerID
FROM orders o1 INNER JOIN orders o2 ON o1.CustomerID = o2.CustomerID
WHERE o1.EmployeeID = 5 AND o2.EmployeeID != 6

Whoops, 525 rows. Maybe add a DISTINCT?

SELECT DISTINCT o1.CustomerID
FROM orders o1 INNER JOIN orders o2 ON o1.CustomerID = o2.CustomerID
WHERE o1.EmployeeID = 5 AND o2.EmployeeID != 6

Now it's 28 rows, still much more than what we were getting with EXCEPT. The reason is that this isn't removing CustomerIDs that have made an order with 6. Rather, it returns all CustomerIDs that have an order with 5 and some EmployeeID other than 6, whether or not they also have an order with EmployeeID 6.

In short, EXCEPT and INTERSECT are set operators that compare two queries, returning unique tuples, and certainly have their use.