Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I had a query (for Postgres and Informix) with a NOT IN clause containing a subquery that in some cases returned NULL values, causing that clause (and the entire query) to fail to return anything.

What's the best way to understand this? I thought of NULL as something without a value, and therefore wasn't expecting the query to fail, but obviously that's not the correct way to think of NULL.

share|improve this question
up vote 18 down vote accepted

Boolean logic - or Three valued logic

  • IN is shorthand for a series of OR conditions
  • x NOT IN (1, 2, NULL) is the same as NOT (x = 1 OR x = 2 OR x = NULL)
  • ... is the same as x <> 1 AND x <> 2 AND x <> NULL
  • ... is the same as true AND true AND unknown **
  • ... = unknown **
  • ... which is almost the same as false in this case as it will not pass the WHERE condition **

Now, this is why folk use EXISTS + NOT EXISTS rather than IN + NOT IN. Also see The use of NOT logic in relation to indexes for more

** Note: unknown is the same as false at the end of an expression in a WHERE condition.
While the expression is being evaluated, then it is unknown
See @kgrittn's comment below for why

share|improve this answer
7  
Even with the clarification it is technically wrong, and in a way that could burn someone. For example, if you view x <> NULL as resolving to FALSE, you would expect NOT (x <> NULL) to evaluate to TRUE, and it doesn't. Both evaluate to UNKNOWN. The trick is that a row is selected only if the WHERE clause (if present) evaluates to TRUE -- a row is omitted if the clause evaluates to either FALSE or UNKNOWN. This behavior (in general, and for the NOT IN predicate in particular) is mandated by the SQL standard. – kgrittn May 3 '12 at 14:35
    
Also NULL NOT IN (some_subquery) should not return the outer row except if some_subquery doesn't return any rows. Which is why the execution plan when both columns are Null-able can be considerably more expensive. SQL Server Example – Martin Smith Jun 18 '12 at 7:08

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.