Take the 2-minute tour ×
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.

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
    
stackoverflow.com/a/129152/27535 and many others. –  gbn May 3 '12 at 13:25
    
1  
read this and weep :-) –  Jack Douglas May 4 '12 at 14:54

1 Answer 1

up vote 14 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
3  
@ErwinBrandstetter: unknown is not true therefore it fails comparisons therefore it is false for practical purposes. Quite simple, no matter how pedantic you want to be... –  gbn May 4 '12 at 4:28
1  
SQL-Priest: Your sins have been forgiven :) –  ypercube May 4 '12 at 14:20
1  
@kgrittn Is the behaviour of select * from (select 1 as foo union all select 2 union all select null) z where not (foo not in (1, null)); in the SQL standard? I've given up being surprised by null –  Jack Douglas May 4 '12 at 15:04
3  
@JackDouglas: That is truly an evil query. Nice example! The way I read the standard, the WHERE clause is equivalent to not (foo <> 1 and foo <> null) which is equivalent to foo = 1 or foo = null. Since, in three-state logic, TRUE OR UNKNOWN is TRUE, the result should show the row with 1. UNKNOWN OR'd with FALSE or UNKNOWN is UNKNOWN, so the other rows should not show. (Remember NULL = NULL is UNKNOWN, since the actual value of either value isn't known.) –  kgrittn May 4 '12 at 18:35

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.