CASE - SQL if-else

Versions: PostreSQL 11

CASE operator is maybe one of the most unknown by the beginner users of SQL. Often when I see a question how to write an if-else condition in a SQL query, some people advise to write a UDF and use if-else directly inside. As you will see in this post, this solution is a little bit overkill though.

I will start this post by a short definition and syntax presentation for CASE...WHEN operator. In the next part, I will show different places where it can be used.

Some basics first

CASE..WHEN..THEN is a SQL equivalent of if-else statement known from programming languages. Therefore, you will define some predicates and at the first positive response, the action specified after THEN will be executed. Exactly like if-else statements, CASE..WHEN..THEN is executed as long as the predicates respond negatively.

The syntax for this operator is the following:

CASE
    WHEN condition1 THEN result1
    WHEN condition2 THEN result2
    ELSE result3
END;

Of course, you can have as many conditions as you want. You can also nest CASE blocks inside another CASE blocks. But you should use this operator carefully because you risk to overuse it. For instance, you shouldn't use CASE to get the first not null value of 2 fields. You have a more appropriate operator called COALESCE which does the job. Also, if you want to return a NULL if 2 attributes are equal, you can use NULLIF. Finally, you can opt for IFNULL if you want to return a default value for a nullable argument.

Examples

Let's see now where CASE operator can be used. But just before, I will create a new container with PostgreSQL 11 docker run --name wfc_case_when_test -e POSTGRES_PASSWORD=root -e POSTGRES_USER=root -e POSTGRES_DB=test_case_when -d postgres:11.

After accessing the container (docker exec -ti 28a9adf0b852 psql -d test_case_when), we can start to discover CASE operator. Below list presents some of its principal use cases (please comment if you think that something is missing):

In this blog post you can discover CASE operator which is the if-else statement in SQL. As you could see in the multiple examples of the second section, you can use it almost everywhere - from a simple aliasing expression to more advanced flattened aggregates.