为什么 T-SQL 中存在 EXCEPT? [英] Why does EXCEPT exist in T-SQL?

问题描述

我刚刚在 MSDN 库中阅读有关 EXCEPT 和 INTERSECT 的内容，并发现了这个关于如何使用 INTERSECT 的示例:

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

我是否遗漏了什么，还是 INTERSECT 与 INNER JOIN 相同?使用一个比另一个有性能优势吗?

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

同样的问题，EXCEPT.这是怎么回事:

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)

与此不同:

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

?

推荐答案

我将专注于 EXCEPT 只是因为我更熟悉它.另外，作为免责声明，我的示例将使用 Sqlite，因为我使用的是 Linux 机器.但是，Sqlite 和 SQL Server 都应该支持该功能.

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.

INTERSECT 和 EXCEPT 都是集合运算符，源自 关系代数.它们对不同的值进行操作，即集合运算符.

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

你的例子很简单.我将举一个反例，使用 Northwind 示例的 Sqlite 版本数据库.

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

假设您想获取所有使用 EmployeeID 为 5 的订单的客户的 CustomerID，而不是那些也使用 EmployeeID 为 6 的订单的客户的 CustomerID.使用 EXCEPT.

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

这会在我的 Northwind 版本上返回 14 行.

This returns 14 rows on my version of Northwind.

假设您决定使用 JOIN 来重写它.也许像这样?

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

糟糕，525 行.也许添加一个 DISTINCT?

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

现在是 28 行，仍然比我们使用 EXCEPT 得到的要多得多.原因是这不会删除使用 6 下订单的 CustomerID.而是返回all 订单为 5 的 CustomerID 和 some EmployeeID 不是 6，无论他们是否也有 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.

简而言之，EXCEPT 和 INTERSECT 是比较两个查询的集合运算符，返回唯一的元组，当然有它们的用途.

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

这篇关于为什么 T-SQL 中存在 EXCEPT?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！