In the previous post I suggested a way to write a single SQL query that filters the EMPLOYEES table (as an example) by one or more of the following columns: DEPARTMENT_ID, JOB_ID, MANAGER_ID and LAST_NAME.
Here is the query from the previous post:
select * from employees where rowid in ( select rid from ( select rowid rid from employees where department_id = :department_id union all select rowid rid from employees where job_id = :job_id union all select rowid rid from employees where manager_id = :manager_id union all select rowid rid from employees where last_name = :last_name) group by rid having count(*) = nvl2(:department_id,1,0) + nvl2(:job_id,1,0) + nvl2(:manager_id,1,0) + nvl2(:last_name,1,0) );
In this query, it is assumed that at least one of the bind variables (:department_id, :job_id, :manager_id, :last_name) contains a non-null value.
If all of them contain nulls, then the result set of the query is empty. Note that in this case Oracle will not even try looking for data; as Stew Ashton commented in the previous post, the Index Range Scan will do no consistent gets for a predicate such as “column_name = :bind_variable” when :bind_variable is null (because Oracle knows that no row satisfies a “column=null” condition).
If we want to support filtering by zero or more of the columns, we should add (union) a subquery to handle the case when all the bind variables are nulls: Continue reading