主页 / dba / 问题 / 83684
Accepted
Heisenberg
Asked: 2014-11-27 02:19:52 +0800 CST

EXCEPT 运算符与 NOT IN

  • 772

运算符是在 SQL Server 2005 中引入的，但和 和有什么EXCEPT区别？NOT INEXCEPT

它做同样的事情吗？我想用一个例子做一个简单的解释。

sql-server sql-server-2008-r2

3 个回答

  1. Best Answer

    EXCEPT和之间有两个主要区别NOT IN。

    除了

    EXCEPT过滤DISTINCT左侧表格中未出现在右侧表格中的值。NOT EXISTS它与使用DISTINCT子句基本相同。

    它还期望两个表（或表中列的子集）在查询的左侧和右侧具有相同数量的列

    例如，您不能这样做：

    SELECT ID, Name FROM TableA
EXCEPT
SELECT ID FROM TableB

    这将导致错误：

    使用 UNION、INTERSECT 或 EXCEPT 运算符组合的所有查询必须在其目标列表中具有相同数量的表达式。

    不在

    NOT IN不过滤DISTINCT值并返回左侧表中未出现在右侧表中的所有值。

    NOT IN要求您将一个表中的单个列与另一个表或子查询中的单个列进行比较。

    例如，如果您的子查询要返回多个列：

    SELECT * FROM TableA AS nc
WHERE ID NOT IN (SELECT ID, Name FROM TableB AS ec)

    您会收到以下错误：

    当不使用 EXISTS 引入子查询时，选择列表中只能指定一个表达式。

    但是，如果右侧表NULL在被 过滤的值中包含 a，NOT IN则返回空结果集，可能会产生意外结果。

    例子

    CREATE TABLE #NewCustomers (ID INT);
CREATE TABLE #ExistingCustomers (ID INT);

INSERT INTO #NewCustomers
        ( ID )
VALUES
     (8), (9), (10), (1), (3), (8);

INSERT INTO #ExistingCustomers
        ( ID )
VALUES
        ( 1) , (2), (3), (4), (5);


-- EXCEPT filters for DISTINCT values
SELECT * FROM #NewCustomers AS nc
EXCEPT
SELECT * FROM #ExistingCustomers AS ec

-- NOT IN returns all values without filtering
SELECT * FROM #NewCustomers AS nc
WHERE ID NOT IN (SELECT ID FROM #ExistingCustomers AS ec)

    从上面的两个查询中，EXCEPT返回 3 行#NewCustomers，过滤掉匹配的 1 和 3#ExistingCustomers以及重复的 8。

    NOT IN不执行此不同的过滤并从#NewCustomers重复的 8 中返回 4 行。

    如果我们现在将 a 添加NULL到#ExistingCustomers表中，我们会看到由 返回的相同结果EXCEPT，但是NOT IN将返回一个空结果集。

    INSERT INTO #ExistingCustomers
        ( ID )
VALUES
        ( NULL );

-- With NULL values in the right-hand table, EXCEPT still returns the same results as above
SELECT * FROM #NewCustomers AS nc
EXCEPT
SELECT * FROM #ExistingCustomers AS ec

-- NOT IN now returns no results
SELECT * FROM #NewCustomers AS nc
WHERE ID NOT IN (SELECT ID FROM #ExistingCustomers AS ec)

DROP TABLE #NewCustomers;
DROP TABLE #ExistingCustomers;

    取而代之的是NOT IN，您应该真正查看Gail Shaw 的博客NOT EXISTS上的两者之间的一个很好的比较。

    • 32

  2. 马克辛金森出色评论的补充：

    NOT IN 要求您将一个表中的单个列与另一个表或子查询中的单个列进行比较。

    实际上，您可以NOT IN使用多个列执行。
    例如，这是一个完全合法的* SQL 查询：

    SELECT  E.first_name, E.last_name
FROM    employees E
WHERE   (E.first_name, E.last_name) NOT IN 
              (SELECT M.first_name, M.last_name FROM managers M)

    这将返回所有员工，但不是经理first_name。last_name

    *：但在 SQL Server 中尚未实现构造。

    • 1

  3. 上面的 NOT IN 失败，因为需要在主查询和子查询中的谓词之间存在关联。如果你忽略它，你会得到一个 UNCORRELATED 子查询。

    SELECT * FROM TableA AS nc WHERE ID NOT IN (SELECT ID, Name FROM TableB AS ec where nc.ID = ec.ID)

    EXCEPT 更好，可以在不使用 IS NULL / IS NOT NULL 谓词的情况下处理任何空行。

    • -2

相关问题