locked
These two queries should return the same results but they do not RRS feed

  • Question

  • Im curious why the two sql snippets do not produce the same result. The first one doesnt return correct results. Adding the check for null in the second fixes it but I do not understand why I need to do this.  Im able to filter out any occurences of 'AD' from the name column, but the rGroup doesnt seem to work the same. They are both varchar() fields and are both nullible. I want any record that is not a member of the 'C' group. Any ideas? Thanks:

     

     

    select DISTINCT a.rGroup ,A.ACCOUNT, A.[NAME], B.TranNum

    FROM AcMaster A join

    AcTran B on A.ACCOUNT = B.ACCOUNT

    where A.rGroup <> 'C' and

    A.[NAME] not like '%AD%'

    order by A.[NAME] asc

     

     

    select DISTINCT a.rGroup ,A.ACCOUNT, A.[NAME], B.TranNum

    FROM AcMaster A join

    AcTran B on A.ACCOUNT = B.ACCOUNT

    where 

    (A.rGroup is null or A.rGroup<> 'C')

    and

    A.[NAME] not like '%AD%'

    order by A.[NAME] asc

     

     

    Wednesday, October 22, 2008 11:36 PM

Answers

  • I am afraid, null cells are not counted by A.rGroup <> 'C' .

     

    Here is an example:

    Code Snippet

     

    use AdventureWorks2008;

    select count(*) from Production.Product

    -- 504

     

    select count(*) from Production.Product

    where Color ='red'

    -- 38

     

    select count(*) from Production.Product

    where Color <>'red'

    -- 218

     

    select count(*) from Production.Product

    where Color is null

    -- 248

     

    select count(*) from Production.Product

    where (Color is null or Color <>'red')

    -- 466

     

    Let us know if helpful.

    Thursday, October 23, 2008 12:18 AM

All replies

  • I am afraid, null cells are not counted by A.rGroup <> 'C' .

     

    Here is an example:

    Code Snippet

     

    use AdventureWorks2008;

    select count(*) from Production.Product

    -- 504

     

    select count(*) from Production.Product

    where Color ='red'

    -- 38

     

    select count(*) from Production.Product

    where Color <>'red'

    -- 218

     

    select count(*) from Production.Product

    where Color is null

    -- 248

     

    select count(*) from Production.Product

    where (Color is null or Color <>'red')

    -- 466

     

    Let us know if helpful.

    Thursday, October 23, 2008 12:18 AM
  • So it  doesnt even attempt a comparrison? Just skips it?

    Thursday, October 23, 2008 12:31 AM
  • Think for a moment about what, exactly the term NULL means in SQL, which is different then being blank ('').

     

    A NULL identifier simply indicates that a value does not even exist so there is nothing SQL can even compare it to.

    A '' value will at least give SQL something to use while comparing records.

     

    Therefore '' = '' but NULL doesn't equal anything, not even NULL.

     

    If you don't specify any comparisons then SQL simply returns all the records, including NULLs.  But as soon as you throw in a comparison operator (such as: = < > ...)  then NULL values are the first thing SQL will filter out.  Afterwards it performs the requested comparisons on the remaining records.

     

    So why doesn't SQL just assign a '' value instead of NULL?  --  Space & Performance are 2 main reasons.

     

    This would actually cause SQL to allocate more space on a page in order to hold the '' values.  Leaving the fields NULL will allow SQL to utilize this space to hold more records instead.  Additionally there is a relative performance impact for SQL to match up all '' records, and depending on your data and join types, among other things, this could be huge.

     

    Hope this helps!

     

     

    Thursday, October 23, 2008 2:39 AM
  •  

    Thanks for the info. I do have one nit picky question though. I get that Null doesnt even equal Null, but Null certainly doesnt equal 'C' right? So a comparrison is still possible. Just ignoring it seems kinda silly to me. 

     

    So I guess whenever I use an operator on a nullible field I should use ISNull() then?

    Thursday, October 23, 2008 4:09 AM
  •  

    [QUOTE=Yeshia] I do have one nit picky question though. I get that Null doesnt even equal Null, but Null certainly doesnt equal 'C' right? So a comparrison is still possible. Just ignoring it seems kinda silly to me. 

    Well, to be picky, you don't know if NULL equals C or not! Wink

    Thursday, October 23, 2008 11:01 AM
    Answerer
  • touche.. touche... but Im still gonna be argumentative. I can conceive of no two NULLS being alike. But I can not understand how NULL could ever equal 'C'. To me, NULL is certainly not 'C' or it would not be NULL. IMO, logic says when doing a comparrison of type <> against a value

    NULLS should be returned. Anywhere where I could read a technical article explaining this behavior? thanks..

    Thursday, October 23, 2008 10:07 PM
  • You know, I can certainly appreciate your reasoning on this.  One important aspect of logic is order or sequence. 

    For the longest time I could not understand why 10 + 4 / 2 did not equal 7, and why it always equals 12. 

     

    If you took a calculator and entered the number

    10 , followed by the 

     + sign, followed by the number

     4 , followed by the 

     /  sign, followed by the number

     2 , followed by the

     = sign you will get 

     7  as your answer. 

    This sequence exactly follows the way it is written above.

     

    Now, if you run the following select statement in SQL: 

    SELECT 10 + 4 / 2

    you will get 12 as your answer.

     

    To get the same results as the calculator returned you would have to specify the order of calcuation you want SQL to use.

    Your select statement would need to be

    SELECT (10 + 4) / 2

    This will return 7 as your answer.

     

    The reason for this, as you probably already know, is due to the algebraic order of calculation.  In short being:

    ( ), ×, ÷, +, -

    This is the worldwide accepted order used to calculate algebraic expressions.

    It doesn't matter if it makes logical sense in my book or not, it's simply the standard.

     

    Using the ( )'s we are telling SQL that we want it to calcuate 10+4 before we divide by 2 otherwise, without ( )'s, it will follow the algebraic order and solve 4/2 (resulting in 2) before adding 10, thus resulting in 12.

     

    I'm not trying to bog you down with a boring algebra course, I'm simply tring to illustrate the importance that order has when it comes to logical expressions.

     

    Following the order as the expression is written also makes perfect logical sense as long as that is the consensus which everyone else will use as well.  (Although the algebraic order does allow for more structure and consistency.)

     

    So, is your logic wrong?  Absolutely not!

    Is it the same logic that SQL uses?  Unfortunately no.

     

    The order SQL uses, as far as logical operators go, is:

    NOT, AND, OR

    However, before it performs any comparisons in this order it always checks for Existance first, as if this were a seperate operation in itself, kind of like:

    EXISTS, NOT, AND, OR  (This is evaluated per record)

     

    You can specify the order that SQL matches records, much like you would rearange the algebraic order using ( )'s.

    To do this you would use one of the 2 methods described in an earlier post.

    In doing so you are telling SQL that you want NULL values to be considered which is why you have to say

    "name <> 'C' or name IS NULL". 

    Otherwise SQL is going to answer the comparison the fastest way it knows how, which is to eleminate any records in which a value does not exist (in other words, NULL).  Then it will look for the next comparison to perform, that being NOT.

     

    Although logic stands to be reasonable, nobody said anything about it having to be sensible.

    Thursday, November 6, 2008 11:52 PM