MySQL numeric functions

MySQL string functions

MySQL Date/Time functions

MySQL aggregate functions

MySQL flow control functions

MySQL CASE Function: Search Statement

In this tutorial, you'll learn how to use the MySQL CASE function as a search statement in a SELECT query.

Prerequisites:

A MySQL server up and running
Access to a MySQL user account with privileges to query tables

Tutorial:

Connect to the MySQL server:

To start the mysql command-line client, open a terminal or command prompt, and enter:

mysql -u [username] -p

Replace [username] with your MySQL username and enter your password when prompted.

Select a database:

Select the database containing the table you want to query:

USE [database_name];

Replace [database_name] with the name of your database.

Use the CASE function as a search statement:

The CASE function can be used as a search statement in a SELECT query to perform conditional logic based on the values of a specific column. The basic syntax for using CASE as a search statement is:

CASE
    WHEN [condition1] THEN [result1]
    WHEN [condition2] THEN [result2]
    ...
    ELSE [default_result]
END

For example, let's say you have a users table with the following columns: id, first_name, last_name, and status. The status column contains integer values that represent different user statuses:

1: Active
2: Inactive
3: Banned

You can use the CASE function to convert the integer status values into human-readable text in the SELECT query:

SELECT id, first_name, last_name,
    CASE
        WHEN status = 1 THEN 'Active'
        WHEN status = 2 THEN 'Inactive'
        WHEN status = 3 THEN 'Banned'
        ELSE 'Unknown'
    END AS user_status
FROM users;

This query will return a result set with columns id, first_name, last_name, and user_status. The user_status column will contain the human-readable text based on the status column values.

Exit the MySQL command-line client:

EXIT;

Now you have learned how to use the MySQL CASE function as a search statement in a SELECT query. This powerful feature allows you to perform conditional logic within your queries and can be used to enhance the readability and usefulness of your query results.

How to use CASE in SELECT statements in MySQL:

Employ the CASE statement to perform conditional operations in SELECT statements.

SELECT
  column1,
  column2,
  CASE
    WHEN condition1 THEN result1
    WHEN condition2 THEN result2
    ELSE default_result
  END AS alias_name
FROM table_name;

Conditional statements with CASE in MySQL:

Use CASE to create conditional statements based on specified conditions.

SELECT
  column,
  CASE
    WHEN condition THEN result
    ELSE default_result
  END AS alias_name
FROM table_name;

MySQL CASE statement examples:

Example:

SELECT
  employee_name,
  salary,
  CASE
    WHEN salary > 50000 THEN 'High'
    WHEN salary > 30000 THEN 'Moderate'
    ELSE 'Low'
  END AS salary_category
FROM employees;

CASE WHEN in MySQL SELECT query:

Use CASE WHEN for more concise syntax.

SELECT
  column,
  CASE WHEN condition THEN result ELSE default_result END AS alias_name
FROM table_name;

Nested CASE statements in MySQL:

Nest CASE statements for complex conditional logic.

SELECT
  column,
  CASE
    WHEN condition1 THEN
      CASE
        WHEN nested_condition THEN nested_result
        ELSE nested_default_result
      END
    WHEN condition2 THEN result2
    ELSE default_result
  END AS alias_name
FROM table_name;

Using CASE for conditional logic in MySQL:

Leverage CASE for flexible conditional logic.

SELECT
  column,
  CASE
    WHEN condition1 AND condition2 THEN result1
    WHEN condition3 OR condition4 THEN result2
    ELSE default_result
  END AS alias_name
FROM table_name;

CASE expression in WHERE clause in MySQL:

Apply CASE in the WHERE clause for dynamic conditions.

SELECT
  column
FROM table_name
WHERE
  CASE
    WHEN condition THEN 1
    ELSE 0
  END = 1;

CASE with aggregate functions in MySQL:

Use CASE with aggregate functions for conditional aggregation.

SELECT
  department,
  AVG(CASE WHEN salary > 50000 THEN salary ELSE NULL END) AS avg_high_salary
FROM employees
GROUP BY department;

MySQL CASE for custom sorting:

Utilize CASE for custom sorting criteria.

SELECT
  product_name,
  price
FROM products
ORDER BY
  CASE
    WHEN price < 50 THEN 1
    WHEN price >= 50 AND price < 100 THEN 2
    ELSE 3
  END;

Handling NULL values with CASE in MySQL:

Manage NULL values using CASE.

SELECT
  column1,
  column2,
  CASE
    WHEN column3 IS NOT NULL THEN column3
    ELSE 'Default Value'
  END AS alias_name
FROM table_name;

Using CASE for multiple conditions in MySQL:

Address multiple conditions with CASE.

SELECT
  column,
  CASE
    WHEN condition1 THEN result1
    WHEN condition2 THEN result2
    WHEN condition3 THEN result3
    ELSE default_result
  END AS alias_name
FROM table_name;

MySQL SWITCH statement equivalent using CASE:

Emulate a SWITCH statement using CASE.

SELECT
  column,
  CASE column_name
    WHEN 'value1' THEN result1
    WHEN 'value2' THEN result2
    ELSE default_result
  END AS alias_name
FROM table_name;

CASE for data transformation in MySQL:

Transform data using CASE.

SELECT
  employee_name,
  CASE
    WHEN position = 'Manager' THEN 'Lead'
    WHEN position = 'Developer' THEN 'Engineer'
    ELSE 'Other'
  END AS transformed_position
FROM employees;