MySQL CASE Expression

 MySQL CASE Expression

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 the 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., SELECTSELECT, WHERE and ORDER BY clauses.

The CASE the expression has two forms: simple CASE and searched CASE.

Note that MySQL has a CASE a 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:

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 a 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:

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 a 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 the expression is used in the character string context, it returns the result as a character string. If the CASE the 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:

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:

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:

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

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:

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;

Here is the output:

How it works.

• First, the CASE the 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() the 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.