SQL Conditional Logic

Interna

• SQL WHERE

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:

• Conditional INSERT
• Conditional UPDATE
• Conditional DELETE

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

Preventing Divisions by zero with CASE

SELECT ..., 
  sum(amount) /
    CASE WHEN count(amount) = 0 THEN 1
    ELSE count(amount)
    END average_amount 
FROM ...

Handing NULL with CASE

SELECT name, 
  CASE
    WHEN address IS NULL THEN 'Unknown'
    ELSE address
  END address,
FROM person;