<>Update:

在我的博客中,这些条款更详细地描述了方法之间的差别:

• NOT IN vs. NOT EXISTS vs. LEFT JOIN / ISNUL: SQL Server
• NOT IN vs. NOT EXISTS vs. LEFT JOIN / ISNUL: PostgreSQL
• NOT IN vs. NOT EXISTS vs. LEFT JOIN / ISNUL: Oracle
• NOT IN vs. NOT EXISTS vs. LEFT JOIN / ISNUL: MySQL

有三个方法可以提出这样的问题:

• LEFT JOIN / ISNUL:

  SELECT  *
  FROM    common
  LEFT JOIN
          table1 t1
  ON      t1.common_id = common.common_id
  WHERE   t1.common_id IS NULL
  

• <编码> NOT EXISTS:

  SELECT  *
  FROM    common
  WHERE   NOT EXISTS
          (
          SELECT  NULL
          FROM    table1 t1
          WHERE   t1.common_id = common.common_id
          )
  

• <编码> NOT IN:

  SELECT  *
  FROM    common
  WHERE   common_id NOT IN
          (
          SELECT  common_id
          FROM    table1 t1
          )
  

当table1.common_id不可否认时,所有这些问题都是从属性的。

如无效,NOT IN则有所不同,因为IN(因此,NOT IN) 返回NUL,如果某一数值与载有< NUL的名单中的任何内容不一致的话。

这可能令人困惑,但如果我们回顾一下这方面的候补辛迪加的话,可能变得更加明显:

common_id = ANY
(
SELECT  common_id
FROM    table1 t1
)

这一状况的结果是清单内所有比较的 b。 当然,单一<代码>NUL 数值产生<代码>NUL,结果使整个结果成为NUL。

我们永远不能说,<代码>common_id并不等于这一清单的任何内容,因为至少有一种价值是NUL。

我们有这些数据:

common

--
1
3

table1

--
NULL
1
2

。 (因为总是对<代码>FALSE或>NUL进行评价。)

在<代码>MySQL中,对于无损栏目,LEFT JOIN / IS NUL和的,其效率比低一些(单位:%) NOT EXISTS。 如果该栏不可行,NOT EXISTS则效率最高(而不是很大)。

在<代码>Oracle 中,所有三项查询都产生了相同的计划(ANTI JOIN)。

在<代码>SQL 服务器/代码>上,NOT IN/更有效率,因为LEFT JOIN / IS NUL不能优化为。 ANTI JOIN by its Bester.

PostgreSQL, LEFT JOIN / IS NUL和NOT EXISTS效率大于NOT IN,其ine被优化为Anti Join,而NOT IN >则使用hashed subplan (或甚至>,条件是该子座标价太大而已满。

SELECT custid, companyname
FROM Sales.Customers
WHERE custid NOT IN(SELECT O.custid
             FROM Sales.Orders AS O);

但是,如果客户的客户身份证明没有出现在订单中的非联合国利比里亚办事处客户身份证明中,并且还有一张<>UNL。 订单中的客户身份证明,你不能肯定地告诉客户有,同样,你也不能肯定地告诉客户没有客户。 没收? 我希望我能以一个实例澄清这一解释。

。 这是因为,如果你与已知客户身份比较,你就会获得FLSE,如果你与全国人民力量比较,你就会获得<代码>。 UNKNOWN。 。 考虑以下表述:22 NOT IN (1, 2, <other non-22 Value> NUL)。 这一表述可改写为。 您可将这一表述扩大到NOT (22 = 1 OR 22 = 2 OR OR 22 = NUL。 将括号中的每一个人表述评价到其真实价值上,并查阅<代码>。 注

The logical meaning of UNKNOWN here, before you apply the NOT operator, is that it can’t be determined whether the customer ID appears in the set, because the NULL could represent that customer ID. The tricky part here is that negating the UNKNOWN with the NOT operator still yields UNKNOWN. This means that in a case where it is unknown whether a customer ID appears in a set, it is also unknown whether it doesn’t appear in the set. Remember that a query filter discards rows that get UNKNOWN in the result of the predicate.

In short, when you use the NOT IN predicate against a subquery that returns at least one NULL, the query always returns an empty set. So, what practices can you follow to avoid such trouble? First, when a column is not supposed to allow NULLs, be sure to define it as NOT NULL. Second, in all queries you write, you should consider NULLs and the three-valued logic. Think explicitly about whether the query might process NULLs, and if so, whether SQL’s treatment of NULLs is correct for you. When it isn’t, you need to intervene. For example, our query returns an empty set because of the comparison with the NULL. If you want to check whether a customer ID appears only in the set of known values, you should exclude the NULLs—either explicitly or implicitly. To exclude them explicitly, add the predicate O.custid IS NOT NULL to the subquery, like this:

SELECT custid, companyname
FROM Sales.Customers
WHERE custid NOT IN(SELECT O.custid
                    FROM Sales.Orders AS O
                    WHERE O.custid IS NOT NULL);

You can also exclude the NULLs implicitly by using the NOT EXISTS predicate instead of NOT IN, like this:

SELECT custid, companyname
FROM Sales.Customers AS C
WHERE NOT EXISTS
   (SELECT *
    FROM Sales.Orders AS O
    WHERE O.custid = C.custid);

Recall that unlike IN, EXISTS uses two-valued predicate logic. EXISTS always returns TRUE or FALSE and never UNKNOWN. When the subquery stumbles into a NULL in O.custid, the expression evaluates to UNKNOWN and the row is filtered out. As far as the EXISTS predicate is concerned, the NULL cases are eliminated naturally, as though they weren’t there. So EXISTS ends up handling only known customer IDs. Therefore, it’s safer to use NOT EXISTS than NOT IN.

以上信息摘自第4章——分册、T-SQL Fundamentals,第三版<>。