Dealing with NULLs - 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

SQL Server Development (2000)

Dealing with NULLs

Author

Topic

billsox
Yak Posting Veteran

74 Posts

Posted - 2002-03-19 : 16:38:24

Hi,

Can someone tell me how I can get the following SQL statement batch to return 4 rows instead of one?

DECLARE @MyTable TABLE (field1 INT NULL, field2 INT NULL)

INSERT INTO @MyTABLE VALUES (1, 1)
INSERT INTO @MyTABLE VALUES (NULL, 1)
INSERT INTO @MyTABLE VALUES (2, NULL)
INSERT INTO @MyTABLE VALUES (NULL, NULL)
INSERT INTO @MyTABLE VALUES (0, NULL)
INSERT INTO @MyTABLE VALUES (3, 4)

SELECT * FROM @MyTable WHERE field1 <> field2
GO

I'm trying to get a recordset of all records where field1 does not equal field2 but the Nulls are complicating the issue. I've tried setting the ANSI_NULLS option to True and then to False but in either case I still only get one row returned. I realize that I could change my WHERE clause to:

WHERE ISNULL(field1, -999) <> ISNULL(field2, -999)

But I'm looking for a cleaner approach.

Any ideas? Thanks in advance.

Bill

yakoo
Constraint Violating Yak Guru

312 Posts

Posted - 2002-03-19 : 16:42:23

I think your IsNull solution is pretty clean, and it should work perfectly fine.

Jay99

468 Posts

Posted - 2002-03-19 : 16:43:04

where coalesce(field1, field2+1, 1) <> coalesce(field2, field1+ 1, 2)

so clean, you could eat off it . . .

Jay
<O>

JamesT
Yak Posting Veteran

97 Posts

Posted - 2002-03-19 : 16:44:50

What's not clean about IsNull?

IsNull(field1,-1) <> IsNull(field2,-1)

That's the great thing about this site. You get answers often times so quickly that people are posting the same answer at the same time.

Edited by - JamesT on 03/19/2002 16:45:57

billsox
Yak Posting Veteran

74 Posts

Posted - 2002-03-19 : 16:46:57

quote:

I think your IsNull solution is pretty clean, and it should work perfectly fine.

It would work in most cases but not in the case where, for example, Field1 = Null and Field2 = -999. In that case, SQL Server would convert the Null in Field1 to -999 and consider it equal to the -999 in Field2. I'd really like a cleaner solution that would work in all cases. Thanks.

Bill

byrmol
Shed Building SQL Farmer

1591 Posts

Posted - 2002-03-19 : 16:49:33

Bill,

Why not use a default instead of a NULL?

How about using -1 instead of NULL?

DavidM

Tomorrow is the same day as Today was the day before.

Jay99

468 Posts

Posted - 2002-03-19 : 16:51:51

quote:

I'd really like a cleaner solution that would work in all cases.

I still like Jay99's solution above . . . it will work in all cases.

Jay
<O>

billsox
Yak Posting Veteran

74 Posts

Posted - 2002-03-19 : 16:55:17

quote:

where coalesce(field1, field2+1, 1) <> coalesce(field2, field1+ 1, 2)

so clean, you could eat off it . . .

Success!!! I love the solution. Thanks!

Bill

JamesT
Yak Posting Veteran

97 Posts

Posted - 2002-03-19 : 16:55:32

But the total rows returned should be 4 correct? Considering that NULL and NULL are the same then

where coalesce(field1, field2+1, 1) <> coalesce(field2, field1+ 1, 2)

Would return 5 rows.

If you change it so:

SELECT * FROM @MyTable WHERE coalesce(field1, field2+1, 1) <> coalesce(field2, field1+ 1, 1)

You get the four rows expected.

Jay99

468 Posts

Posted - 2002-03-19 : 17:02:19

quote:

But the total rows returned should be 4 correct? Considering that NULL and NULL are the same then

where coalesce(field1, field2+1, 1) <> coalesce(field2, field1+ 1, 2)

Would return 5 rows.

If you change it so:

SELECT * FROM @MyTable WHERE coalesce(field1, field2+1, 1) <> coalesce(field2, field1+ 1, 1)

You get the four rows expected.

