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 upvoting anyone who told me the query would work as written, since they are correct. Thanks to @OMG Ponies and @Ted Elliott especially!
For a scalar subquery: If the subquery returns no rows, the result of the scalar subquery is NULL . If the subquery returns more than one row, it is an error. If the subquery returns one row, the result is the value of the query's (only) column for that row.
Subqueries cannot manipulate their results internally, that is, a subquery cannot include the order by clause, the compute clause, or the into keyword. Correlated (repeating) subqueries are not allowed in the select clause of an updatable cursor defined by declare cursor. There is a limit of 50 nesting levels.
Subqueries are not allowed in the defining query of a CREATE PROJECTION statement. Subqueries are supported within UPDATE statements with the following exceptions: You cannot use SET column = {expression} to specify a subquery.
A subquery can be nested inside the WHERE or HAVING clause of an outer SELECT , INSERT , UPDATE , or DELETE statement, or inside another subquery.
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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With