Introduction

In some cases, you might want to choose the output value based on column values directly in your SQL statement.

In this article, we will explain how to use the CASE expression in SELECT SQL statements. We will also explain how to use an alternative approach with an IF clause for MySQL.

Prerequisites

For the sake of simplicity, we will use a sample table called users with the following columns:

CREATE TABLE users (
    id INTEGER NOT NULL,
    name VARCHAR(255) NOT NULL,
    status VARCHAR(255) NOT NULL,
    email VARCHAR(255) NOT NULL,
    PRIMARY KEY (id)
);

And let's insert some data into it:

INSERT INTO users (id, name, status, email) VALUES (1, 'John', 'pending', '[email protected]');
INSERT INTO users (id, name, status, email) VALUES (2, 'Jane', 'active', '[email protected]');

CASE in SELECT SQL statements

Let's start with the CASE expression. The CASE expression is used to evaluate a condition and return a value based on the result:

SELECT id, name,
    CASE users.status
        WHEN 'pending' THEN false
        WHEN 'verified' THEN true
    END AS status
FROM users;

Output:

 id | name  | status
----+-------+--------
  1 | test  | f
  2 | test2 | t

A quick rundown of the above query:

• SELECT id, name,: Selects the columns id and name from the table users.
• CASE users.status: Starts the CASE expression with the column status from the table users.
• WHEN 'pending' THEN false: When the value of the column status is pending, return false.
• WHEN 'verified' THEN true: When the value of the column status is verified, return true.
• END: End the CASE expression.

In most cases, you will want to use the CASE expression instead of the IF clause. As the CASE expression is ANSI standard, making it portable to other databases without the need for alteration.

IF in SELECT SQL statements

If you are using MySQL, you can use the IF clause instead of the CASE expression.

Here is the same query as above, but using the IF clause:

SELECT id, name,
       IF(status = 'pending', false, true) as status
FROM users;

Output:

+----+------+--------+
| id | name | status |
+----+------+--------+
|  1 | John |      0 |
|  2 | Jane |   NULL |
+----+------+--------+

You need to keep in mind that in Postgres the IF statement is part of the default procedural language PL/pgSQL:

• PL/pgSQL Conditionals

And also IF is typically used for logic flow, where CASE is used to return data values based on the result of a condition.

Conclusion

This is all! Now you should have a better idea of how to choose the output value based on column values in a table using IF or a CASE expression.

In case you want to learn more about SQL in general, I would recommend the following free eBook:

• Introduction to SQL open-source eBook

In case you are already using SQL on daily basis, and are looking for a way to drastically reduce the latency of your data analytics, make sure to out Materialize!

Materialize is a Streaming Database for Real-time Analytics. It is a reactive database that delivers incremental view updates and it helps developers easily build with streaming data using standard SQL.