NULL does not equal NULL . . . (that's why I had the NULL,NULL case resolve to 1<>2. I actually thought that all the way through) . . . don't believe me?



if null = null
begin
    print 'Jay99 is wrong'
end
else
begin
    print 'JamesT is wrong'
end

Jay
<O>

byrmol
Shed Building SQL Farmer

1591 Posts

Posted - 2002-03-19 : 17:08:01

My solution is better than yours!

DavidM

Tomorrow is the same day as Today was the day before.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-03-19 : 17:10:34

quote:
Considering that NULL and NULL are the same...

A-HA! The problem is that 2 NULLs are NOT equal to each other. Null is a placeholder for a missing value, you really can't compare two missing values to each other.

The best example I can think of is a table where you have name, address, and hair color. If two people have a NULL address, does that mean they live in the same house? If they have NULL in the hair color column, does that mean they have the same color hair? That's really why you can't consider Nulls to be equivalent. Putting the SET ANSI NULLS setting on really doesn't solve that problem, it just masks it.

JamesT
Yak Posting Veteran

97 Posts

Posted - 2002-03-19 : 17:13:18

Clearly from your code sample you are correct. I agree with that. The issue that I have is the following:

DECLARE @MyTable TABLE (field1 INT NULL, field2 INT NULL)

INSERT INTO @MyTABLE VALUES (1, 1)
INSERT INTO @MyTABLE VALUES (NULL, 1)
INSERT INTO @MyTABLE VALUES (2, NULL)
INSERT INTO @MyTABLE VALUES (NULL, NULL)
INSERT INTO @MyTABLE VALUES (0, NULL)
INSERT INTO @MyTABLE VALUES (3, 4)

SELECT * FROM @MyTable WHERE coalesce(field1, field2+1, 1) <> coalesce(field2, field1+ 1, 1)

This yields:

Field1 Field2
NULL 1
2 NULL
0 NULL
3 4

If I change the coalesce to: coalesce(field1, field2+1, 1) <> coalesce(field2, field1+ 1, 2) (what you have) the result set is:
Field1 Field2
NULL 1
2 NULL
NULL NULL
0 NULL
3 4

The resolution of NULLs is always a funky thing but the original post indicated that the result set should be four rows. I am making an assumption here <insert assumption pun> but I believe that the NULL NULL combination would be excluded from the result set because they are "equal".

If I am wrong I reserve the right to beg for mercy and buy the first round.

setbasedisthetruepath
Used SQL Salesman

992 Posts

Posted - 2002-03-19 : 17:29:21

NULLs trip up developers sometimes because there is no analogous concept in most application languages.

Jay99 is correct - NULL does not equal NULL. NULL represents the absence of a value rather than a "sentinel" value. The ANSI standard for dealing with NULLs specifies that all equality operators against NULLs always return false.

That is:
NULL = NULL false
NULL != NULL false
NULL > NULL false
...

When you have NULL in a table you are signifying that a column simply has no value at all, perhaps because the value is not yet known or it is not applicable to the particular row. Comparing that to something doesn't really make any sense.

setBasedIsTheTruepath
<O>

jackstow
Posting Yak Master

160 Posts

Posted - 2002-03-20 : 04:14:36

This is a good example then for the concept of never allowing NULLs in your table if you can possibly avoid it - it really makes it easier in the front end coding of ASP, PHP, .NET, whatever to not have to worry about checking for NULLs.

AjarnMark
SQL Slashing Gunting Master

3246 Posts

Posted - 2002-03-20 : 13:48:56

quote:
If they have NULL in the hair color column, does that mean they have the same color hair?

No, I thik it means they are bald.

------------------------
GENERAL-ly speaking...

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-03-20 : 13:51:46

quote:

quote:
If they have NULL in the hair color column, does that mean they have the same color hair?

No, I thik it means they are bald.

There are other places to check

eeeeeeeeeeeeeeeeeeeeeeeeeeeeewwwwwwwwwwwwwwwWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWW!

Jay99

468 Posts

Posted - 2002-03-20 : 13:59:07

quote:
...the original post indicated that the result set should be four rows.....

You are correct.

I love consulting!!

Jay
<O>

AjarnMark
SQL Slashing Gunting Master

3246 Posts

Posted - 2002-03-20 : 14:34:11

Nice picture Jay!

Consulting... The ONLY way to go.

------------------------
GENERAL-ly speaking...

davidpardoe
Constraint Violating Yak Guru

324 Posts

Posted - 2002-03-22 : 05:55:32

quote:

This is a good example then for the concept of never allowing NULLs in your table if you can possibly avoid it - it really makes it easier in the front end coding of ASP, PHP, .NET, whatever to not have to worry about checking for NULLs.

I disagree with this as NULL is a valid value. I always think of NULLs as "unknown" and write queries etc with this in mind. In most businesses "unknown" is a very valid concept and should be treated appropriately by any applications.

An example could be a field called age (defined as an int). If a particular product cannot be sold to those over 65 then an application would include the condition age < 65. If a customer's age is unknown then the age would be NULL and the condition would be FALSE resulting in the customer being rejected - you can't give the product if they are over 65 and if you don't know the age then they may or may not be over 65.

I have seen cases like the above where "unknown" age is held as 0, resulting in them passing the condition. There would equally be problems if you held "unknown" as say 99 - which I have also seen. If you hold it as NULL then you can always deal with it appropriately.

I think I better change my signature from The Dabbler to....

============================
Chairman of
The NULL Appreciation Society
"Keep NULLs as NULL"

jackstow
Posting Yak Master

160 Posts

Posted - 2002-03-22 : 08:09:21

Well I did say "never allowing NULLs in your table if you can possibly avoid it" - sure NULLs are a valid concept, it's just they can be a real bind if they're overused or used inappropriately.

When is the next meeting of The NULL Appreciation Society? I guess you wouldn't know as all ways of checking for the date would return FALSE..

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources