Adding optional conditions to your queries

The NVL function is very useful in a lot of different situations and is probably one of the most widely used among Oracle developers.

One of the ways we can use it is to write queries that have ‘optional’ conditions. How so? I mean, queries that sometimes might need to be run with a condition, and sometimes you want them to run without that condition.

Let me explain it with an example: Say I have a query that gets me the list of employees that earn more than $10K a month and work in a specific department of the company. The query would look something like this:

FROM employees
WHERE salary > 10000
AND department_id = 'IT';

But it turns out that sometimes I need to get the same list of employees that earn more than $10K regardless of the department they work in, what should I do?

Pretty easy, right? I just need to remove the last condition from my WHERE clause, and that’s it. But, what happens if this query is part of an application? Since I can obviously not be modifying the query in the application like I would when I run it on SQL Developer, my best bet would probably be having (or dynamically constructing at runtime) 2 similar queries, one with the condition and one without it, and add logic to the application to execute one or the other according to the circumstances. But, what if there are 5 or more ‘optional’ conditions? Should I write 5 different queries, and a lot more to cover all the possible combinations? Things can get complicated…

This situation can easily be handled with the help of the NVL function. I just need to add the ‘optional’ condition, but instead of comparing the column directly to some literal value, I will compare it to a call to the NVL of the optional parameter.

In our example, the query would be something like this:

FROM employees
WHERE salary > 10000
AND department_id = NVL(:department,department_id);

And voilá, if we enter an actual department_id, the query will filter the results accordingly, but if we don’t enter any department_id, the query will bring results from all departments. Why? Because the second parameter given to the NVL function is the same database column we are applying the condition to, so when the parameter is null we end up comparing department_id to itself, which will always be true, and thus the condition is practically nullified.

Now, this seems pretty handy, but it is actually not very efficient. A slightly more efficient way to achieve the same thing would be having a condition that explicitly checks if the bind variable is null, like this:

FROM employees
WHERE salary > 10000
AND ( 
      department_id = :department
      or :department is null

The query optimizer will usually do a better job optimizing this version of the query.

So, there you have it. A pretty easy way to have parameterized queries with optional conditions, which can be useful for application and report developers.

Note about performance: As mentioned before, the NVL approach is not the best one if performance is an important factor. Using functions on the search conditions can make the database engine not to use indexes even if appropriate ones exist, and in some cases will also result in additional filters generated by the optimizer. The second version is better, but still not very efficient because that kind of condition will usually make the database read the whole table because of the condition that checks for NULL, so, if you are concerned about performance, writing (or dynamically constructing) different specific queries would probably be the best option.

Opt In Image
Don't want to miss any Oracle SQL tips?

Subscribe to be informed about new posts, tips and more awesome things.



I've been working with Oracle databases on a daily basis for more than 10 years.

Leave a Reply