SQL "IN subquery" when the subquery may be NULL

Question

I have a query that should return results that DO NOT match in the subquery. A sub query can return an empty result, so I need to set a default value (say, 0) if the auxiliary 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 the subquery does not find the corresponding results.

COALESCE does not work here, since a sub-query is likely to return more than one result.

Solutions should work in SQLite or postgresql. How can I prevent a subquery from returning an empty set?

---

Everyone tells me that the request should work as written. And you are all right. The request is built by Rails3 AREL, as I was going to post the full request here. I noticed that AREL put NULL for an empty set when using array conditions.

those. My query on rails looked like this:

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

when Object.where(other_conditions) evaluates to [] ? replaced by NULL

So, I rewrite the request like this:

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

The problem is resolved.

I do justice to @Michael Buen, but also support anyone who told me that the request will work as it is written, as they are true. Especially thanks to @OMG Ponies and @Ted Elliott!

Answer 1

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 complicate this a bit, NOT IN will have rows, even there are no rows in the subquery. Your request will work unchanged. In any case, if you really want your subquery to produce rows (rows), even if the conditions were not met, 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 removes duplicates anyway, UNION ALL saves duplicates

Answer 2

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 

Answer 3

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

You might think of a situation where a subquery is used as a scalar. In this case, the result value is null if the subquery does not return rows, for example,

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

Answer 4

Why is this not working?

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

Answer 5

There will be problems in a multi-valued subquery if you use NOT in the AND operator, if the set returned by the subquery contains NULL. If the subquery is an empty set, this does not mean that it returns null;)

In the case of adding a null value, it forces the external query to return one empty set, because it cannot tell whether the value is in UNKNOWN or NOT IN Unknown.

Here is one example using the 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 does not return any rows, because NULL in the list passed to NOT IN will ruin it. SO, if your subquery can return any null value, you must process it using the functions (NVL, NVL2, COALESCE) in the subquery.

Hope this helped.

thanks

Alexander Bufalo