not like?

Question

not like?

Viewing 7 posts - 16 through 21 (of 21 total)

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

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

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.

[p]

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'

[/p]

VM-723206 SSCrazy Points: 2964 More actions · Answer 2

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 SSC Journeyman Points: 89 More actions · Answer 3

My solution is:

declare @test-2 table(col1 int,col2 varchar(8))

insert into @test-2 values(1,'aaaa')

insert into @test-2 values(2,'delbbbb')

insert into @test-2 values(3,'delcccc')

insert into @test-2 values(4,NULL)

insert into @test-2 values(5,'dddd')

select col1 from @test-2 where COALESCE(col2,'Null') not like '%del%'

DavE E-474734 Newbie Points: 7 More actions · Answer 4

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 SSCommitted Points: 1784 More actions · Answer 5

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.

archie flockhart SSCrazy Points: 2339 More actions · Answer 6

DavE E (3/24/2009)
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

Apart from the issue of following ANSI SQL standards, when I add a clause "WHERE col1=col2" I want to be sure that the two columns contain identical data, not just that they both happen to have been left blank, or generated by a non-matched item in an outer join.

kapil_kk SSC-Insane Points: 21316 More actions · Answer 7

good question...

very good discussion by experties 🙂

_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/