How many types of case syntax are there in sql?

The CASE syntax in SQL provides a mechanism to conditionally select different expressions. It has four types: Simple: checks the condition to be true or false and returns the result. Search: Compare values ​​to return matching results. Range: Returns results based on range conditions. NULLIF: Check whether the expressions are the same, if they are the same, return NULL.

CASE syntax in SQL

The CASE syntax in SQL provides a conditional selection of different expressions powerful mechanism. It has four types:

1. Simple CASE syntax

<code>CASE WHEN 条件 THEN 结果 ELSE 其他结果 END</code>

is used for simple condition checks, and the result will be returned only when the condition is true.

2. Search CASE syntax

<code>CASE 表达式
    WHEN 值1 THEN 结果1
    WHEN 值2 THEN 结果2
    ...
    ELSE 其他结果
END</code>

is used to compare an expression with a range of values ​​and return the corresponding results based on the matching values.

3. Range CASE syntax

<code>CASE
    WHEN 表达式 BETWEEN 值1 AND 值2 THEN 结果1
    WHEN 表达式 BETWEEN 值3 AND 值4 THEN 结果2
    ...
    ELSE 其他结果
END</code>

is used to return results based on a given range condition.

4. NULLIF CASE syntax

<code>NULLIF(表达式1，表达式2)</code>

is used to check whether two expressions are the same. If they are the same, NULL is returned, otherwise expression 1 is returned.

The choice of each CASE syntax depends on the specific logic to be implemented. These syntaxes are widely used for data transformation, condition checking, and returning specific values ​​under different conditions.

The above is the detailed content of How many types of case syntax are there in sql?. For more information, please follow other related articles on the PHP Chinese website!