Comparing columns containing null values

Question

Comparing columns containing null values

Viewing 11 posts - 16 through 25 (of 25 total)

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

Scott Coleman One Orange Chip Points: 27452 More actions · Answer 1

The syntactic flexibility added by SET ANSI_NULLS OFF is very limited. This setting apparently only affects col = NULL or col = @var (where @var is NULL). I could not find any context where col1 = col2 was true when both were null.

Try these test queries:

set

nocount on

set ansi_nulls off

declare @val int

create table #test (id int identity not null, col int null)

insert into #test values (NULL)

insert into #test values (1)

insert into #test values (2)

select * from #test where col = @val

select * from #test where col <> @val

select a.id, b.id, a.col, b.col from #test a join #test b on a.col = b.col

select a.id, b.id, a.col, b.col from #test a join #test b on a.col <> b.col

select a.id, b.id, a.col, b.col,

case when a.col = @val then 'Column = NULL' else 'Column <> NULL' end,

case when a.col = b.col then 'Columns are equal' else 'Columns not equal' end

from #test a join #test b on a.id = b.id

drop table #test

Jesper-244176 SSCertifiable Points: 7082 More actions · Answer 2

Good point, I didn't know that (but I guess I shouldn't be too surprised by now).

Kenneth Wilhelmsson SSC-Dedicated Points: 30043 More actions · Answer 3

This behaviour is a documented property of the count() function - it's not quite what Jesper asked for.

/Kenneth

Jesper-244176 SSCertifiable Points: 7082 More actions · Answer 4

Exactly my conclusion. I think that the contents of your initial paragraph ought to be included in BOL (at least, I cannot find it)

SN SSC Rookie Points: 31 More actions · Answer 5

This is documented in BOL for null values;

"Null values cannot be used for information that is required to distinguish one row in a table from another row in a table"

Based on my understanding, this also implies that null values cannot be used to distinguish one col in a table from another col in a table. Please correct me if I am wrong.

Sergiy SSC Guru Points: 110206 More actions · Answer 6

Guys, you just think wrong way.

NULL is not actually a value, it's ABSENCE OF VALUE.

It's designed to display the fact that there is nothing, and you must use it right way.

You cannot compare something you don't have.

Can you compare who's estate in Switzerland is bigger - yours or mine?

You can only eatablish the fact that you don't have or I don't have or me and you both don't have. Nothing more.

_____________
Code for TallyGenerator

Roy Rogers SSCertifiable Points: 7712 More actions · Answer 7

/*

Sergiy I would say every one knows what you say. But the confusion is how they interpret set ansii nulls off.

If ansii nuls is off When comapring the column values with another column value null <> null when comparing variables null = null. They want that to be explicitly stated in BOL.

see this value of @a is taken from table itself.

*/

set ansi_nulls off

go

create table #temp(a int null)

go

insert into #temp select null

declare @a int

select @a = a from #temp

-- select 1 returns no rows

select * from #temp where a = a

-- select 1 returns one row

select * from #temp where a = @a

-- select 2 returns one row

select * from #temp where a = null

drop table #temp

go

Regards,
gova

Scott Coleman One Orange Chip Points: 27452 More actions · Answer 8

I think it's spelled out better in the SQL 2005 documentation:

"SET ANSI_NULLS ON only affects a comparison if one of the operands of the comparison is either a variable that is NULL or a literal NULL. If both sides of the comparison are columns or compound expressions the setting does not affect the comparison."

mpv-1060508 SSC Eights! Points: 885 More actions · Answer 9

I am trying to compare two columns to find the ones that are different.

If I use the following, I only get item4. I would expect to get item2, item3 and item4.

drop table #tempa

create table #tempa(id varchar(10),desc1 varchar(10))

insert into #tempa values('item1',NULL)

insert into #tempa values('item2',NULL)

insert into #tempa values('item3','three')

insert into #tempa values('item4','four')

insert into #tempa values('item5','five')

--

drop table #tempb

create table #tempb(id varchar(10),desc1 varchar(10))

insert into #tempb values('item1',NULL)

insert into #tempb values('item2','two')

insert into #tempb values('item3',NULL)

insert into #tempb values('item4','four4')

insert into #tempb values('item5','five')

--

select a.id, a.desc1, b.desc1

from #tempa a, #tempb b

where a.id = b.id

and a.desc1 b.desc1

What do I need to do to return all the different rows (ie, item2, item3 and item4).

Thanks... mpv

SN SSC Rookie Points: 31 More actions · Answer 10

When doing a comparison that have null values, the best way is to convert it. Keeping the null values in aggration will always cause issues.

select a.id, a.desc1, b.desc1

from #tempa a, #tempb b

where a.id = b.id

and isnull(a.desc1,'') isnull(b.desc1, '')

mpv-1060508 SSC Eights! Points: 885 More actions · Answer 11

Thanks for your reply. I thought I deleted this post as I realized I should have posted it in SS2005.

I was told to use COALESCE and it worked for me.

Here is the solution:

Topic756730-338-1