MySQL CASE() Function

Syntax of MySQL CASE() Function

The syntax of MySQL CASE Function is as follows:

Parameters of MySQL CASE() Function

The MySQL CASE Function takes two parameters as given below:

• condition1, condition2, …conditionN: These are the conditions according to which result is being output is generated.
• result1, result2, …resultN: These are the specific output values which is to be returned if the stated condition is satisfied.

Return Value of MySQL CASE() Function

The MySQL CASE() Function returns a result based on the conditions specified in the function's parameters. The result can be a value, expression, or SQL statement.

Exceptions of MySQL CASE() Function

MySQL CASE() Function does not have any exceptions.

How does the MySQL CASE() Function Work?

The MySQL CASE() Function works by evaluating the value or expression specified in the function's parameters. It then compares the value with each of the specified conditions (condition1, condition2, etc.) until a match is found. Once a match is found, the corresponding result (result1, result2, etc.) is returned.

If none of the specified conditions match the value, then the default result (resultN) is returned. If no default result is specified, then NULL is returned.

Examples

Now we have learned about the CASE() function in MySQL, So we can apply it in some of our examples.

Example-1: Basic usage of CASE() function

Below Code is a basic example of how to use the MySQL CASE() function to apply conditional logic when querying data from a table. Here is a breakdown of what each part of the code does:

• The first part creates a table called MyTable with a single column called "value" of type int.
• The second part inserts five rows of data into the MyTable table, with values ranging from 1 to 5.
• The third part queries the data in the MyTable table and uses the MySQL CASE() function to apply conditions to the "value" column. The function checks the value of each row and assigns a corresponding text value based on the condition met. For example, when the value is equal to 1, the text "One" is returned; when the value is equal to 2, the text "Two" is returned, and so on. If none of the conditions are met, the text "Five" is returned as the default result.

Output:

The final output of this code is a table with two columns: "Value" and "Txt". The "Value" column displays the original value stored in the "value" column of the MyTable table, and the "Txt" column displays the corresponding text value based on the conditional logic applied using the MySQL CASE() function.

Example-2: Using the CASE() function to compare values

This code is an example of how to use the MySQL CASE() function to apply conditional logic when querying data from a table. Here is a breakdown of what each part of the code does:

• The first part creates a table called MyTable with a single column called "price" of type int.
• The second part inserts five rows of data into the MyTable table, with values ranging from 100 to 500.
• The third part queries the data in the MyTable table and uses the MySQL CASE() function to apply conditions to the "price" column. The function checks the value of each row and assigns a corresponding text value based on the condition met. For example, when the value is less than 200, the text "Less than 200" is returned; when the value is equal to 200, the text "Equal to 200" is returned, and when the value is greater than 200, the text "More than 200" is returned.

Output:

The final output of this code is a table with two columns: "Price" and "Value". The "Price" column displays the original value stored in the "price" column of the MyTable table, and the "Value" column displays the corresponding text value based on the conditional logic applied using the MySQL CASE() function.

Example-3: Using the CASE() with WHERE Clause

This code is an example of how to use the MySQL CASE() function with a WHERE clause to filter data from a table based on certain conditions. Here is a breakdown of what each part of the code does:

• The first part creates a table called MyTable with a single column called "price" of type int.
• The second part inserts five rows of data into the MyTable table, with values ranging from 100 to 500.
• The third part queries the data in the MyTable table and uses the MySQL CASE() function with a WHERE clause to filter the data. The function checks the value of each row and returns true when the value is equal to either 100 or 200. If none of these conditions are met, the function returns null.

Output:

The final output of this code is a table with a single column called "price" that contains only the rows where the value is either 100 or 200.

Example-4: Using the CASE() with Update Clause

This code is an example of how to use the MySQL CASE() function with an UPDATE statement to modify data in a table based on certain conditions. Here is a breakdown of what each part of the code does:

• The first part creates a table called MyTable with a single column called "price" of type int.
• The second part inserts five rows of data into the MyTable table, with values ranging from 100 to 500.
• The third part uses the MySQL CASE() function with an UPDATE statement to modify the data in the MyTable table. The function checks the value of each row and sets the price to 600 when the value is equal to 100. For all other values, the price is set to 1000.
• The fourth part prints the modified data in the MyTable table.

Output:

The final output of this code is a table called MyTable with a single column called "price" that has been modified based on the conditions specified in the CASE() function.

See Also

• SQL INSERT INTO Statement
• WHERE Clause in SQL
• SQL GROUP BY Clause