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.
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]');
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;
id | name | status ----+-------+-------- 1 | test | f 2 | test2 | t
A quick rundown of the above query:
SELECT id, name,: Selects the columns
namefrom the table
CASE users.status: Starts the
CASEexpression with the column
statusfrom the table
WHEN 'pending' THEN false: When the value of the column
WHEN 'verified' THEN true: When the value of the column
END: End the
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.
SELECT SQL statements
If you are using MySQL, you can use the
IF clause instead of the
Here is the same query as above, but using the
SELECT id, name, IF(status = 'pending', false, true) as status FROM users;
+----+------+--------+ | 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:
IF is typically used for logic flow, where
CASE is used to return data values based on the result of a condition.
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
In case you want to learn more about SQL in general, I would recommend the following free 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.