Oracle SQL: ANY + IS NULL

Question

I need to check several fields with NULL. Of course I can do it with OR.

SELECT * FROM table WHERE f1 IS NULL OR f2 IS NULL OR f3 IS NULL

But there is ANY which works fine with logical operations.

Is it possible to do something like that in Oracle's SQL?

SELECT * FROM table WHERE ANY (f1, f2, f3) IS NULL

I use Oracle 12c.

Answer 1

On the inverse of Tim's anser, you can use GREATEST or LEAST to look where any value is NULL

select * from dual where greatest('a',2,3,null) is null;

Answer 2

A trick in Oracle using expressions or functions:

If those are all numeric values, you could just use +:

with t(a, b, c) as (
  select 1, 2, 3 from dual union all 
  select null, 2, 3 from dual union all 
  select null, null, 3 from dual
)
select *
from t
where a + b + c is null;

It yields

A|B|C|
-|-|-|
 |2|3|
 | |3|

Also, the less readable DECODE function could be used, or GREATEST as Gary already showed:

decode(null, a, 1, b, 1, c, 1, 0) = 1;

Using MINUS

You can use (a, b, c) = ((a, b, c)) to check if none of the values is NULL, in case of which the predicate yields NULL. Unfortunately, as Oracle doesn't know BOOLEAN types, you cannot NULL check that predicate itself (and LNNVL doesn't seem to work with the above condition), but you could use MINUS:

with t(a, b, c) as (
  select 1, 2, 3 from dual union all 
  select null, 2, 3 from dual union all 
  select null, null, 3 from dual
)
select *
from t
minus
select *
from t
where (a, b, c) = ((a, b, c)); -- None of the values is NULL

This is obviously a slow solution in many cases, so not good.

Standard SQL:

It's worth mentioning that standard SQL (e.g. implemented by PostgreSQL, but not Oracle) supports null predicates on row value expressions like this:

not ((a, b, c) is not null)

The "double negative" is necessary because the truth table shows that not x is null and x is not null are not the same thing (source):

+-----------------------+-----------+---------------+---------------+-------------------+
| Expression            | R IS NULL | R IS NOT NULL | NOT R IS NULL | NOT R IS NOT NULL |
+-----------------------+-----------+---------------+---------------+-------------------+
| degree 1: null        | true      | false         | false         |  true             |
| degree 1: not null    | false     | true          | true          |  false            |
| degree > 1: all null  | true      | false         | false         |  true             |
| degree > 1: some null | false     | false         | true          |  true             |
| degree > 1: none null | false     | true          | true          |  false            |
+-----------------------+-----------+---------------+---------------+-------------------+