# not like?

• 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]

• 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! 🙂

• 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%'

• 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

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

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

• good question...

very good discussion by experties 🙂

_______________________________________________________________