MySQL CASE Expression?

Summary: in this tutorial, we will show you how to use MySQL CASE expression to construct conditional queries.

Introduction to MySQL CASE expression

MySQL CASE expression is a flow control structure that allows you to construct conditions inside a query such as SELECT or WHERE clause. MySQL provides you with two forms of the CASE expressions.

The following illustrates the first form of the CASE expression.

1 2 3 4 5

CASE value WHEN compare_value_1 THEN result_1 WHEN compare_value_2 THEN result_2 … ELSE result END

If the value is equal to compare_value e.g., compare_value_1, compare_value_2, etc., then the CASEexpression returns the corresponding result i.e., result_1, result_2 . In case the value does not match any compare_value, the CASE expression returns the result specified in the ELSE clause.

The second form of the CASE expression is as follows:

1 2 3 4 5

CASE WHEN condition_1 THEN result_1 WHEN condition_2 THEN result_2 … ELSE result END

In the second form, the CASE expression returns the result such as result_1, result_2, etc., if the condition is true. If all conditions are false, then the result in the ELSE part is returned. If the ELSEpart is omitted, the CASE expression returns NULL .

The CASE expression returns the result whose data type depends on the context where it is used. For example, if the CASE expression is used in string context, it returns the result as a string. If the CASEexpression is used in a numeric context, it returns the result as an integer, a decimal, or a real value.

MySQL CASE function examples

Let’s take a look at the customers table in the sample database.

Suppose you want to sort the customers by state, and if the state is NULL, you want to use the country as the sorting criterion instead. To achieve this, you can use the first form of the CASE expression as follows:

1 2 3 4 5 6 7 8

SELECT     customerName, state, country FROM     customers ORDER BY (CASE     WHEN state IS NULL THEN country     ELSE state END);

In this example, we used the CASE expression in the ORDER BY clause to determine the field, state or country, which we want to sort.

In the next example, we will use the orders table in the sample database to demonstrate the second form of the CASE expression.

If you want to see the number of sales orders by their statuses e.g., the number of shipped orders, on-hold orders, etc., you can use the second form of the CASE expression as follows:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28

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;

The result of the query is as follows:

In the SELECT statement, if the status is equal to shipped, on hold, etc., the CASE expression returns 1, otherwise, it returns 0. We used the SUM function to calculate the total of the sales orders for each status.

In this tutorial, we have shown you how to use the MySQL CASE expression in SELECT statements to construct conditional queries.