is NULL and = NULL - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

is NULL and = NULL

Author

Topic

Christine Glew
Starting Member

7 Posts

Posted - 2003-10-29 : 07:10:55

Has anyone come across this problem? I was getting incorrect results on a simple query using 'is null' when I changed it to '= null' it worked and was much faster. The execution plans were different, the first was doing an index seek on two indexes and a hash match, the second just on the one index. I also discovered a fix (MKBA 824028) and the workaround resolved the incorrect results

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2003-10-29 : 07:50:22

"is null" and "= null" mean two different things.

Jay White
{0}

Merkin
Funky Drop Bear Fearing SQL Dude!

4970 Posts

Posted - 2003-10-29 : 07:54:09

To expand on Jay's post..

NULL isn't a value, it signifies the absence of value. Nothing can EQUAL null, but something can BE NULL. It's very Zen really

Damian

dsdeming

479 Posts

Posted - 2003-10-29 : 07:54:09

This may help demonstrate the difference:

set nocount on
create table #a( id int identity( 1, 1 ), test varchar( 20 ))
insert into #a select 'test'
insert into #a select NULL

select * from #a where test = NULL
select * from #a where test IS NULL
drop table #a

Dennis

mohdowais
Sheikh of Yak Knowledge

1456 Posts

Posted - 2003-10-30 : 01:01:05

This behaviour is affected by the ANSI_NULLS option. Try this on the above example:


SET ANSI_NULLS OFF
set nocount on
create table #a( id int identity( 1, 1 ), test varchar( 20 ))
insert into #a select 'test'
insert into #a select NULL

select * from #a where test = NULL
select * from #a where test IS NULL
drop table #a

Owais

Make it idiot proof and someone will make a better idiot

homam
Starting Member

31 Posts

Posted - 2003-10-30 : 01:25:12

Null is the blackhole of SQL! Whenver it's used in an expression, it practically gulps up the operands and only Null remains. So you should avoid using Null directly in expressions and use "is null" or the IsNull() function instead. For example:


/* Nothing will print because the expression will evaluate to null */
if null = null print 'Equal!'

/* OK */
if null is null print 'Equal!'

/* OK */
print isnull(null, 'OK')

MakeYourDaddyProud

184 Posts

Posted - 2003-10-30 : 04:46:42

I agree. I have seen many an SQL statement generate incorrect results due rookie use of "= null". Should ALWAYS be "is null". Unfortunately, nulls are the antichrist of relational architecture but serves a valid purpose for missing information or "not a value" logic.

Daniel Small MIAP
www.danielsmall.com IT Factoring

Amethystium
Aged Yak Warrior

701 Posts

Posted - 2003-10-30 : 04:50:12

What about an update though?


UPDATE myTable
SET myColumn = NULL
WHERE myColumn IS NOT NULL

How about that? IS NOT NULL and =NULL used in one statment!

__________________
Make love not war!

homam
Starting Member

31 Posts

Posted - 2003-10-30 : 07:02:14

Yeah, but that's basicallly an assignment statement with an exprssion that contains only NULL. But if you used the NULL with other operands, it will eat them all up without you knowing :)

For this contrived example, I forgot to initialize @middle_initial:


declare @first_name varchar(128), @last_name varchar(128),
@middle_initial char(1), @full_name varchar(256)

set @first_name = 'John'
set @last_name = 'Doe'
set @full_name = @first_name + ' ' + @middle_initial + ' ' + @last_name
print isnull(@full_name, 'Oops!!! Mr. Blackhole has just gobbled up the whole expression.')

I learned to initialize every single variable I use in my stored procedures and scripts because of the Blackhole effect.

For example, if you're adding up errors like this:


declare @error int
begin transaction
-- Some statement 1
set @error = @error + @@error
-- Some statement 2
set @error = @error + @@error
-- Some statement 3
set @error = @error + @@error
if @error <> 0
	rollback transaction
else
	commit transaction

The last test will fail and the transaction will be rolled back because the uninitialized @error contained NULL that gulped up all the other error values! Same thing happens when you're building a complex dynamic SQL string. If you use any NULL variable when building the string, it will be eaten up.

So I immediately initilize variables after declaration and protect with IsNull() when building dynamic SQL strings.

quote:
Originally posted by Amethystium

What about an update though?

UPDATE myTable
SET myColumn = NULL
WHERE myColumn IS NOT NULL

How about that? IS NOT NULL and =NULL used in one statment!

__________________
Make love not war!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources