Conditional logic, CASE statement

SQL, like many programming languages, lets you write conditional logic so that a query's behavior depends on the values of certain columns or expressions. In this article, we'll look at how this is implemented in SQL using the CASE operator.

Concept of conditional logic

The concept of conditional logic means that the program has several execution paths depending on some condition.

The Schedule database has a Student table with a birthday field that reflects the student's date of birth. Let's say in the selection, it is necessary to display not the date of birth itself, but the text value "Adult" or "Minor", depending on whether the student is 18 or older. This is an example of conditional logic, in which either one value or another should be displayed depending on the specific condition.

The implementation of such a query using CASE might look like this:

MySQL 8.4
SELECT first_name, last_name,
CASE
  WHEN TIMESTAMPDIFF(YEAR, birthday, NOW()) >= 18 THEN 'Adult'
  ELSE 'Minor'
END AS status
FROM Student

MySQL 8.4
SELECT first_name, last_name,
CASE
  WHEN EXTRACT(YEAR FROM AGE(NOW(), birthday)) >= 18 THEN 'Adult'
  ELSE 'Minor'
END AS status
FROM Student

CASE search expression syntax

CASE
    WHEN condition THEN result
    [WHEN ...]
    [ELSE default_result]
END

The WHEN branches are checked in order: if the condition of the first branch is true, the CASE expression returns its result, otherwise the next branch is checked and so on.

Example

Let's take the CASE operator to implement the stages of schooling.

MySQL 8.4
SELECT name,
CASE
  WHEN SUBSTRING(name, 1, INSTR(name, ' ')) IN (10, 11) THEN 'High School'
  WHEN SUBSTRING(name, 1, INSTR(name, ' ')) IN (5, 6, 7, 8, 9) THEN 'Middle school'
  ELSE 'Elementary school'
END AS stage
FROM Class

MySQL 8.4
SELECT name,
CASE
  WHEN SUBSTRING(name, 1, POSITION(' ' IN name) - 1) IN ('10', '11') THEN 'High School'
  WHEN SUBSTRING(name, 1, POSITION(' ' IN name) - 1) IN ('5', '6', '7', '8', '9') THEN 'Middle school'
  ELSE 'Elementary school'
END AS stage
FROM Class

• Next, we check for occurrences of this class number in the list of classes related to "High School" and "Middle school".
• If the class number doesn't match 5-11, we output "Elementary school".

Syntax of a simple CASE expression

The CASE operator also has a simpler syntax, which is similar to the CASE search expression, but is a little less flexible. Here is its syntax:

CASE value
    WHEN compare_value THEN result
    [WHEN ...]
    [ELSE default_result]
END

In this syntax, the value in CASE is compared in turn with the values in WHEN, and if one matches, the value following THEN is returned.

Using this syntax, we can rewrite our previous example:

MySQL 8.4
SELECT name,
CASE SUBSTRING(name, 1, INSTR(name, ' '))
  WHEN 11 THEN 'High School'
  WHEN 10 THEN 'High School'
  WHEN 9 THEN 'Middle school'
  WHEN 8 THEN 'Middle school'
  WHEN 7 THEN 'Middle school'
  WHEN 6 THEN 'Middle school'
  WHEN 5 THEN 'Middle school'
  ELSE 'Elementary school'
END AS stage
FROM Class

MySQL 8.4
SELECT name,
CASE SUBSTRING(name, 1, POSITION(' ' IN name) - 1)
  WHEN '11' THEN 'High School'
  WHEN '10' THEN 'High School'
  WHEN '9' THEN 'Middle school'
  WHEN '8' THEN 'Middle school'
  WHEN '7' THEN 'Middle school'
  WHEN '6' THEN 'Middle school'
  WHEN '5' THEN 'Middle school'
  ELSE 'Elementary school'
END AS stage
FROM Class

Test yourself

What value will the CASE operator return in this case?

MySQL 8.4
CASE 2
  WHEN 0 THEN 'Zero'
  WHEN 1 THEN 'One'
  ELSE 'Many'
END