How Does the SQL CASE Expression Work Across Different Database Systems?

Complete syntax of SQL CASE expression

SQL CASE expressions are designed to handle complex queries, allowing you to evaluate multiple conditions and return specific values ​​based on which conditions are met. Its syntax varies among different database engines.

SQL Server

The general syntax of SQL Server’s CASE expression is as follows:

<code class="language-sql">CASE case-expression
    WHEN when-expression-1 THEN value-1
  [ WHEN when-expression-n THEN value-n ... ]
  [ ELSE else-value ]
END</code>

Oracle

Oracle's CASE expression syntax takes a slightly different approach:

<code class="language-sql">CASE
    WHEN boolean-when-expression-1 THEN value-1
  [ WHEN boolean-when-expression-n THEN value-n ... ]
  [ ELSE else-value ]
END</code>

Components of CASE expression

• case-expression: The value to evaluate against when-expressions.
• when-expression: An expression to compare with case-expression.
• value: The result of a CASE statement if the corresponding when-expression matches the case-expression.
• boolean-when-expression: A Boolean expression that determines whether to return the corresponding value.
• else-value: The value returned if no when-expression matches case-expression.

Execution semantics

CASE expression evaluates each when-expression sequentially. When a match is found, the corresponding value is returned. If no match is found, returns the else-value if there is one. Note that the order of when-expressions is important because the first matching expression takes precedence.

Example

Consider the following query:

<code class="language-sql">SELECT CASE student_age
    WHEN 18 THEN 'Teenager'
    WHEN 21 THEN 'Adult'
    ELSE 'Minor'
END AS age_category
FROM students;</code>

This query assigns an age category to the student based on their age, "Teenager" if they are 18, "Adult" if they are 21, or "Minor" if they are under 18 or no match is found.

The above is the detailed content of How Does the SQL CASE Expression Work Across Different Database Systems?. For more information, please follow other related articles on the PHP Chinese website!