SQL conditional expressions

When you enter your query in the SQL Editor or create a calculation, you use Presto SQL syntax. To return values based on whether specific conditions are met, you may find these common expressions useful.

CASE

To evaluate a list of conditions and return a value based on the first match or true condition, use a CASE expression, such as to enable if/else logic. CASE has two formats:

• In its simple form, CASE evaluates the list for a specific expression and returns its value, such as:
  

  CASE expression
      WHEN value THEN result
      [ WHEN ... ]
      [ ELSE result ]
  END

• In its searched form, CASE evaluates a list of booleans and return the value of the first TRUE condition, such as:
  

  CASE
      WHEN condition THEN result
      [ WHEN ... ]
      [ ELSE result ]
  END

For example, in its simple form to return a value ("one" or "two") based on a matching expression:

SELECT expression,
       CASE expression
           WHEN 1 THEN 'one'
           WHEN 2 THEN 'two'
           ELSE 'many'
       END

If no conditions are true, CASE returns the value of the ELSE clause ("many", in this example), or NULL if there is no ELSE clause.

Note: To return a value based on whether a single condition is true, use an IF statement.

COALESCE

To return the first non-null value in a list, use the COALESCE expression as COALESCE(value1, value2, value3).

IF

To return a value depending on whether a condition is true or false, use the IF statement as IF(condition, true_value, false_value).

Note: If the condition is false and no false_value is specified, the IF statement returns NULL.

IS NULL or IS NOT NULL

To test whether a value is empty or NULL, use the IS NULL or IS NOT NULL operator:

• To test for empty values, use IS NULL.
• To test for non-empty values, use IS NOT NULL.

For example, to test customer data for those with no addresses:

SELECT Customer_Name, Contact_Name, Address
FROM Customers
WHERE Address IS NULL

TRY

To return NULL when an expression fails due to corrupt or invalid data, use the TRY expression as TRY(expression). TRY handles these errors:

• Division by zero
• Invalid CAST or function argument
• Numeric value out of range

Note: To return a default value instead of NULL when an expression fails, use TRY with a COALESCE expression as COALESCE(TRY(expression), default_value).