2019-07-sql-optional-filters-coalesce.md

uuid	title	published	description	content_tags	date	cover_image
899fb73c-a78e-4cd9-b712-1886715b2d56	How to write optional filters in SQL	true	A simple way to filter by optional values in SQL with the COALESCE function.	SQL quick-tip dev	2019-07-11	/assets/2019-07-sql-optional-filters-coalesce.png

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

SELECT *
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 = ?";
  arguments.push(authorFilter);
}
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, 'tiim.ch', null, '@TiimB') as example;

-- Will return

example
---------
tiim.ch

But how will this function help us?

Optional filters with the coalesce function

SELECT *
FROM books
WHERE
  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 😎