{"id":3539,"date":"2015-05-25T22:34:01","date_gmt":"2015-05-26T06:34:01","guid":{"rendered":"http:\/\/www.mysqltutorial.org\/?page_id=3539"},"modified":"2023-12-28T22:00:22","modified_gmt":"2023-12-29T05:00:22","slug":"mysql-case-function","status":"publish","type":"page","link":"https:\/\/www.mysqltutorial.org\/mysql-control-flow-functions\/mysql-case-function\/","title":{"rendered":"MySQL CASE Expression"},"content":{"rendered":"\n

Summary<\/strong>: in this tutorial, you will learn how to use the MySQL CASE<\/code> expression to add if-else logic to queries.<\/p>\n\n\n\n

Introduction to MySQL CASE expression<\/h2>\n\n\n\n

MySQL CASE<\/code> expression is a control flow structure that allows you to add if-else logic to a query. Generally speaking, you can use the CASE<\/code> expression anywhere that allows a valid expression e.g., SELECT<\/a><\/code>, WHERE<\/a><\/code> and ORDER BY<\/a><\/code> clauses.<\/p>\n\n\n\n

The CASE<\/code> expression has two forms: simple CASE<\/code> and searched CASE<\/code>.<\/p>\n\n\n\n

Note that MySQL has a CASE<\/a><\/code> statement that you can use only in stored programs such as stored procedures, stored functions, events, and triggers, which is not the CASE<\/code> expression covered in this tutorial.<\/p>\n\n\n\n

Simple CASE expression<\/h3>\n\n\n\n

The following illustrates the syntax of a simple  CASE<\/code> expression:<\/p>\n\n\n

CASE value\n   WHEN value1 THEN result1\n   WHEN value2 THEN result2\n   \u2026\n   [ELSE else_result]\nEND<\/span><\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
In this syntax, CASE<\/code> matches the value with the value1<\/code>, value2<\/code>, etc., for equality and return the corresponding result1<\/code>, result2<\/code>,… If the value<\/code> does not equal to any value1<\/code>, value2<\/code>, … CASE<\/code> returns the result in the ELSE<\/code> clause if the ELSE<\/code> clause is specified.<\/p>\n\n\n\n
The CASE<\/code> compares the value<\/code> with values<\/code> in the WHEN<\/code> clauses for equality, you cannot use it with NULL<\/code> because NULL = NULL<\/code> returns false.<\/p>\n\n\n\n
Searched CASE expression<\/h3>\n\n\n\n
The following shows the syntax of a searched CASE<\/code> expression:<\/p>\n\n\n
CASE\n   WHEN expression1 THEN result1\n   WHEN expression2 THEN result2\n   \u2026\n   [ELSE else_result]\nEND<\/span><\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
In this syntax, the CASE<\/code> evaluates expressions specified in the WHEN<\/code> clauses. If an expression evaluates to true. CASE returns the corresponding result in the THEN<\/code> clause. Otherwise, it returns the result specified in the ELSE<\/code> clause. In case the ELSE<\/code> clause is not available, then the CASE<\/code> expression returns NULL<\/code> .<\/p>\n\n\n\n
MySQL CASE expression examples<\/h2>\n\n\n\n
1) Using CASE expression in the SELECT clause example<\/h3>\n\n\n\n
See the following orders<\/code> and customers<\/code> tables:<\/p>\n\n\n\n
\"\"<\/figure>\n\n\n\n
The following statement returns the customers and their orders:<\/p>\n\n\n
SELECT<\/span> \n    customerName, \n    COUNT<\/span>(*) orderCount\nFROM<\/span>\n    orders\nINNER<\/span> JOIN<\/span> customers \n\tUSING<\/span> (customerNumber)\nGROUP<\/span> BY<\/span> customerName\nORDER<\/span> BY<\/span> COUNT<\/span>(*);<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
\"MySQL<\/figure>\n\n\n\n
This example uses the CASE<\/code> expression in the SELECT<\/code> clause to return the type of customers based on the number of orders that customers ordered:<\/p>\n\n\n
WITH<\/span> cte AS<\/span> (\n\tSELECT<\/span> \n\t\tcustomerName, \n\t\tCOUNT<\/span>(*) orderCount\n\tFROM<\/span>\n\t\torders\n\tINNER<\/span> JOIN<\/span> customers \n\t\tUSING<\/span> (customerNumber)\n\tGROUP<\/span> BY<\/span> customerName\n)\nSELECT<\/span> \n    customerName, \n    orderCount,\n    CASE<\/span> orderCount\n\t\tWHEN<\/span> 1<\/span> THEN<\/span> 'One-time Customer'<\/span>\n        WHEN<\/span> 2<\/span> THEN<\/span> 'Repeated Customer'<\/span>\n        WHEN<\/span> 3<\/span> THEN<\/span> 'Frequent Customer'<\/span>\n        ELSE<\/span> 'Loyal Customer'<\/span>\n\tend<\/span> customerType\nFROM<\/span>\n    cte\nORDER<\/span> BY<\/span> customerName;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
\"MySQL<\/figure>\n\n\n\n
2) Using CASE expression in the ORDER BY clause example<\/h3>\n\n\n\n
The following example uses the CASE<\/code> expression to sort customers by state if the state is not NULL<\/code>,\u00a0 or sort the country in case the state is NULL<\/code>:<\/p>\n\n\n
SELECT<\/span> \n    customerName, \n    state, \n    country\nFROM<\/span>\n    customers\nORDER<\/span> BY<\/span> (\n    CASE<\/span>\n\tWHEN<\/span> state IS<\/span> NULL<\/span> \n            THEN<\/span> country\n\tELSE<\/span> state\nEND<\/span>);<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Try It Out<\/a><\/p>\n\n\n\n
\"MySQL<\/figure>\n\n\n\n
3) Using CASE expression with an aggregate function example<\/h3>\n\n\n\n
The following example uses the CASE<\/code> expression with the SUM()<\/a><\/code> function to calculate the total of sales orders by order status:<\/p>\n\n\n
SELECT<\/span> \n    SUM<\/span>(CASE<\/span>\n        WHEN<\/span> status<\/span> = 'Shipped'<\/span> THEN<\/span> 1<\/span>\n        ELSE<\/span> 0<\/span>\n    END<\/span>) AS<\/span> 'Shipped'<\/span>,\n    SUM<\/span>(CASE<\/span>\n        WHEN<\/span> status<\/span> = 'On Hold'<\/span> THEN<\/span> 1<\/span>\n        ELSE<\/span> 0<\/span>\n    END<\/span>) AS<\/span> 'On Hold'<\/span>,\n    SUM<\/span>(CASE<\/span>\n        WHEN<\/span> status<\/span> = 'In Process'<\/span> THEN<\/span> 1<\/span>\n        ELSE<\/span> 0<\/span>\n    END<\/span>) AS<\/span> 'In Process'<\/span>,\n    SUM<\/span>(CASE<\/span>\n        WHEN<\/span> status<\/span> = 'Resolved'<\/span> THEN<\/span> 1<\/span>\n        ELSE<\/span> 0<\/span>\n    END<\/span>) AS<\/span> 'Resolved'<\/span>,\n    SUM<\/span>(CASE<\/span>\n        WHEN<\/span> status<\/span> = 'Cancelled'<\/span> THEN<\/span> 1<\/span>\n        ELSE<\/span> 0<\/span>\n    END<\/span>) AS<\/span> 'Cancelled'<\/span>,\n    SUM<\/span>(CASE<\/span>\n        WHEN<\/span> status<\/span> = 'Disputed'<\/span> THEN<\/span> 1<\/span>\n        ELSE<\/span> 0<\/span>\n    END<\/span>) AS<\/span> 'Disputed'<\/span>,\n    COUNT<\/span>(*) AS<\/span> Total\nFROM<\/span>\n    orders;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Here is the output:<\/p>\n\n\n\n
\"MySQL<\/figure>\n\n\n\n
How it works.<\/p>\n\n\n\n
    \n
  • First, the CASE<\/code> statement returns 1 if the status equals the corresponding status such as Shipped, on hold, in Process, Cancelled, Disputed, and zero otherwise.<\/li>\n\n\n\n
  • Second, the SUM()<\/code> function returns the total number of orders per order status.<\/li>\n<\/ul>\n\n\n\n
    In this tutorial, you have learned how to use the MySQL CASE<\/code> expression to add if-else logic to the queries.<\/p>\n
    \n\t
    \n\t\t
    Was this tutorial helpful? <\/div>\n\t\t
    \n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t Yes <\/span>\n\t\t\t<\/button>\n\n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t No <\/span>\n\t\t\t<\/button>\n\t\t<\/div>\n\t<\/header>\n\n\t
    \n\t\t
    \n\t\t\t
    <\/div>\n\t\t\t\n\t\t\t
Try It Out<\/a><\/p>\n\n\n\n
The CASE<\/code> expression returns a result whose data type<\/a> depends on the context where it is used. For example, if the CASE<\/code> expression is used in the character string context, it returns the result as a character string. If the CASE<\/code> expression is used in a numeric context, it returns the result as an integer, a decimal, or a real value.<\/p>\n\n\n\n