Oracle SQL - How to build where clause with optional search parameters

Question

There are four field in the page lets say

EMPLOYEE ID
DEPT
LOCATION
UNIT:

User might enter any of the field values all are optional, if he enter EMPLOYEE ID then the query should return rows related to that EMPLOYEE ID. If he enters only LOCATION then the query should return all the employees of that location. How to write the where clause condition with optional parameters.

Answer 1

Oracle will likely build a well-optimized query if you use NVL in your predicates:

select *
  from employee
 where employee_id = nvl(:employee_id, employee_id)
   and dept = nvl(:dept, dept)
   and location = nvl(:location, location)
   and unit = nvl(:unit, unit)

The above code is mostly equivalent to LeoLozes's answer. Although his answer is more readable, in this case the cryptic version may run much faster. One important difference is that the above code will not work if the column is NULL. If you have nullable columns you'll need to use something like LeoLoze's answer, since null = null is not true.

Oracle is used to the NVL trick, and can automatically convert this static query into a dynamic query using a FILTER operation. The execution plan will have both a FULL TABLE SCAN and and INDEX RANGE SCAN, and will pick the appropriate one at run-time, depending on the value of the bind variable. See my answer here for some sample code demonstrating how this works.