SQL“select where not in subquery”不返回任何结果

有三种方法可以执行此类查询：

LEFT JOIN / IS NULL:

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(and, therefore, NOT IN) 当一个值与包含空值的列表中的任何内容不匹配时，返回空值。 这可能令人困惑，但如果我们调用此的备用语法，则可能会变得更加明显：

common_id = ANY
(
SELECT  common_id
FROM    table1 t1
)

此条件的结果是列表中所有比较的布尔乘积。当然，单个空值会产生空结果，而整个结果也会呈现为空。 我们不能肯定地说common_id不等于这个列表中的任何内容，因为至少有一个值是NULL。 假设我们有这些数据:

common

--
1
3

table1

--
NULL
1
2

LEFT JOIN / IS NULL 和 NOT EXISTS 将返回 3, NOT IN 将不返回任何东西 (因为它的值总是为FALSE或NULL)。

在MySQL中，对于非空列，LEFT JOIN / IS NULL和NOT In要比NOT EXISTS稍微高效一些(几个百分点)。如果该列是可空的，那么NOT EXISTS是最有效的(同样，不是很多)。

在Oracle中，所有三个查询都产生相同的计划( 一个ANTI JOIN)。

在SQL Server中，NOT In / NOT EXISTS更有效，因为LEFT JOIN/ IS NULL不能被其优化器优化为ANTI JOIN。

在PostgreSQL中，LEFT JOIN / IS NULL和NOT EXISTS比NOT In更有效，因为它们被优化为ANTI JOIN，而 NOT IN使用散列子计划(如果子查询太大而不能散列，甚至可以使用普通的子计划)