plsql check at least one variable is not null inside where clause

Question

I'd like to filter columns, depending on two variables, namely (in pseudo code):

• A if columns 1 and 2 are not null, then filter on both filter1 and filter2, otherwise
• B if column 1 is not null, then do filter on filter1, otherwise
• C if column 2 is not null, then do filter on filter2

I have tried the following PL/SQL statement:

select * from table t where 
((t.column1 is not null and t.column1=filter1 and t.column2 is not null and t.column2=t.filter2) -- condition A
 or (t.column1 is not null and t.column1=filter1) -- condition B
 or (t.column2 is not null and t.column2=filter2)); -- condition C

Although I have checked that it works for each condition independently, it does not work on all three conditions. E.g.:

• if there is only condition A, that works,
• or it there are only conditions B and C, that works.
• But that doesn't for condition A, B and C altogether.

What is wrong ? Please help :)

Answer 1

try this:

select *
  from table t
 where ((t.column1 = filter1 and t.column2 = t.filter2) -- condition 1
       or (t.column1 = filter1 and t.column2 is null )  -- condition 2
       or (t.column2 = filter2 and t.column1 is null)); -- condition 3

filters value should not be null, otherwise you have to use nvl as like the following:

select *
  from table t
 where ((t.column1 = nvl(filter1,-1) and t.column2 = nvl(t.filter2, -1) ) -- condition 1
       or (t.column1 = nvl(filter1,-1) and t.column2 is null )            -- condition 2
       or (t.column2 = nvl(filter2,-1) and t.column1 is null));           -- condition 3

Answer 2

You should think the other way around, when they are null:

select *
  from table t
 where (t.column1 is null or t.column1=filter1)
       and
       (t.column2 is null or t.column2=filter2);

Consider this condition:

(t.column1 is null or t.column1=filter1)

If t.column1 is null is true, then the t.column1=filter1 is not even evaluated, since true or whatever is always true. Which means, that t.column1=filter1 is evaluated only when t.column1 is not null.

---

Let's test each situation.

column1 = null, column2 = null
The query returns all rows, since the condition is
where (true or t.column1=filter1) and (true or t.column2=filter2) which simplifies to
where true and true

column1 = null, column2 != null
The condition is where (true) and (false or t.column2=filter2) which simplifies to
where t.column2=filter2

column1 != null, column2 = null
The condition is where (false or t.column1=filter1) and (true) which simplifies to
where t.column1=filter1

column1 != null, column2 != null
The condition is where (false or t.column1=filter1) and (false or t.column2=filter2) which simplifies to
where t.column1=filter1 and t.column2=filter2