Ah. Thanks for the clarification. It seems to me that using NULL as 'anything' or 'unknown' - it becomes a wildcard of sorts and could create a lot of confusion in queries.
note to self : use NULL with extreme caution. Ron Wilson, S/W Systems Engineer III, Tyco Electronics, 434.455.6453 -----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of D. Richard Hipp Sent: Wednesday, June 25, 2008 12:27 PM To: General Discussion of SQLite Database Subject: Re: [sqlite] bug with NULL in NOT IN On Jun 25, 2008, at 12:12 PM, Wilson, Ron P wrote: > Why should the second query return zero rows? Clearly ids 1 and 2 > don't > exist in b. The meaning of "NULL" in SQL is overloaded. In some contexts NULL means "anything" or "unknown". In other contexts it means "nothing". If we assume NULL means "nothing" then your statement above is correct. But if we assume NULL means "anything" or "unknown" then we don't know if the right-hand side (RHS) of the NOT IN contains a 1 or 2 because it contains a NULL which is a placeholder for an unknown value which might be a 1 or a 2 - we just don't know. If I understand Peter correctly, he is saying that NULL should mean "unknown" in the context of the RHS of a NOT IN operator. SQLite does not currently operate this way. SQLite currently interprets a NULL in the RHS of a NOT IN operator to mean "nothing". D. Richard Hipp [EMAIL PROTECTED] _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
