Summary: in this tutorial, you will learn how to use the MySQL CASE
expression to add if-else logic to queries.
Introduction to MySQL CASE
expression
MySQL CASE
expression is a control flow structure that allows you to add if-else logic to a query. Generally speaking, you can use the CASE
expression anywhere that allows a valid expression e.g., SELECT
, WHERE
and ORDER BY
clauses.
The CASE
expression has two forms: simple CASE
and searched CASE
.
Note that MySQL has a CASE
statement that you can use only in stored programs such as stored procedures, stored functions, events and triggers, which is not the CASE
expression covered in this tutorial.
Simple CASE
expression
The following illustrates the syntax of a simple CASE
expression:
Code language: SQL [Structured Query Language] [sql]
CASE value WHEN value1 THEN result1 WHEN value2 THEN result2 … [ELSE else_result] END
In this syntax, CASE
matches the value with the value1
, value2
, etc., for
equality and return the corresponding result1
, result2
,… If the value
does not equal to any value1
, value2
, … CASE
returns the result in the ELSE
clause if the ELSE
clause is specified.
The CASE
compares the value
with values
in the WHEN
clauses for equality, you cannot use it with NULL
because NULL = NULL
returns false.
Searched CASE
expression
The following shows the syntax of a searched CASE
expression:
Code language: SQL [Structured Query Language] [sql]
CASE WHEN expression1 THEN result1 WHEN expression2 THEN result2 … [ELSE else_result] END
In this
syntax, the CASE
evaluates expressions specified in the WHEN
clauses. If an expression evaluates to true. CASE returns the corresponding result in the THEN
clause. Otherwise, it returns the result specified in the ELSE
clause. In case the ELSE
clause is not available, then the CASE
expression returns NULL
.
The CASE
expression returns a result whose data type depends on the
context where it is used. For example, if the CASE
expression is used in the character string context, it returns the result as a character string. If the CASE
expression is used in a numeric context, it returns the result as an integer, a decimal, or a real value.
MySQL CASE
expression examples
A] Using CASE
expression in the SELECT
clause example
See the following orders
and customers
tables:
The following statement returns the customers and their orders:
Code language: SQL [Structured Query Language] [sql]
SELECT customerName, COUNT[*] orderCount FROM orders INNER JOIN customers USING [customerNumber] GROUP BY customerName ORDER BY COUNT[*];
This example uses the CASE
expression in the SELECT
clause to return the type of customers based on the number of orders that customers ordered:
Code language: SQL [Structured Query Language] [sql]
WITH cte AS [ SELECT customerName, COUNT[*] orderCount FROM orders INNER JOIN customers USING [customerNumber] GROUP BY customerName ] SELECT customerName, orderCount, CASE orderCount WHEN 1 THEN 'One-time Customer' WHEN 2 THEN 'Repeated Customer' WHEN 3 THEN 'Frequent Customer' ELSE 'Loyal Customer' end customerType FROM cte ORDER BY customerName;
B] Using CASE
expression in the ORDER BY
clause example
The following example uses the CASE
expression to sort customers by states if the state
is not NULL
, or sort the country in case the state is NULL
:
Code language: SQL [Structured Query Language] [sql]
SELECT customerName, state, country FROM customers ORDER BY [ CASE WHEN state IS NULL THEN country ELSE state END];
Try It Out
C] Using
CASE
expression with an aggregate function example
The following example uses the CASE
expression with the SUM[]
function to calculate the total of sales orders by order status:
Code language: SQL [Structured Query Language] [sql]
SELECT SUM[CASE WHEN status = 'Shipped' THEN 1 ELSE 0 END] AS 'Shipped', SUM[CASE WHEN status = 'On Hold' THEN 1 ELSE 0 END] AS 'On Hold', SUM[CASE WHEN status = 'In Process' THEN 1 ELSE 0 END] AS 'In Process', SUM[CASE WHEN status = 'Resolved' THEN 1 ELSE 0 END] AS 'Resolved', SUM[CASE WHEN status = 'Cancelled' THEN 1 ELSE 0 END] AS 'Cancelled', SUM[CASE WHEN status = 'Disputed' THEN 1 ELSE 0 END] AS 'Disputed', COUNT[*] AS Total FROM orders;
Try It Out
Here is the output:
How it works.
- First, the
CASE
statement returns 1 if the status equals the corresponding status such as Shipped, on hold, in Process, Cancelled, Disputed and zero otherwise. - Second, the
SUM[]
function returns the total number of orders per order status.
In this tutorial, you have learned how to use the MySQL CASE
expression to add if-else logic to the queries.
Was this tutorial helpful?