Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


not like?


not like?

Author
Message
Olga B
Olga B
Mr or Mrs. 500
Mr or Mrs. 500 (596 reputation)Mr or Mrs. 500 (596 reputation)Mr or Mrs. 500 (596 reputation)Mr or Mrs. 500 (596 reputation)Mr or Mrs. 500 (596 reputation)Mr or Mrs. 500 (596 reputation)Mr or Mrs. 500 (596 reputation)Mr or Mrs. 500 (596 reputation)

Group: General Forum Members
Points: 596 Visits: 455
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
antony-688446
Old Hand
Old Hand (340 reputation)Old Hand (340 reputation)Old Hand (340 reputation)Old Hand (340 reputation)Old Hand (340 reputation)Old Hand (340 reputation)Old Hand (340 reputation)Old Hand (340 reputation)

Group: General Forum Members
Points: 340 Visits: 371
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
JF1081
SSC-Addicted
SSC-Addicted (440 reputation)SSC-Addicted (440 reputation)SSC-Addicted (440 reputation)SSC-Addicted (440 reputation)SSC-Addicted (440 reputation)SSC-Addicted (440 reputation)SSC-Addicted (440 reputation)SSC-Addicted (440 reputation)

Group: General Forum Members
Points: 440 Visits: 322
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 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
David in .AU
Mr or Mrs. 500
Mr or Mrs. 500 (530 reputation)Mr or Mrs. 500 (530 reputation)Mr or Mrs. 500 (530 reputation)Mr or Mrs. 500 (530 reputation)Mr or Mrs. 500 (530 reputation)Mr or Mrs. 500 (530 reputation)Mr or Mrs. 500 (530 reputation)Mr or Mrs. 500 (530 reputation)

Group: General Forum Members
Points: 530 Visits: 561


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 Smile

-d
David in .AU
David in .AU
Mr or Mrs. 500
Mr or Mrs. 500 (530 reputation)Mr or Mrs. 500 (530 reputation)Mr or Mrs. 500 (530 reputation)Mr or Mrs. 500 (530 reputation)Mr or Mrs. 500 (530 reputation)Mr or Mrs. 500 (530 reputation)Mr or Mrs. 500 (530 reputation)Mr or Mrs. 500 (530 reputation)

Group: General Forum Members
Points: 530 Visits: 561

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
john.arnott
john.arnott
UDP Broadcaster
UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)

Group: General Forum Members
Points: 1466 Visits: 3059
james (3/23/2009)

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.


Actually, NULL does not compare to ANY data type.

NULL means no value is determined. For example, the winning percentage of a sports team may be defined as Wins/Matches. But before the season starts, the value of Matches is zero. Since you cannot divide by zero, you may be tempted to say the winning percentage is also zero, but truly there is no value as there have been no matches. A losing team that has zero wins over five matches has a real winning percentage of zero, but a team that has yet to play has no percentage at all. This may sound like pedantry and symantic parsing for no good reason, but it does allow for a more accurate representation of the real world in data form.


Since the value of NULL is not determined, it cannot be compared to anything, not even another NULL. Try this:


Declare @myInt1 int
Declare @myInt2 int
set @myInt1 = NULL
set @myInt2 = @myInt2
Declare @mychar1 char
Declare @mychar2 char
set @mychar1 = NULL
set @mychar2 = @mychar2

select    @myInt1
      ,@myInt2
      ,@myChar1
      ,@myChar2

If @myInt1 = @myInt2
select 'Equal Int values'
else select 'Not Equal Int Values'

If @mychar1 = @mychar2
select 'Equal char values'
else select 'Not Equal char Values'

If @myInt1 = NULL
select 'Int1 = null'
else select 'Int1 Not Equal Null'

If @mychar1 = NULL
select 'Char1 Equal Null'
else select 'Char1 Not Equal Null'



VM-723206
VM-723206
Say Hey Kid
Say Hey Kid (662 reputation)Say Hey Kid (662 reputation)Say Hey Kid (662 reputation)Say Hey Kid (662 reputation)Say Hey Kid (662 reputation)Say Hey Kid (662 reputation)Say Hey Kid (662 reputation)Say Hey Kid (662 reputation)

Group: General Forum Members
Points: 662 Visits: 267
The explaination should have been:

Since col2 is used in the WHERE clause and happens to be a null value and since null cannot be compared to other values, the col1 value 4 is not brought in by the query.

I'd come across such a situation while analyzing one of the queries (which I hadn't noticed until later) and was puzzled why the query wasn't bringing in the data even though all the join conditions were met.

This was a nice thing to learn!

Thanks to all for the inputs! :-)
leandrohb
leandrohb
Forum Newbie
Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)

Group: General Forum Members
Points: 9 Visits: 119
My solution is:

declare @test table(col1 int,col2 varchar(8))
insert into @test values(1,'aaaa')
insert into @test values(2,'delbbbb')
insert into @test values(3,'delcccc')
insert into @test values(4,NULL)
insert into @test values(5,'dddd')

select col1 from @test where COALESCE(col2,'Null') not like '%del%'
DavE E-474734
DavE E-474734
Forum Newbie
Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)

Group: General Forum Members
Points: 5 Visits: 22
I understand all this but why doesn't Microsoft make everyones life easier and make it work as everyone expects

i.e. NULL does equal NULL

So we can use col2 = NULL or col2 != null instead of IS NULL or IS NOT NULL.

It would lead to so many less bugs
David in .AU
David in .AU
Mr or Mrs. 500
Mr or Mrs. 500 (530 reputation)Mr or Mrs. 500 (530 reputation)Mr or Mrs. 500 (530 reputation)Mr or Mrs. 500 (530 reputation)Mr or Mrs. 500 (530 reputation)Mr or Mrs. 500 (530 reputation)Mr or Mrs. 500 (530 reputation)Mr or Mrs. 500 (530 reputation)

Group: General Forum Members
Points: 530 Visits: 561
There is.

SET ANSI_NULLS OFF

the reason it is not recommended is because as the name of the variable suggests, you are turning off an ANSI standard which can lead to compatibility issues should that code ever be moved, etc.

but if you cant live without it, there you go.

No warranties implied blah blah blah.

-d

Edit as per BOL:
This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search