Comparing columns containing null values

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

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

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

    /Kenneth

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

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

     

     

  • 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

  • /*

    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

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

     

  • 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

  • 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, '')

  • 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

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

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