## Forum Stats

• 3,752,658 Users
• 2,250,533 Discussions

Discussions

# How to "where any of(b,c,d,e,f,g,h..) is null" with SQL(not PL/SQL)

• Member Posts: 1,057 Bronze Badge
It has already been proposed but....

Where do you want to find something quicker than this
```SELECT *
FROM T
WHERE a IS NULL
OR b IS NULL
OR c IS NULL
...
...
OR z IS NULL```
Bye Alessandro
• Member Posts: 1,054
how coalesce will return the information I need which is; if any of the list members is NULL
Sorry. I'd confused with ALL.

I don't think that anything would be faster then ORs. But another example
`decode(null, a,0, b,0, c,0, ...) = 0`
for numneric:
`a+b+c+... is null`
• Member Posts: 147
edited Sep 7, 2007 4:14AM
very nice solution... 5 stars
• Member Posts: 1,329
JFF
```SQL> create table nulltest (x int, y int, z int);

Table created.

SQL> insert into nulltest values (1,2,3);

1 row created.

SQL> insert into nulltest values (1,2,null);

1 row created.

SQL> insert into nulltest values (1, null,null);

1 row created.

SQL> insert into nulltest values (1, null,2);

1 row created.

SQL> select * from nulltest
2  where x+y+z is null;

X          Y          Z
---------- ---------- ----------
1          2
1
1                     2```
• Member Posts: 8,424 Bronze Crown
.. or still a bit shorter one when you are on 10g:
```select * from emp
where lnnvl(sys.dbms_debug_vc2coll() not member of (sys.dbms_debug_vc2coll(empno, comm, sal)))```
• Member Posts: 2,208
Based on michaels suggestion to treat the column list as a collection - an alternative to the subquery might be to use MULTISET operators to check for NULLs.
```Oracle Database 10g Express Edition Release 10.2.0.1.0 - Production
SQL> SELECT b, c, d, e, f, g, h
2  FROM   mytable
3  WHERE  sys.dbms_debug_vc2coll (NULL) MULTISET EXCEPT
4            sys.dbms_debug_vc2coll (b, c, d, e, f, g, h) IS EMPTY;
B          C          D          E          F          G          H
---------- ---------- ---------- ---------- ---------- ---------- ----------
1          1          1          1                     1          1
1                     1                     1                     1
SQL> SELECT b, c, d, e, f, g, h
2  FROM   mytable
3  WHERE  sys.dbms_debug_vc2coll (NULL) MULTISET INTERSECT
4            sys.dbms_debug_vc2coll (b, c, d, e, f, g, h) IS NOT EMPTY;
B          C          D          E          F          G          H
---------- ---------- ---------- ---------- ---------- ---------- ----------
1          1          1          1                     1          1
1                     1                     1                     1
SQL>```
• Member Posts: 5,856 Bronze Trophy
Very nice as well!

But no matter how brilliant, or how fun-to-write these solutions are, I cannot think of a valid reason in terms of performance and future maintenance, why not to write the "several or" variant.

Regards,
Rob.
• Member Posts: 1,906 Gold Trophy
```select x.*
from (
select 1 b, 2 c, 1 d, 1 e, 1 f, 1 g, 1 h from dual
union all select 1, 1, 1, 1, null, 1, 1 from dual
union all select 1, 1, 1, 1, 1, 1, 1 from dual
union all select null, null, null, null, null, null, null from dual
union all select 1, null, 1, null, 1, null, 1 from dual
) x
where  case when ( b, c, d, e, f, g, h ) = all ( ( b, c, d, e, f, g, h ) )
then 1
end is null```
Anton
• Member Posts: 2,525
edited Sep 7, 2007 5:12AM
Another option would surely be
`WHERE NULL IN (b,c,d,e,f,g,h)`
Seems the most straightforward to me (and the most often overlooked). Don't know about performance though.

EDIT: Forget this one - inlists with null members don't work. I'd taken it from my own code where I was checking for a particular value and the fields were all NOT NULL.

Message was edited by:
Dave Hemming
• Member Posts: 2,208
and the most often overlooked
Yes I wonder why that is.
This discussion has been closed.