not like?

  • Comments posted to this topic are about the item not like?

  • I like these questions a lot more than making 'SQL puzzles', just by showing bad code. Your question is a typical sample of what I run into daily. Every DBA should use ISNULL in their queries, I guess that's the lesson for today 🙂

    Ronald HensbergenHelp us, help yourself... Post data so we can read and use it: http://www.sqlservercentral.com/articles/Best+Practices/61537/-------------------------------------------------------------------------2+2=5 for significant large values of 2

  • r.hensbergen (3/23/2009)


    I like these questions a lot more than making 'SQL puzzles', just by showing bad code. Your question is a typical sample of what I run into daily. Every DBA should use ISNULL in their queries, I guess that's the lesson for today 🙂

    Actually,

    They should use COALESCE, it's much easier to use, doesn't request nesting for multiple tests and is ANSI SQL compliant.



    --Mark Tassin
    MCITP - SQL Server DBA
    Proud member of the Anti-RBAR alliance.
    For help with Performance click this link[/url]
    For tips on how to post your problems[/url]

  • mtassin (3/23/2009)


    r.hensbergen (3/23/2009)


    I like these questions a lot more than making 'SQL puzzles', just by showing bad code. Your question is a typical sample of what I run into daily. Every DBA should use ISNULL in their queries, I guess that's the lesson for today 🙂

    Actually,

    They should use COALESCE, it's much easier to use, doesn't request nesting for multiple tests and is ANSI SQL compliant.

    ISNULL may not be ANSI SQL compliant, but I think you will find it faster than COALESCE when you only need to test a single value for a null value. Using COALESCE makes sense when you have multiple values and you want the first non-null value.

  • To Null Or Not To Null, but let's be consistent.

    This is usually hard to explain to a non techinical person who knows enough SQL to be dangerous.

    http://www.sqlservercentral.com/articles/Basic+Querying/understandingthedifferencebetweenisnull/871/

  • I know why the null value is not returned but I am puzzled by the explanation.

    "Since col2 is used in the WHERE clause, the output does not include the corresponding col1 value i.e. 4, even though no such 'not equal to NULL' condition is specified."

  • This happens to be one of my eternal blind spots.

    I have lost count of the number of times I had to correct a query because I forgot that using 'NOT LIKE' also implies "NOT NULL".

    The error may go unnoticed for ages because missing a few rows from the query results is not easy to spot.

    Thanks for the reminder ... but no doubt I'll make the same mistake again - sigh.

  • kevin.l.williams (3/23/2009)


    I know why the null value is not returned but I am puzzled by the explanation.

    "Since col2 is used in the WHERE clause, the output does not include the corresponding col1 value i.e. 4, even though no such 'not equal to NULL' condition is specified."

    It works like this, the records returned by the following query:

    select col1 from @test-2 where col2 not like '%del%'

    are those where the value in col2 is not like '%del%', meaning the the NOT LIKE is true. However, is col2 is null, this result of the NOT LIKE is false.

    Try the following:

    declare @TestVal int;

    set @TestVal = null; -- ensure @TestVal is null

    select

    case when @TestVal = null then 'equal null'

    when @TestVal <> null then 'not equal null'

    else 'undetermined'

    end;

  • Lynn Pettis (3/23/2009)


    kevin.l.williams (3/23/2009)


    I know why the null value is not returned but I am puzzled by the explanation.

    "Since col2 is used in the WHERE clause, the output does not include the corresponding col1 value i.e. 4, even though no such 'not equal to NULL' condition is specified."

    It works like this, the records returned by the following query:

    select col1 from @test-2 where col2 not like '%del%'

    are those where the value in col2 is not like '%del%', meaning the the NOT LIKE is true. However, is col2 is null, this result of the NOT LIKE is false.

    Try the following:

    declare @TestVal int;

    set @TestVal = null; -- ensure @TestVal is null

    select

    case when @TestVal = null then 'equal null'

    when @TestVal <> null then 'not equal null'

    else 'undetermined'

    end;

    Understood. I just don't think the authors explanation will help anyone who does not understand why 4 is not returned.

  • What's happening here is that comparisons with NULL always return "false". Not even NULL compares true to NULL. So, even though NULL (row 4) doesn't contain string "del", it still gives a "false" when compared with (like) '%del%'. Null means no value is assigned and you may not compare any value to an undetermined value.

    Comparisons with NULL return "false" whether you use equal (=) or not equal (!=), or use "like" or "not like". This code...

    If 'Hello' = NULL print 'Hello = Null'

    else print 'Hello != NULL'

    If 'Hello' != NULL print 'Hello != Null'

    else print 'Hello = NULL'

    returns the rather schizophrenic results....

    Hello != NULL

    Hello = NULL

    So, to cover yourself, you can explicitly code for the possibility of NULL with either "is NULL" or one of the operators "IsNull" or "Coalesce". like mtassin in previous post, I prefer coalesce.

  • john.arnott (3/23/2009)


    What's happening here is that comparisons with NULL always return "false". Not even NULL compares true to NULL. So, even though NULL (row 4) doesn't contain string "del", it still gives a "false" when compared with (like) '%del%'. Null means no value is assigned and you may not compare any value to an undetermined value.

    You are entirely correct, of course. For the purposes of explaining things to a newcomer (or trying to figure it out myself), though, I tend to rephrase phrases like NULL (row 4) doesn't contain string "del" as We DON'T KNOW if NULL (row 4) contains string "del".

    In my mental math, I substitute "who knows what!" for NULL. Does who knows what contain the string "del"? Who knows! That's the only way I can make sense of this stuff.

  • I agree with the above on this one. A good question, as it highlights a pitfall that even experienced coders get trapped by (and not just in SQL!). However, the explanantion is a complete cop-out. It provides no real explanation as to why row 4 isn't returned, and I don't think it would help increase peoples understanding of why certain rows don't get returned in their queries.

  • kevin.l.williams (3/23/2009)


    Lynn Pettis (3/23/2009)


    kevin.l.williams (3/23/2009)


    I know why the null value is not returned but I am puzzled by the explanation.

    "Since col2 is used in the WHERE clause, the output does not include the corresponding col1 value i.e. 4, even though no such 'not equal to NULL' condition is specified."

    It works like this, the records returned by the following query:

    select col1 from @test-2 where col2 not like '%del%'

    are those where the value in col2 is not like '%del%', meaning the the NOT LIKE is true. However, is col2 is null, this result of the NOT LIKE is false.

    Try the following:

    declare @TestVal int;

    set @TestVal = null; -- ensure @TestVal is null

    select

    case when @TestVal = null then 'equal null'

    when @TestVal <> null then 'not equal null'

    else 'undetermined'

    end;

    Understood. I just don't think the authors explanation will help anyone who does not understand why 4 is not returned.

    Yes, I understood what was going on (answered correctly) until I read the explanation. It should be mentioned somewhere in there that the NULL does not compare well to certain data types and therefore did not return a TRUE.

    EDIT: Don't get me wrong, the question is great. It applies to MS Access also. NULL is always screwing up my queries. I often wish the DB I work with spent a little more time in the planning stage and NULL was allowed less.

  • Actually,

    They should use COALESCE, it's much easier to use, doesn't request nesting for multiple tests and is ANSI SQL compliant.

    ISNULL may not be ANSI SQL compliant, but I think you will find it faster than COALESCE when you only need to test a single value for a null value. Using COALESCE makes sense when you have multiple values and you want the first non-null value.

    To throw another option into the mix for those that may not know about it:

    You could use IS NULL (or IS NOT NULL for the opposite) in the where clause. Depends on the question that the SQL is trying to answer.

    Good ol' SQL, "It Depends" seems to crop up a lot doesn't it 🙂

    -d

  • EDIT: Don't get me wrong, the question is great. It applies to MS Access also. NULL is always screwing up my queries. I often wish the DB I work with spent a little more time in the planning stage and NULL was allowed less.

    Although you should limit NULL where possible, I have also found NULL to be a very usefull tool, the problem is knowing of their existance in the first place and taking it into account when you build your query.

    Some people argue that you should put an empty string into a field instead of a NULL. This is not always possible or advisable.

    And if you do some kind of outer join, you will end up with NULLS even if there aren't any in the data tables; so rather than shun them, I say the opposite, embrace the NULL, learn to bend them to your will and you will find that they arent as bad as some people claim.

    At least, that's been my experience.

    -d

Viewing 15 posts - 1 through 15 (of 22 total)

You must be logged in to reply to this topic. Login to reply