Conditional functions

When you work with data, it is essential to know how to get new information from it and how to make data human-readable. In this topic, we'll discuss how to use CASE and COALESCE statements to achieve these goals.

CASE expression

The CASE expression helps us get different values based on a particular condition. Let's take a look at the simple table personswith the column name VARCHAR(40) and age INT:

As you can see, in this table, we have adults and minors. With the CASE expression, we can transform data from the age column and output 'adult' if a person is 18 or older and 'minor' otherwise:

SELECT name, CASE 
             WHEN age >= 18 THEN 'adult' 
             ELSE 'minor' 
             END AS age_status
FROM persons; 

The result of this query looks like this:

In the example above, we check if each row fulfills the condition to produce values for the new column age_status. However, the CASE expression has an alternative syntax that allows us to check if the value in the column (also called selector in this case) is equal to the given value or not. In table clients below, we have the city column:

We will use this column to add information about the country to the result set:

SELECT 
    client_id, 
    name, 
    CASE city 
        WHEN 'New-York' THEN 'USA'
        WHEN 'London' THEN 'UK' 
        WHEN 'Moscow' THEN 'Russia'
    END AS country 
FROM
    clients;

In this query with the CASE expression, we don't have the ELSE part. As 'Paris' does not have a suitable value, the CASE will return NULL for it. After executing the query, we will get the following result:

The CASE statement can be used in different parts of the SQL queries and even as an argument of an aggregate function.

Notice that the CASE statement returns the first result that fulfills the suitable condition or value, so if we have two or more suitable alternative results for one value, the CASE statement will only return the first one.

COALESCE function

The COALESCE function receives a list of values and returns the first non-null value in this list. If all the values in the list are NULL, the COALESCE statement will return NULL.

Let's have a look at the following simple example:

SELECT COALESCE(NULL, NULL, 'Alice', NULL, 1) AS first_non_null;

The query will return just one row:

As you can see, there can be different types of values in the list.

The COALESCE function can be used to transform NULL values into something more practical. In the table employees, the column department contains a NULL value:

We can select all non-null values from the department column and add 'No department' to the result set instead of NULL:

SELECT 
    name, 
    COALESCE(department, 'No department') AS department 
FROM 
    employees; 

The result of the query execution will look as follows:

Conclusion

The CASE expression evaluates each row against a condition and returns the first match. Here's the template for the CASE expression with conditions:

CASE 
  WHEN condition_1 THEN result_1
  WHEN condition_2 THEN result_2
  .... 
  WHEN condition_n THEN result_n 
  [ELSE default_result]
END 

The template for the CASE expression with selector, the value of which is used to select one of the several alternatives, looks as follows:

CASE selector
  WHEN value_1 THEN result_1
  WHEN value_2 THEN result_2
  .... 
  WHEN value_n THEN result_n 
  [ELSE default_result]
END 

The COALESCE function returns the first non-null value from the list or NULL if there are no such values:

COALESCE(value_1,value_2, ..., value_n)

Now it's time to move on to the exercises to see how well you understood today's topic. Good luck!