Filtering rows from DB2 table with multiple combination of search values

Question

Say I have a DB2 table consisting of 5 columns. How should I filter rows based on any combination of a known value from each of this columns?

If I have a search value for each of the 5 columns, and I want to return rows that matches all of them, I can can simply filter it by

 SELECT * FROM TABLE
  WHERE A = value1 and B = value2 and C = value3 and D = value 4 and E = value 5

But what if I only have search values from say columns A and B, or A and C, or A B and C, etc. How can I make a single WHERE clause that can filter returned rows based on the available search values I have?

How can I implement the idea of the more search criteria you enter the lesser rows you'll return in one WHERE clause?

Answer 1

It would depend on the specific database parser, but if you have five columns a, b, c, d, e and five potentially null parameters p0, p1, p2, p3, p4 the query could take the form:

select *
from t
where (a = :p0 or :p0 is null)
  and (b = :p1 or :p1 is null)
  and (c = :p2 or :p2 is null)
  and (d = :p3 or :p3 is null)
  and (e = :p4 or :p4 is null)

Using JDBC the parameterized query could look like:

select *
from t
where (a = ? or ? is null)
  and (b = ? or ? is null)
  and (c = ? or ? is null)
  and (d = ? or ? is null)
  and (e = ? or ? is null)

Keep in mind that a query like this one offers few opportunities for optimization. Most likely the engine will need to scan the whole heap each time.