SQL Query ISNULL-CodePudding

Let's assume I have four fields A,B,C,D I need SQL query to show results only when two or more fields are not null

CodePudding user response：

For Postgres you can use:

select *
from the_table
where num_nonnulls(a,b,c,d) >= 2;

CodePudding user response：

If you want ANSI SQL which would work with any SQL database, you would need to use the case statement. It gets a bit cumbersome because case is pretty wordy, but is relatively straightforward:

select 
    * 
from 
    mytable
where 
   case when a is null then 0 else 1 end 
   case when b is null then 0 else 1 end 
   case when c is null then 0 else 1 end 
   case when d is null then 0 else 1 end 
 >= 2;