Conditional Formula

Table of Contents

A formula column expression with Then/Else operators is called conditional formula and it must follow some patterns: there could be one or more Then and an optional Else formula terminator immediately after the last Then.

Simple Conditionals

Switch to design mode the Simple Conditionals demo query, located in the Queries > Demo Queries > Basic > Conditionals folder (use Add Demo Queries menu command if not there):

First column shows a simple IF case: if ‘fruits’ = ‘Apples’ (which is not the case here), we return ‘These are apples’. If not, it’s not always clear what’s the default database behavior: could be returning NULL or the original ‘fruits’ value. This is why it’s always recommended to follow the last Then by an Else (as in the second column): if no Then follows ends a boolean Formula expression, a Then True Else False (both strings!) is automatically appended.

The simple_case column shows a formula usually translated into a Simple CASE CASE field WHEN value THEN result […] [ELSE result] SQL expression. This is when one top expression is compared for equality with a bunch of values. Fourth column shows an alternative visual notation, which will be demonstrated more in depth for the Searched CASE: whenever a Then is followed by a contextual conditional operation, it applies to the top expression (as in column 3). But if you add an independent object (value, field, other query column or global function call), next conditional operation will be rather applied to this.

Here is the generated SQL query in Oracle for this demo:

SELECT CASE 'fruits' WHEN 'Apples' THEN 'These are apples' END AS "if",
   CASE 'fruits' WHEN 'Apples' THEN 'True' ELSE 'False' END AS "if_else",
   CASE 'fruits' 
      WHEN 'Apples' THEN 'These are apples'
      WHEN 'Oranges' THEN 'No, they are oranges'
      ELSE 'Neither apples nor oranges'
   END AS "simple_case",
   CASE 
      WHEN ('fruits' = 'Apples') THEN 'These are apples'
      WHEN ('fruits' = 'Oranges') THEN 'No, they are oranges'
      ELSE 'Neither apples nor oranges'
   END AS "simple_alternative_case"
FROM dual

When Replace CASE with IF query option is checked, a simple full IF-THEN-ELSE expression will be translated into a IF(expression, if_true, if_false) call, if the function is supported by your database (this works in MySQL, Amazon Aurora, MariaDB, Firebird and Microsoft Access). The same equivalent generated SQL query in MySQL using IF:

SELECT CASE 'fruits' WHEN 'Apples' THEN 'These are apples' END AS `if`,
   IF('fruits' = 'Apples', 'True', 'False') AS `if_else`,
   CASE 'fruits' 
      WHEN 'Apples' THEN 'These are apples'
      WHEN 'Oranges' THEN 'No, they are oranges'
      ELSE 'Neither apples nor oranges'
   END AS `simple_case`,
   CASE 
      WHEN ('fruits' = 'Apples') THEN 'These are apples'
      WHEN ('fruits' = 'Oranges') THEN 'No, they are oranges'
      ELSE 'Neither apples nor oranges'
   END AS `simple_alternative_case`
FROM dual

Also, when Replace CASE with DECODE query option is checked, a simple CASE expression will be translated into a DECODE(expression, match1, result1, match2, result2, …, default) call, if the function is supported by your database (this works in Oracle, DB2, Firebird and Amazon Redshift). Oracle supports DECODE and this is the equivalent SQL query using DECODE instead:

SELECT DECODE('fruits', 'Apples', 'These are apples') AS "if",
   DECODE('fruits', 'Apples', 'True', 'False') AS "if_else",
   DECODE('fruits', 'Apples', 'These are apples', 'Oranges',
      'No, they are oranges', 'Neither apples nor oranges') AS "simple_case",
   CASE 
      WHEN ('fruits' = 'Apples') THEN 'These are apples'
      WHEN ('fruits' = 'Oranges') THEN 'No, they are oranges'
      ELSE 'Neither apples nor oranges'
   END AS "simple_alternative_case"
FROM dual

Searched Conditionals

Switch now to design mode the Searched Conditionals demo query:

Whenever we have mixed conditional operators, not all equal, each formula is usually translated into a Searched CASE CASE WHEN condition THEN result […] [ELSE result] SQL expression. As for the Simple CASE, second column shows an Alternative CASE visual notation, with top expression repeated after each Then. Where this alternative extended pattern is indeed useful is in the third column, where we applied each conditional expression to a new independent item each time: only first condition remains applied to 123, second is now to 456, third to 789. This means we can easily create totally independent conditional expressions, not all applied to the same top expression.

