SQL "IN subquery" when subquery can be NULL

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.

For example

SELECT * FROM example_table WHERE id NOT IN (subquery_that_selects_ids)

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

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

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

---

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.

I.E. My query in rails looked like:

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

when Object.where(other_conditions) evaluated to [] the ? was being replaced with 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 upvoti开发者_如何学Gong anyone who told me the query would work as written, since they are correct. Thanks to @OMG Ponies and @Ted Elliott especially!

---

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

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

---

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

---

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

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.,

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

---

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 ;)

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.

Here is one example using hr.employees table.

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

This query will return 26 rows.

*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.

Hope this helped.

Thanks

Alexander Bufalo

---

Why wouldn't this work?

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