How to write optional filters in SQL

How to write optional filters in SQL

dev quick-tip sql

published on

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/

Your SQL query might look like something like this

FROM books

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);

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


But how will this function help us?

Optional filters with the coalesce function

FROM books
  author = coalesce(?, author);

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 😎

You found an error in this post? Open a pull request.
Photo of Tim Bachmann

Hi, my name is Tim Bachmann! I'm a master graduate in computer science at University of Basel, swimmer and swim coach.

I am passionate about all things web development, swimming, personal knowledge management and much more. If you liked this or any of my posts, feel free to follow me.

0 Comments and Interactions

Leave a comment or interact with this page via WebMention

Built with SvelteKit and hosted on GitHub Pages.

View this website on GitHub!

Other pages