Forum Stats

  • 3,752,637 Users
  • 2,250,529 Discussions
  • 7,867,903 Comments

Discussions

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
  • 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
  • 17777
    17777 Member Posts: 147
    edited Sep 7, 2007 4:14AM
    very nice solution... 5 stars
  • 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
  • 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)))
  • 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>
  • 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.
  • ascheffer
    ascheffer 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
  • 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
  • 94799
    94799 Member Posts: 2,208
    and the most often overlooked
    Yes I wonder why that is.
This discussion has been closed.