Recent PostsRecent Posts Popular TopicsPopular Topics
 Home Search Members Calendar Who's On

 ANSI NULLS Rate Topic Display Mode Topic Options
Author
 Message
 Posted Tuesday, October 19, 2010 10:58 PM
 Ten Centuries Group: General Forum Members Last Login: Tuesday, November 05, 2013 3:06 AM Points: 1,097, Visits: 747
 Thanks for your explanationHugo Kornelis (10/11/2010)Great question. Thanks, cengland.In addition to the explanation: "Thorough testing shows this also applies to the IN statement", there is also a logical explanation.The ANSI standard defines the IN operator as a series of OR'ed equation tests. In other word, the ANSI standard says that "x IN (a, b, c)" equates to "x = a OR x = b OR x = c". Or in the case of this question, "WHERE Column1 IN (1,NULL)" equates to "WHERE Column1 = 1 OR Column1 = NULL". Under ANSI null setting, any comparison to NULL always results in the truth value Unknown. So for the five rows in the sample table, here are the evaluation results:`Column1 | Column1 = 1 | Column1 = NULL | Column1 = 1 OR Column1 = NULL--------+-------------+----------------+------------------------------ 1 | True | Unknown | True 2 | False | Unknown | Unknown 3 | False | Unknown | Unknown 4 | False | Unknown | Unknown Null | Unknown | Unknown | Unknown`Only rows where the condition evaluates to True will be returned, so that is only 1 row.With ANSI NULLS OFF, the result of a NULL = NULL test changes to True, so the last line now changes to all True results. (I don't know if the result of a (not NULL) = NULL test changes to False under non-ANSI settings. I've never used them and since they are deprecated, I don't really care.)
Post #1007454

 Permissions