Forum Stats

  • 3,750,085 Users
  • 2,250,104 Discussions
  • 7,866,773 Comments

Discussions

Categories

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

2

Comments

  • Alessandro Rossi
    Alessandro Rossi 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
    · Share on TwitterShare on Facebook
  • 121256
    121256 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
    · Share on TwitterShare on Facebook
  • 17777
    17777 Member Posts: 147
    edited Sep 7, 2007 4:14AM
    very nice solution... 5 stars
    · Share on TwitterShare on Facebook
  • 584412
    584412 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
    · Share on TwitterShare on Facebook
  • MichaelS
    MichaelS 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)))
    · Share on TwitterShare on Facebook
  • 94799
    94799 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>
    · Share on TwitterShare on Facebook
  • Rob van Wijk
    Rob van Wijk 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.
    · Share on TwitterShare on Facebook
  • ascheffer
    ascheffer Member Posts: 1,903 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
    · Share on TwitterShare on Facebook
  • 450441
    450441 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
    · Share on TwitterShare on Facebook
  • 94799
    94799 Member Posts: 2,208
    and the most often overlooked
    Yes I wonder why that is.
    · Share on TwitterShare on Facebook
This discussion has been closed.