Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««123»»

not like? Expand / Collapse
Author
Message
Posted Monday, March 23, 2009 1:33 PM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Friday, September 12, 2014 4:57 PM
Points: 582, Visits: 453
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.
Post #681788
Posted Monday, March 23, 2009 2:22 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, September 16, 2014 11:36 PM
Points: 269, Visits: 308
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.
Post #681842
Posted Monday, March 23, 2009 2:59 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Tuesday, December 7, 2010 8:13 AM
Points: 436, 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.
Post #681882
Posted Monday, March 23, 2009 3:55 PM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Sunday, August 31, 2014 6:29 PM
Points: 522, Visits: 555


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
Post #681933
Posted Monday, March 23, 2009 4:04 PM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Sunday, August 31, 2014 6:29 PM
Points: 522, Visits: 555

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
Post #681938
Posted Monday, March 23, 2009 4:07 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Thursday, August 28, 2014 8:53 PM
Points: 1,521, Visits: 3,039
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'



Post #681941
Posted Monday, March 23, 2009 11:11 PM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Tuesday, July 23, 2013 6:34 AM
Points: 654, Visits: 265
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!
Post #682065
Posted Tuesday, March 24, 2009 6:59 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, September 15, 2014 11:34 AM
Points: 9, Visits: 105
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%'
Post #682268
Posted Tuesday, March 24, 2009 5:41 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, June 16, 2009 4:16 PM
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
Post #682976
Posted Tuesday, March 24, 2009 10:21 PM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Sunday, August 31, 2014 6:29 PM
Points: 522, Visits: 555
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.
Post #683027
« Prev Topic | Next Topic »

Add to briefcase ««123»»

Permissions Expand / Collapse