I want a NULL to equal a 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)

I want a NULL to equal a NULL

Author

Topic

emydura
Starting Member

6 Posts

Posted - 2007-10-04 : 23:24:36

When comparing variables, I need to pull out those records where two fields match, including NULL values. But in SQL a NULL can't match a NULL. I thought I may be able to fix the problem by setting ANSI_NULLS to off, but this only helps when trying to select those values from an individual field that are NULL, example - select * from t1 where VARa=NULL.

Below is some sample code. If this was to work as I want it to, the final select statement would pull out the data from the table. Currently it pulls out nothing.

Is there any simple solution to my problem?

CREATE TABLE t1 (VARa varchar, VARb varchar)
INSERT INTO t1 values (null,null)
GO

SET ANSI_NULLS Off

select * from t1 where VARa=VARb

Thanks

David

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-10-04 : 23:31:11

where VARa is null
and VARb is null

KH
[spoiler]Time is always against us[/spoiler]

emydura
Starting Member

6 Posts

Posted - 2007-10-04 : 23:51:12

The example I gave you was very simplistic in order for it to be easily understood. My real world problem is much more complex as I'm trying to match numerous variables at the same time. Some of these have NULL values and others don't. So unfortunately the solution of

where VARa is null and VARb is null

won't cut it.

David

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-10-04 : 23:56:26

[code]where VARa = VARb
or (VARa is null and and VARb is null)[/code]

KH
[spoiler]Time is always against us[/spoiler]

emydura
Starting Member

6 Posts

Posted - 2007-10-05 : 00:34:04

quote:
Originally posted by khtan

where VARa = VARb
or (VARa is null and and VARb is null)

KH
[spoiler]Time is always against us[/spoiler]

That works for simple solutions but not for anything complex. That is what I have done in the past but I'm now trying to avoid this. Here is an example of the code I require it for. As you can see it is just a maze of and/or statements. And I now need to add another 2 variables to this matching process which will just make it exponentially more complex. This would be all so simple if a NULL could equal a NULL.

and a.highestedqualification=c.highestedqualification
and ((a.yearcompleted=c.yearcompleted and a.Educationcountry = c.country) or
(a.yearcompleted=c.yearcompleted and a.Educationcountry is null and c.country is null) or
(a.yearcompleted is null and c.yearcompleted is null and a.Educationcountry = c.country)
or ( a.yearcompleted is null and c.yearcompleted is not null and a.Educationcountry = c.country)
or (a.yearcompleted=c.yearcompleted and a.Educationcountry is null and c.country is not null)
or (a.yearcompleted is null and a.educationcountry is null)
)

emydura
Starting Member

6 Posts

Posted - 2007-10-05 : 01:08:40

With the help of a colleague I have a solution. The ISNULL converts the NULL values to 0 which then enable you to match the variables irrespective of whether the values are NULL or not. Works like a charm.

select * from t1 where isnull(VARa,0)=isnull(VARb,0)

David

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2007-10-05 : 01:55:13

that will also evaluate to true if VARa is null and VARb is zero, or vice versa. is that acceptable?

I would say that NULL != 0 but you have equated them above.

elsasoft.org

emydura
Starting Member

6 Posts

Posted - 2007-10-05 : 02:15:58

quote:
Originally posted by jezemine

that will also evaluate to true if VARa is null and VARb is zero, or vice versa. is that acceptable?

I would say that NULL != 0 but you have equated them above.

elsasoft.org

In the data I'm comparing, 0 is not a valid value for the variables in question, so the scenario you talk about can't happen. But you make a good point. It is important that the dummy values you use (0 or whatever) cannot be a value in the actual variables you are comparing. Otherwise you will incorrectly match a NULL to a 0 data value.

Thanks

David

Kristen
Test

22859 Posts

Posted - 2007-10-05 : 11:13:18

Note also that

where isnull(VARa,0)=isnull(VARb,0)

will not use any index on VARa nor VARb, whereas

where VARa = VARb
or (VARa is null and and VARb is null)

may use an available index.

I also recommend that (if you are just starting to use ISNULL) you use COALESCE instead which is standards compliant (and has some slightly less annoying foibles, except for its more verbose name!)

Kristen

emydura
Starting Member

6 Posts

Posted - 2007-10-05 : 15:40:36

quote:
Originally posted by Kristen

Note also that

where isnull(VARa,0)=isnull(VARb,0)

will not use any index on VARa nor VARb, whereas

where VARa = VARb
or (VARa is null and and VARb is null)

may use an available index.

I also recommend that (if you are just starting to use ISNULL) you use COALESCE instead which is standards compliant (and has some slightly less annoying foibles, except for its more verbose name!)

Kristen

Thanks Kristen. Will do.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources