Usage of case statement in sql

CASE statement is used to return different values ​​based on conditions. Syntax: CASE WHEN condition THEN result WHEN condition THEN result ... ELSE default_result END. Usage: 1. Specify a condition; 2. Return a result if the condition is true; 3. Use the ELSE clause to specify the default result when all conditions are false.

CASE statement usage in SQL

The CASE statement is used in SQL to return different values ​​based on different conditions. value. Its syntax is as follows:

<code class="sql">CASE
  WHEN condition1 THEN result1
  WHEN condition2 THEN result2
  ...
  ELSE default_result
END</code>

Usage:

1. Specify conditions: Use the WHEN clause to specify the conditions to be checked.
2. Return result: If the condition is true, the associated result value is returned.
3. Default result: Use the ELSE clause to specify the value to be returned if all conditions are false.

Example:

<code class="sql">-- 根据分数计算成绩等级
SELECT
  CASE
    WHEN score >= 90 THEN 'A'
    WHEN score >= 80 THEN 'B'
    WHEN score >= 70 THEN 'C'
    WHEN score >= 60 THEN 'D'
    ELSE 'F'
  END AS grade
FROM students;</code>

More options:

• Multiple CASE statements : Nested CASE statements can be used to handle more complex situations.
• SEARCH condition: Use the SEARCH clause to search for a pattern in a string and return matches.
• NULLIF condition: Use the NULLIF clause to check whether the value is NULL, and if so, return a specified NULL value.

Advantages:

• Provides a concise way to handle different conditions.
• Improve the readability and maintainability of queries.
• Eliminates the need for IF-ELSE statements.

Note:

• The ELSE clause must be specified to ensure a value is returned.
• The conditions of the WHEN clause must be mutually exclusive.

The above is the detailed content of Usage of case statement in sql. For more information, please follow other related articles on the PHP Chinese website!