TestingDocs.com
Software Testing website
  • Automation
    • Selenium
    • JBehave Framework
  • Tutorials
    • MySQL Tutorials
    • Testlink
    • Maven
    • Git
  • IDEs
    • IntelliJ IDEA
    • Eclipse
  • Flowcharts
    • Flowgorithm
    • Raptor
  • About

MySQL

MySQL CASE Function

Overview

In this tutorial, we will learn about MySQL CASE Function. The CASE function provides a branching evaluation construct.

CASE Function

When the initial expression value is present, CASE compares it to the expression following each WHEN. For the first one that is equal, result for the corresponding THEN value is returned. This is useful for comparing a given value to a set values.

Simple CASE Syntax

The general syntax for the CASE function is as follows:

CASE value

WHEN <compare_value> THEN <result>
[WHEN <compare_value>THEN<result>…]
[ELSE <result>]
END

Example

mysql> SELECT CASE 3
-> WHEN 1 THEN ‘One’
-> WHEN 2 THEN ‘Two’
-> WHEN 3 THEN ‘Three’
-> ELSE ‘Not Specified’
-> END AS ‘Column Header’;
+—————+
| Column Header |
+—————+
| Three |
+—————+
1 row in set (0.01 sec)

mysql> — Simple CASE Syntax

Simple MySQL CASE Function

Search CASE Syntax

When the initial expression value is not present, CASE evaluates the WHEN expressions. For the first one that is true (not zero and not NULL), the result for the corresponding THEN value is returned. This is useful for performing non-equality tests or testing arbitrary conditions. If no WHEN expression matches, the expression for the ELSE clause is returned, if there is one.

Syntax

CASE
WHEN <condition>THEN<result>
[WHEN<condition>THEN<result>…..]
[ELSE<result>]
END

 

The first version returns the result where value=compare_value. The second version returns the result for the first condition that is true. If there was no matching result value, the result after ELSE is returned, or NULL if there is no ELSE part.

Example

Let’s take an example from the City table from the world MySQL database. Suppose that if a city population is greater than 2 million then its called as a metropolitan city. We will the CASE function to print the New York city metro status in the following query:

mysql> SELECT ID, Name, CountryCode, Population,
-> CASE
-> WHEN Population > 2000000 THEN ‘Metro’
-> WHEN Population <= 2000000 THEN ‘Non-Metro’
-> ELSE ‘N/A’
-> END As ‘City Metro Status’
-> FROM City
-> WHERE Name= ‘New York’;
+——+———-+————-+————+——————-+
| ID | Name | CountryCode | Population | City Metro Status |
+——+———-+————-+————+——————-+
| 3793 | New York | USA | 8009000 | Metro |
+——+———-+————-+————+——————-+
1 row in set (0.00 sec)

 

MySQL CASE Function Example

—

MySQL Tutorials

MySQL Tutorials on this website:

https://www.testingdocs.com/mysql-tutorials-for-beginners/

For more information on MySQL Database:

https://www.mysql.com/

Related Posts

DataGrip Desktop Shortcut

MySQL /

Launch DataGrip on Windows

DataGrip Download

MySQL /

Install DataGrip IDE on Windows 11

MySQL Workbench Windows 11

MySQL /

New MySQL Connection in Workbench

MySQL Command-line Client

MySQL /

Command-Line MySQL Client

Start MySQL Client

MySQL /

Start MySQL Client on Windows 11

‹ MySQL Flow Control Fuctions› MySQL LIKE Operator

Recent Posts

  • MS Access Data Types
  • Install RAPTOR Avalonia on CentOS
  • Download RAPTOR Avalonia Edition on Windows
  • npm doctor command
  • Build & Run CLion Project
  • Create New CLion C Project on Windows
  • Configure CLion Toolchains on Windows
  • Launch CLion IDE on Windows
  • Activate CLion IDE
  • CLion IDE for C/C++ Development

Back to Top

Links

  • Contact
  • Privacy Policy
  • Cookie Policy

www.TestingDocs.com

Go to mobile version