How Does SQL CASE Expression Syntax Vary Across Database Engines?

SQL CASE expression syntax

The full syntax of a SQL CASE expression varies depending on the database engine used. The following is the syntax for SQL Server:

CASE syntax using case-expression:

CASE case-expression
    WHEN when-expression-1 THEN value-1
  [ WHEN when-expression-n THEN value-n ... ]
  [ ELSE else-value ]
END

CASE syntax using boolean-when-expression:

CASE
    WHEN boolean-when-expression-1 THEN value-1
  [ WHEN boolean-when-expression-n THEN value-n ... ]
  [ ELSE else-value ]
END

The following are the terms and concepts used in grammar:

• case-expression: An expression that produces a value.

• when-expression-x: An expression to compare with case-expression.

• boolean-when-expression: An expression that produces a TRUE/FALSE result.

• value-x: The result of a CASE statement if:

  • when-expression == case-expression
  • boolean-when-expression == TRUE

• else-value: The result of the CASE statement if no matching WHEN condition is found.

You need to pay attention to the following points:

• The order of the WHEN statements is critical because the first matching condition will be used.
• If no ELSE clause is provided and no matching WHEN conditions are met, the result will be NULL.

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