MySQL FAQs
FAQs Categories
Client Server Commands
Database Structure
Table Types or Storage Engines
Indexes
SQL Statements
Table Joins
Funtions and Operators
Tricky Select Queries
Speed Up Queries
Data Back Up
General Questions
Errors
1PLs Company - #1Payday.Loans Agency - Loans online and near me $100-$2500 (Same Day)
Powered by MySQL
 
Home / Funtions and Operators / Question No: 229

How to set conditions on query data with CASE construct?

Answer No: 229

Control flow functions enable you to choose between different values based on the result of an expression. The CASE construct is not a function, but it too provides flow control. It has two forms of syntax. The first looks like this:

CASE case_expr
  WHEN when_expr THEN result
  [WHEN when_expr THEN result] ...
  [ELSE result]
END

The expression case_expr is evaluated and used to determine which of the following clauses in the rest of the CASE to execute. The when_expr in the initial WHEN clause is evaluated and compared to case_expr. If the two are equal, the expression following THEN is the result of the CASE. If when_expr is not equal to case_expr, and there are any following WHEN clauses, they are handled similarly in turn. If no WHEN clause has a when_expr equal to case_expr, and there is an ELSE clause, the expression in the ELSE clause becomes the CASE result. If there is no ELSE clause the result is NULL. In the following example, CASE expression returns a string that indicates full name of language against each language_id available in the table:

mysql> SELECT CASE languageId
    ->          WHEN 1 THEN 'English'
    ->          WHEN 2 THEN 'French'
    ->          WHEN 3 THEN 'Arabic'
    ->          WHEN 4 THEN 'dutch'
    ->        ELSE 'Urdu'
    ->        END AS Language
    -> FROM language;
+----------+
| Language |
+----------+
| English  |
| French   |
| Arabic   |
| dutch    |
+----------+
4 rows in set (0.02 sec)

The second CASE syntax looks like this:

CASE
  WHEN when_expr THEN result
  [WHEN when_expr THEN result] ...
  [ELSE result]
END

For this syntax, the conditional expression in each WHEN clause is executed until one is found to be true, and then its corresponding THEN expression becomes the result of the CASE. If none of them are true and there is an ELSE clause, its expression becomes the CASE result. If there is no ELSE clause the result is NULL.  The following CASE expression tests whether the value of the @val user variable is NULL or less than, greater than, or equal to 0:

mysql> SET @val = NULL;
mysql> SELECT CASE
    ->   WHEN @val IS NULL THEN '@val is NULL'
    ->   WHEN @val < 0 THEN '@val is less than 0'
    ->   WHEN @val > 0 THEN '@val is greater than 0'
    ->   ELSE '@val is 0'
    -> END AS result;
+--------------+
| result       |
+--------------+
| @val is NULL |
+--------------+

Related MySQL FAQs to the Above FAQ

What-is-MySQL-date-format-and-its-specifiers What is MySQL date format and its specifiers?

How-to-get-comma-separated-all-values-in-a-single-row-against-each-ID How to get comma separated all values in a single row against each ID?

How-to-concat-values-in-MySQL How to concat values in MySQL?

How-to-convert-time-zone-in-MySQL How to convert time zone in MySQL?

What-are-comparison-functions-in-MySQL What are comparison functions in MySQL

How-to-set-control-conditions-on-query-data-with-IF-funtion How to set control conditions on query data with IF funtion?

What-are-mathematical-functions-in-MySQL What are mathematical functions in MySQL?

How-are-aggregate-functions-in-MySQL How are aggregate functions in MySQL?

What-are-string-functions-in-MySQL What are string functions in MySQL?

What-are-temporal-or-date-time-functions-in-MySQL What are temporal or date time functions in MySQL?

What-are-NULL-value-related-functions-in-MySQL What are NULL value related functions in MySQL?

How-does-union-work-in-MySQL How does union work in MySQL?

About FAQs: Recently Added FAQs

About MySQL FAQs: Site Map | Bookmark Us | Recommend this Site to Your Friend | Contact Us

Useful Links: Wikipedia.org | Oracle.com | w3schools.com | www.php.net | Github.com

© 2023  www.mysqlfaqs.net
All rights reserved.