If one of all fields for a record is null

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)

If one of all fields for a record is null

Author

Topic

M.E.
Aged Yak Warrior

539 Posts

Posted - 2002-05-30 : 12:04:49

create table(
a int,
b varchar(50),
c int,
d int
)

Hopefully thats good enough as an example..

I got a query that goes through

select a from table
where a is null or b is null or c is null or d is null

Now this is fine for a table with 4 columns... I got one with 42 columns and something like 800k rows. I need to selelct all records (or atleast their ID column) where 1 or more field in the row is null. Is the only way to do this using a giant 'or' where clause?

edited the post subject so it makes more sense

Edited by - M.E. on 05/30/2002 12:16:38

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-05-30 : 12:15:49

I got this to work:

SELECT * FROM myTable
WHERE Null IN (A, B, C, D, E)

Just reverse the IN list from the usual usage.

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2002-05-30 : 12:23:27

What were you using Rob? Not SQL Server, that's for sure!
"NULL IN (x,y,z)" will return UNKNOWN for all values of x,y,z.

Edited by - Arnold Fribble on 05/30/2002 12:26:00

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-05-30 : 12:26:01

Uh-huh! Actually, I tested it in Northwind:

SELECT EmployeeID, Region, ReportsTo
FROM Employees
WHERE Null IN (Region, ReportsTo)

You'll get 5 rows, 1 row will have a null ReportsTo, the other 4 will have null Regions.

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2002-05-30 : 12:28:18

I think you forgot to mention something:
SET ANSI_NULLS OFF

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-05-30 : 12:31:14

Mine were off by default, that's why I didn't mention it.

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2002-05-30 : 12:33:50

I always keep it on, which is why I didn't think it would work