问题:SQL "IN subquery" 当子查询可以为NULL时

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

例如

SELECT * FROM example_table WHERE id NOT IN (subquery_that_selects_ids)

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

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

解决方案需要在 SQLite 或 postgresql 中运行。如何防止子查询返回空集?

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

IE。我在 Rails 中的查询如下所示:

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

当Object.where(other_conditions)评估为[]时,?被替换为NULL

所以我重新编写查询看起来像:

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

问题解决了。

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

解答

尝试:

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

我认为你有点复杂,即使子查询中没有行,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 无论如何都会消除重复,UNION ALL 保留重复