Results 1 to 5 of 5

Thread: IS NOT NULL for nothing

  1. #1
    Join Date
    Oct 2005
    Location
    Ireland
    Posts
    92

    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?

  2. #2
    Join Date
    Sep 2002
    Posts
    5,938
    But null <> 0.

  3. #3
    Join Date
    Oct 2005
    Location
    Ireland
    Posts
    92
    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.

  4. #4
    Join Date
    Sep 2002
    Posts
    5,938
    >> 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.

  5. #5
    Join Date
    Apr 2009
    Posts
    86
    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
  •