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 ( ORDER BY COUNT(*) Code language: SQL (Structured Query Language) ( sql ) The following statement returns the customers and their orders: SELECT See the following orders and customers tables: MySQL CASE expression examples A) Using CASE expression in the SELECT clause example If the CASE expression is used in a numeric context, it returns the result as an integer, a decimal, or a real value. For example, if the CASE expression is used in the character string context, it returns the result as a character string.

The CASE expression returns a result whose data type depends on the context where it is used. In case the ELSE clause is not available, then the CASE expression returns NULL. Otherwise, it returns the result specified in the ELSE clause. CASE returns the corresponding result in the THEN clause. In this syntax, the CASE evaluates expressions specified in the WHEN clauses. The following shows the syntax of a searched CASE expression: CASE The CASE compares the value with values in the WHEN clauses for equality, you cannot use it with NULL because NULL = NULL returns false. 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 following illustrates the syntax of a simple CASE expression: CASE valueĮND Code language: SQL (Structured Query Language) ( sql )

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. The CASE expression has two forms: simple CASE and searched CASE. Generally speaking, you can use the CASE expression anywhere that allows a valid expression e.g., SELECT, WHERE and ORDER BY clauses. MySQL CASE expression is a control flow structure that allows you to add if-else logic to a query. Whereas, the operator is terminated by END.Summary: in this tutorial, you will learn how to use the MySQL CASE expression to add if-else logic to queries. MySQL also have a CASE operator which is similar to the CASE statement the only difference is that the ELSE NULL clause is not allowed in the CASE statement and it is terminated by END CASE. Mysql> CALL case_example ('MSC', OK, 0 rows affected (0.00 sec) Mysql> CALL case_example ('BSC', OK, 0 rows affected (0.00 sec) Mysql> CALL case_example ('M-Tech', OK, 0 rows affected (0.00 sec) Mysql> CALL case_example ('B-Tech', OK, 0 rows affected (0.13 sec) You can call the above procedure using various input values as follows − WHEN 'BSC' THEN SET full_form = 'Bachelor of Science' WHEN 'M-Tech' THEN SET full_form = 'Master of Technology' SET full_form = 'Bachelor of Technology' Syntaxįollowing is the syntax of the CASE statement −įollowing query demonstrates the usage of the MySQL CASE statement in a procedure −ĬREATE PROCEDURE case_example(IN degree VARCHAR(20), OUT full_form Varchar(50)) The case statement in MySQL is used to test a value for equality against a list of values/conditions.

Mysql case statement example