-
IS NOT NULL for nothing
would I be correct in saying that if you can determine that a field is not zero, it follows that is can definitely NOT be null (i.e. if you know it's not zero, it can't be unknown) so the second part of this clause will never be executed:
WHERE IT.MemberID <> 0 AND IT.MemberID IS NOT NULL
If this is the case, does removing it make any difference to the execution plan/query processor or will it just be ignored anyway?
-
-
short and sweet but I don't agree.
Null is no more not equal to zero than it is equal to zero.
By definition, it is unknown. Therefore, to say for certain that something is not equal to zero, it must be a known quantity which null is not.
Also, I've been reading up on short circuiting in sql server and it appears to work when only variables are involved but not when data is involved as the query processor/parser/planner kicks in and rearranges everything.
-
>> to say for certain that something is not equal to zero, it must be a known quantity which null is not.
Not really, not equal to zero can be anything other than zero. You can test it with simple query.
-
I actually stated to reply to this earlier with the same thing rmiao said, but after thinking about it, I started to second guess myself. So I did this simple test.
Created table TEST (COL1 SMALLINT NOT NULL, COL2 SMALLINT)
INSERT INTO TEST (COL1) VALUES(1);
INSERT INTO TEST (COL1, COL2) VALUES(2, 0);
SELECT *
FROM TEST
WHERE COL2 <> 0 AND COL2 IS NOT NULL
No rows returned.
SELECT *
FROM TEST
WHERE COL2 <> 0
No rows returned.
And just to check all possibilities:
SELECT *
FROM TEST
WHERE COL2 <> 0 OR COL2 IS NULL
1 row returned.
1, NULL
So it appears bobbo has a point. This makes sense after the fact because the only way you can check for NULL is with IS or IS NOT. Any other comparison (=, <, >, <>) will never result in a TRUE (if it is even valid syntax).
However, having the AND IS NOT NULL may help the database process the query in some fashion.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
|