postgresql 当子查询可以为 NULL 时，SQL“IN subquery”

SQL "IN subquery" when subquery can be NULL

提问by SooDesuNe

I have a query that needs to return results that are NOT matched in a sub query. The sub query can return an empty result, so I need to set a default value (say 0) if the sub query returns an empty set to prevent IN (NULL)which always returns another NULL.

我有一个查询需要返回在子查询中不匹配的结果。子查询可以返回一个空结果，所以如果子查询返回一个空集，我需要设置一个默认值（比如 0），以防止IN (NULL)它总是返回另一个 NULL。

For example

例如

SELECT * FROM example_table WHERE id NOT IN (subquery_that_selects_ids)

subquery_that_selects_idscan return a set of integers, i.e. (1,2,5,6) or an empty set if subquery finds no matching results.

subquery_that_selects_ids如果子查询没有找到匹配的结果，可以返回一组整数，即 (1,2,5,6) 或空集。

COALESCEdoesn't work here, since the sub query will likely return more than one result.

COALESCE在这里不起作用，因为子查询可能会返回多个结果。

Solutions need to work in SQLite or postgresql. How can I prevent the sub query from returning an empty set?

解决方案需要在 SQLite 或 postgresql 中工作。如何防止子查询返回空集？

---

---

Everyone is telling me that the query should work as written. And you are all correct. The query is being built by Rails3's AREL, as I was about to post the full query here I noticed that AREL was putting NULL in for an empty set when using array conditions.

每个人都告诉我查询应该按书面方式工作。你们都是对的。该查询是由 Rails3 的 AREL 构建的，因为我正要在这里发布完整的查询，我注意到 AREL 在使用数组条件时将 NULL 放入空集。

I.E. My query in rails looked like:

IE 我在 rails 中的查询看起来像：

Object.where("id NOT IN (?)", Object.where(other_conditions).select(:id))

when Object.where(other_conditions)evaluated to []the ?was being replaced with NULL

当Object.where(other_conditions)评价为[]所述?正在取代NULL

So I re-write the query to look like:

因此，我将查询重新编写为如下所示：

Object.where("id NOT IN (" + Object.where(other_conditions).select(:id).to_sql + ")")

Problem solved.

问题解决了。

I'm giving credit to @Michael Buen, but also upvoting anyone who told me the query would work as written, since they are correct. Thanks to @OMG Ponies and @Ted Elliott especially!

我感谢@Michael Buen，但也赞成任何告诉我查询将按书面方式工作的人，因为它们是正确的。特别感谢@OMG Ponies 和@Ted Elliott！

采纳答案by Michael Buen

Try:

尝试：

SELECT * FROM example_table 
WHERE id NOT 
    IN (select x.id from subquery_that_selects_ids as x where x.id is not null)

I think you are complicating it a bit, NOT IN will have rows even there's no rows in subquery. Your query will work without modification. Anyway, if you really desire your subquery to yield row(s) even if the conditions wasn't satisfied, use UNION

我认为你有点复杂，即使子查询中没有行，NOT IN 也会有行。您的查询无需修改即可使用。无论如何，如果您真的希望您的子查询即使条件不满足也能产生行，请使用 UNION

SELECT * FROM example_table 
WHERE id NOT 
    IN (select x.id from subquery_that_selects_ids as x 
        where 1 = 0 -- empty set
        union
        select 0)

UNION eliminates duplicate anyway, UNION ALL preserve duplicates

UNION 无论如何都会消除重复项，UNION ALL 保留重复项

回答by Peter Eisentraut

I think you are confusing something. The query you posted works just fine if subquery_that_selects_idsreturns an empty set (so every row from example_tableis selected). There are no implicit null values involved here.

我认为你在混淆一些东西。如果subquery_that_selects_ids返回一个空集，您发布的查询就可以正常工作（因此example_table选择了其中的每一行）。这里不涉及隐式空值。

You may be thinking of the situation where a subquery is used as a scalar. In that case, the result value is null if the subquery returns no rows, e.g.,

您可能会想到将子查询用作标量的情况。在这种情况下，如果子查询不返回任何行，则结果值为 null，例如，

SELECT * FROM example_table WHERE id = (SELECT id FROM other_table WHERE name = 'foo')

回答by SteveCav

what about:

关于什么：

SELECT ex.ID, ex.OtherFields
FROM ExampleTable ex left join (Select ID from SomeOtherTable) o on o.ID = ex.ID
WHERE o.ID is null

回答by user3390665

On multi-valued subquery will have problems if you use the NOT in operator AND if the set returned by the subquery contains a NULL value. If the subquery is an empty set, that doesn't mean that it returned null ;)

如果您使用 NOT in 运算符并且子查询返回的集合包含 NULL 值，则在多值子查询上将出现问题。如果子查询是一个空集，这并不意味着它返回 null ;)

In the case of containing a null value, it forces the outer query to return one empty set because it cannot say if the value is in UNKNOWN or NOT IN Unknown.

在包含空值的情况下，它会强制外部查询返回一个空集，因为它无法判断该值是处于 UNKNOWN 还是 NOT IN Unknown。

Here is one example using hr.employees table.

这是一个使用hr.employees 表的示例。

*SELECT last_name FROM hr.employees WHERE commission_pct NOT IN (0.1,0.35);*

*从hr.employees 中选择姓氏，其中commission_pct NOT IN (0.1,0.35);*

This query will return 26 rows.

此查询将返回 26 行。

*SELECT last_name FROM hr.employees WHERE commission_pct NOT IN (0.1,0.35,NULL);*

*SELECT last_name FROM hr.employees WHERE Commission_pct NOT IN (0.1,0.35,NULL);*

This query returns no rows because the NULL in the list passed to NOT IN will spoil it out. SO, if your subquery may return any null value, you should treat it with the functions (NVL,NVL2,COALESCE) in the subquery.

此查询不返回任何行，因为传递给 NOT IN 的列表中的 NULL 会破坏它。所以，如果您的子查询可能返回任何空值，您应该使用子查询中的函数 (NVL,NVL2,COALESCE) 来处理它。

Hope this helped.

希望这有帮助。

Thanks

谢谢

Alexander Bufalo

亚历山大·布法罗

回答by Sean

Why wouldn't this work?

为什么这行不通？

SELECT *
  FROM example_table
 WHERE id IN (
    SELECT COALESCE(id, 0)
      FROM another_example_table
);