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 columnsid
andname
from the tableusers
. -
CASE users.status
: Starts theCASE
expression with the columnstatus
from the tableusers
. -
WHEN 'pending' THEN false
: When the value of the columnstatus
ispending
, returnfalse
. -
WHEN 'verified' THEN true
: When the value of the columnstatus
isverified
, returntrue
. -
END
: End theCASE
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:
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:
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.