Usage of case when statement in sql

The CASE WHEN statement is used in SQL to evaluate an expression and return a different value based on specified conditions. Its syntax consists of WHEN clause (conditional expression and return expression), ELSE clause (default expression) and END keyword. It can be used in a variety of scenarios, including assigning values, modifying results, and converting data formats.

Usage of CASE WHEN statement in SQL

CASE WHEN statement is a control flow in SQL query Statement that evaluates different expressions based on specified conditions. The syntax is as follows:

CASE
  WHEN <condition 1> THEN <expression 1>
  WHEN <condition 2> THEN <expression 2>
  ...
  ELSE <default_expression>
END

Usage:

• Condition: Specifies the Boolean expression used to evaluate the expression.
• Expression: The value that is evaluated and returned if the condition is true.
• Default_expression: The value that is evaluated and returned when no condition is true.

Steps:

1. Determine the condition to evaluate: Determine what condition you want to return a different value based on.
2. Writing the WHEN clause: For each condition, use the WHEN clause to specify the condition and the expression to be returned.
3. Add ELSE clause: Specify the default return value when all conditions are not met (optional).
4. End the CASE statement: Use the END keyword to end the CASE statement.

Example:

To assign discounts to customers based on their age group, you can use the following SQL query:

SELECT CASE
  WHEN age < 18 THEN 0.1
  WHEN age >= 18 AND age < 25 THEN 0.15
  WHEN age >= 25 AND age < 35 THEN 0.2
  ELSE 0.25
END AS discount
FROM customers;

In this query Medium:

• condition groups customers based on their age.
• Expression specifies the discount rate for each age group.
• The default expression is 0.25, which represents the discount rate for all customers over 35 years old.

Use cases:

The CASE WHEN statement can be used in various scenarios, including:

• Assign different values ​​based on conditions ( such as discounts or categories).
• Modify query results based on conditions (such as filtering or sorting).
• Convert data formats (such as converting text to numbers).

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