not like?

Question

not like?

VM-723206

SSCrazy

Points: 2964
More actions
March 21, 2009 at 11:40 am

#201416

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

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

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

Ronald H SSCarpal Tunnel Points: 4053 More actions · Answer 1

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

mtassin SSC-Insane Points: 23099 More actions · Answer 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]

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 3

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.

tkruse SSC Enthusiast Points: 171 More actions · Answer 4

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/

Kevin SSCarpal Tunnel Points: 4962 More actions · Answer 5

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."

mosaic-263591 Right there with Babe Points: 737 More actions · Answer 6

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.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 7

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;

Kevin SSCarpal Tunnel Points: 4962 More actions · Answer 8

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.

john.arnott SSChampion Points: 11882 More actions · Answer 9

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.

Olga B SSCommitted Points: 1529 More actions · Answer 10

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.

antony-688446 Ten Centuries Points: 1221 More actions · Answer 11

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.

JF1081 SSC Eights! Points: 982 More actions · Answer 12

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.

David in .AU SSCommitted Points: 1784 More actions · Answer 13

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

David in .AU SSCommitted Points: 1784 More actions · Answer 14

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