The SQL query generated in Oracle for this demo:

SELECT CASE 
      WHEN (123 >= 100) AND (123 <= 400) THEN 'Between 100 and 400'
      WHEN (123 > 400) THEN 'Greater than 400'
      WHEN (123 = 0) THEN 'It''s zero'
      ELSE 'Some other value'
   END AS "searched_case",
   CASE 
      WHEN (123 >= 100) AND (123 <= 400) THEN 'Between 100 and 400'
      WHEN (123 > 400) THEN 'Greater than 400'
      WHEN (123 = 0) THEN 'It''s zero'
      ELSE 'Some other value'
   END AS "searched_alternative_case",
   CASE 
      WHEN (123 >= 100) AND (123 <= 400) THEN 'Between 100 and 400'
      WHEN (456 > 400) THEN 'Greater than 400'
      WHEN (789 = 0) THEN 'It''s zero'
      ELSE 'Some other value'
   END AS "searched_extended_case",
   CASE 
      WHEN ('fruits' = 'Apples') THEN 'These are apples'
      WHEN ('fruits' IS NULL) THEN 'Oops, this IS NULL'
      WHEN ('fruits' IS NOT NULL) THEN 'This IS NOT NULL'
   END AS "searched_case_with_null"
FROM dual

Conditionals with NULL

Switch to design mode the Null Conditionals demo query:

We expose both IsNull and IsNotNull as functions, translated obviously into the IS NULL and IS NOT NULL expressions. Equality or inequality comparison with Null is always immediately translated into a Is [Not] Null operation.

NullIf returns NULL on some condition. IfNull (a more friendly name for the internal COALESCE) returns an alternative value if the context is NULL.

Generated SQL query in Oracle:

SELECT (CASE WHEN (
      CASE WHEN 'some value here' IS NULL THEN 'True' ELSE 'False' END)
      IS NOT NULL THEN 'True' ELSE 'False' END) AS "is_null",
   CASE 
      WHEN ('some value here' IS NULL) THEN 'This IS NULL'
      WHEN ('some value here' IS NOT NULL) THEN 'This IS NOT NULL'
   END AS "is_null_2",
   NULLIF('expression1', 'expression2') AS "null_if",
   CASE 'expression1' WHEN 'expression2' THEN NULL ELSE 'expression1' END AS "null_if_2",
   COALESCE(COALESCE(
      COALESCE('expression1', 'expression2'), 'expression3'), 'All NULL') AS "coalesce",
   CASE 
      WHEN ('expression1' IS NOT NULL) THEN 'expression1'
      WHEN ('expression2' IS NOT NULL) THEN 'expression2'
      WHEN ('expression3' IS NOT NULL) THEN 'expression3'
      ELSE 'All NULL'
   END AS "coalesce_2"
FROM dual

Mapping Conditionals

Switch to design mode the Mapping Conditionals demo query. Its result looks like a lookup table: each value from one column is mapped to a different value (of different type) in another column.

The sample provides an alternate name for each fruit, based on the numeric id value. Data is generated on the fly in a subquery with SELECT UNION lines.

Generated query in Oracle, using DECODE:

WITH fruitsu_ AS (
SELECT 1 AS "id", 'Apples' AS "name", 2.0 AS "unit_price",
   22 AS "quantity", 3 AS "depends_on" FROM dual
UNION ALL SELECT 2, 'Oranges', 1.9, 5, 8 FROM dual
UNION ALL SELECT 3, 'Plums', 4, 0, NULL FROM dual
UNION ALL SELECT 4, 'Lemons', 0.88, 8, 7 FROM dual
UNION ALL SELECT 5, 'Grapes', 1.22, 0, 1 FROM dual
UNION ALL SELECT 6, 'Melons', 1.67, 14, 10 FROM dual
UNION ALL SELECT 7, 'Apricots', 2.3, 8, 8 FROM dual
UNION ALL SELECT 8, 'Bananas', 3, 14, 1 FROM dual
UNION ALL SELECT 9, 'Cherries', 0.20, 14, 3 FROM dual
UNION ALL SELECT 10, 'Clementines', 2.4, 1, 1 FROM dual
ORDER BY 1)

SELECT fruitsu_."id" AS "id",
   DECODE(fruitsu_."id", 1, 'apple', 2, 'orange', 3, 'plum', 
      4, 'lemon', 5, 'grape', 6, 'melon', 7, 'apricot', 
      8, 'banana', 9, 'cherry', 10, 'clementine') AS "name"
FROM fruitsu_