I have two statements that seem the same that look for the same rows with the same null values. One returns the correct results and one returns 0 results. With this table having some null values:
Why is it that this statement returns 3 results:
But this block returns 0 results:
I realize the variable is an intermediate step, but the variable is null on initializing and then it is explicitly set to null so I do not understand the difference. |
I suspect that the ansinull option is set to ON on your connection when you ran your first statement, and your first statement, by itself without any other information, is treating the "AAItemID = null" as a TSQL comparison and therefore is using TSQL semantics. As such, the test for null will do what you appear to want - that is check for null values - and will return three rows. In your second example, the existence of the "begin ... end" block is telling SQL Anywhere that you are using the Watcom SQL dialect and hence is using ANSI semantics. As such the predicate "AAItemID = @NoVal" or even "AAItemID = null" will never match any rows because nothing equals null in ANSI-land. Please see the NULL value documentation for more information about the treatment of NULL in SQL Anywhere. As Ron has pointed out, if you are wanting to find null values, the correct method is to use "AAItemID IS NULL". Your suspicions are correct. ansinull was set to ON for both but I incorrectly assumed that "= null" would be the same as "is null" and typed them both with = to keep them as similar as possible, assuming that the variable was somehow to blame. Mark, correct me if I'm wrong, but "nothing equals null in ANSI-land" also means that NULL doesn't even equal NULL... which means that a predicate of "WHERE NULL = NULL" will return the same result as "WHERE 1 = 2" @Ron: IMHO, that's not completely true: 1=2 returns FALSE whereas NULL = NULL returns UNKNOWN. However, in a WHERE clause, only conditions evaluating to TRUE are returned, so in your example, both comparisons are not TRUE, and as such, they are filtered out. @Ron: To add: Note that for CHECK constraints, the logic is different: They are only violated by conditions that evaluate to FALSE. Therefore, something like CHECK(@value = 1) will accept both a value of 1 and the NULL value. So this is different from WHERE clauses. |
If you want to have a comparison that returns TRUE in both cases:
and
and you're using SA12, you can use the new (and ANSI-compliant) NOT DISTINCT FROM search condition. I.e.
is sematically the same as
However, the performance for NOT DISTINCT FROM should be better, as it is sargable. |