DEV Community

Tim Bachmann
Tim Bachmann

Posted on

How to write optional filters in SQL

The problem

Let's say you have a rest API with the following endpoint that returns all of the books in your database:

GET /book/
Enter fullscreen mode Exit fullscreen mode

Your SQL query might look like something like this

FROM books
Enter fullscreen mode Exit fullscreen mode

Sometimes you want to only list books, for example, from a specific author. How do we do this in SQL?

Naive solution: String concatenation ✂

One way would be to concatenate your sql query something like this:

const arguments = [];
const queryString = 'SELECT * FROM books WHERE true';
if ( authorFilter != null) {
  queryString  += 'AND author = ?';
db.query(queryString, arguments); 
Enter fullscreen mode Exit fullscreen mode

I'm not much of a fan of manually concatenating strings.

The coalesce function 🌟

Most Databases have the function coalesce which accepts a variable amount of arguments and returns the first argument that is not null.

-- Examle
SELECT coalesce(null, null, '', null, '@TiimB') as example;

-- Will return 

Enter fullscreen mode Exit fullscreen mode

But how will this function help us?

Optional filters with the coalesce function

FROM books
  author = coalesce(?, author);
Enter fullscreen mode Exit fullscreen mode

If the filter value is null the coalesce expression will resolve to author
and the comparison author = author will be true.

If on the other hand the value is set for example to Shakespeare then the author will be compared to Shakespeare.

I came across this way to implement optional filters only recently. If you have a more idiomatic way to do this let me know please ✨

If you liked this post please follow me on here or on Twitter under @TiimB 😎

Top comments (0)