Reduce the use of AND operator in Oracle while comparing multiple NULL values in different columns

Question

I want to compare multiple column's NULL values. eg. Assume I have 3 columns in my table from which I have to find out NOT NULL values. I am using following code :

select * from table1 where 
column1 is not null 
and column2 is not null
and column3 is not null

I don't want to use this code as it uses "and" multiple times if columns goes on increasing. Anybody have option to this in Oracle 11g ?

Answer 1

I agree with the comment that your query is fine as is. If the columns that you are checking are all of a numeric variety then you can use Oracle's behavior with null values to your advantage to shorten the query like this:

select * from table 1
where 
  (
    column1 
    + column2
    + column3
  ) is not null;

If any of the listed columns are null then the sum will be null also. Unfortunately, if you have strings instead--null strings concatenate just fine, so the same approach doesn't work with them.