是否有可能在带有 NOT IN 子句的子查询成功时 LEFT JOIN 失败

Question

一段时间，我已经发布了这个问题的答案PostgreSQL多条件声明。

任务非常简单 - 如果另一个表中没有相应的值，则从一个表中选择值。假设我们有如下表：

CREATE TABLE first  (foo numeric);
CREATE TABLE second (foo numeric);

我们希望从first.foo中获取second.foo中未发生的所有值。我提出了两个解决方案：

• 使用LEFT JOIN

SELECT first.foo
FROM first
LEFT JOIN  second 
ON first.foo = second.foo
WHERE second.foo IS NULL;

• 组合子查询和IN运算符：

SELECT first.foo 
FROM first
WHERE first.foo NOT IN (
  SELECT second.foo FROM second
);

由于某种原因，第一个在 OP 的上下文中不起作用（返回 0 行），从那时起它一直困扰着我。我试图使用不同版本的PostgreSQL重现该问题，但到目前为止没有运气。

是否有任何特殊原因导致第一个解决方案失败而第二个解决方案按预期工作？我错过了一些明显的东西吗？

这是sqlfiddle，但它似乎适用于任何可用的平台。

编辑

就像@bma和@MostyMostacho在评论中指出的那样，它应该是第二个没有返回结果（sqlfiddle）的。

Answer 1

根据您的 sql 小提琴，由于第二个表中的 NULL 而无法返回结果。

问题是 NULL 表示"未知"，因此我们不能说以下表达式是正确的：10 not in (5, null) .

原因是比较 10 = NULL 时会发生什么。 我们得到一个空，而不是真的。 这意味着 NOT IN 子句中的 NULL 表示不会传递任何行。

要让第二个查询按照您期望的方式执行，您有一个相对复杂的查询：

SELECT first.foo 
FROM first
WHERE (first.foo  IN (
  SELECT second.foo FROM second
) IS NOT TRUE);

这将正确处理 NULL 比较，但联接语法可能更简洁。

Answer 2

select values from one table if there is no corresponding value in another table. 你刚刚回答了你自己的问题：

SELECT o.value
FROM table_one o
WHERE NOT EXISTS (
    SELECT *
    FROM table_two t
    WHERE t.value = o.value
    );

---

一个简短的演示：

CREATE TABLE first  (foo numeric);
CREATE TABLE second (foo numeric);
INSERT INTO first VALUES (1);
INSERT INTO first VALUES (2);
INSERT INTO first VALUES (3);
INSERT INTO first VALUES (4);
INSERT INTO first VALUES (5);
INSERT INTO first VALUES (NULL); -- added this for completeness
INSERT INTO second VALUES (1);
INSERT INTO second VALUES (3);
INSERT INTO second VALUES (NULL);

SELECT f.foo AS ffoo, s.foo AS sfoo
        -- these expressions all yield boolean values
        , (f.foo = s.foo)                                               AS is_equal
        , (f.foo IN (SELECT foo FROM second))                           AS is_in
        , (f.foo NOT IN (SELECT foo FROM second))                       AS is_not_in
        , (EXISTS (SELECT * FROM second x WHERE x.foo = f.foo))         AS does_exist
        , (NOT EXISTS (SELECT * FROM second x WHERE x.foo = f.foo))     AS does_not_exist
        , (EXISTS (SELECT * FROM first x LEFT JOIN second y ON x.foo = y.foo
                WHERE x.foo = f.foo AND y.foo IS NULL))
                                                                        AS left_join_is_null
FROM first f
FULL JOIN second s ON (f.foo = s.foo AND (f.foo IS NOT NULL OR s.foo IS NOT NULL) )
        ;

---

结果：

CREATE TABLE
CREATE TABLE
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
 ffoo | sfoo | is_equal | is_in | is_not_in | does_exist | does_not_exist | left_join_is_null 
------+------+----------+-------+-----------+------------+----------------+-------------------
    1 |    1 | t        | t     | f         | t          | f              | f
    2 |      |          |       |           | f          | t              | t
    3 |    3 | t        | t     | f         | t          | f              | f
    4 |      |          |       |           | f          | t              | t
    5 |      |          |       |           | f          | t              | t
      |      |          |       |           | f          | t              | f
      |      |          |       |           | f          | t              | f
(7 rows)

如您所见，布尔值对于IN()和等于情况可以是 NULL。对于EXISTS()情况，它不能为 NULL。成为或不成为。LEFT JOIN ... WHERE s.foo IS NULL（几乎）等同于 NOT EXISTS 情况，只是它实际上包含查询结果中的second.*（在大多数情况下不需要）