SQL Conditional Logic
Interna
Overview
SQL conditional logic is the ability of SQL statements to take one of several paths during execution, depending on the value of an expression. The SQL has syntax that provides IF/ELSE conditional logic, except that it uses the CASE
keyword instead of IF
. The CASE
expressions can be used in conditional SELECT
, INSERT
, UPDATE
and DELETE
and also in the SELECT ... WHERE
clause.
All major databases have built-in function that mimic IF/ELSE statements. However, the CASE
expressions should be preferred instead, because CASE
is part of the SQL92 standard and thus available on all standard-compliant servers. CASE
has been built into the grammar of the SELECT
, INSERT
, UPDATE
and DELETE
statements, and in the WHERE
clause.
There are two different types of CASE
syntax: searched CASE expressions and simple CASE expressions.
Searched CASE Expressions
CASE
WHEN <conditional_expression_1> THEN <expression_1>
WHEN <conditional_expression_2> THEN <expression_2>
...
[ELSE <default_expression>]
END
When the CASE
expression is evaluated, the WHEN
conditional expressions are evaluated in order from top to bottom. As soon as one of the conditional expressions is evaluated to TRUE, the corresponding expression is evaluated and returned. If none of the WHEN
conditional expressions evaluate to true, then expression introduced by ELSE
is evaluated and returned. If the CASE
does not have an ELSE
, then NULL
is returned.
All returned expressions (<expression_1>
, <expression_2>
, ... <default_expression>
) must evaluate to the same type.
<expression_1>
, <expression_2>
, ... <default_expression>
can all be subqueries.
This is an example of using CASE
in SELECT
:
SELECT name,
CASE
WHEN active = 1 THEN 'ACTIVE'
ELSE 'INACTIVE'
END activity_level
FROM person
This is an example of using CASE
in SELECT ... WHERE
:
SELECT name FROM person
WHERE
CASE
WHEN active IS NULL THEN FALSE
ELSE active = 1
END;
CASE
can also be used in:
Simple CASE Expressions
CASE <expression>
WHEN <result_1> THEN <expression_1>
WHEN <result_2> THEN <expression_2>
...
[ELSE <default_expression>]
END
The <expression>
is evaluated and the result compared from top to bottom with result_1
, result_2
, etc. Upon the first match, the corresponding expression is evaluated and returned. If there is no match, the <default_expression>
is evaluated and returned. If there is ELSE
, NULL
is returned.
A simple IF/ELSE:
CASE <expression_that_evaluates_to_true_or_false> WHEN TRUE THEN <if_expression>
ELSE <else_expression>
END
Usage example:
CASE array_length(person.skills, 1) > 0 WHEN TRUE THEN ('skill_we_search_for' = ANY(person.skills))
ELSE 'key_we_search_for' = ANY(general.skills)
END