SQL Conditional Expressions

Conditional Expressions Summary Table

Function Description

Case operator

Return the first non-NULL argument

Returns the largest value from a list of any number of expressions

NULL IF/ELSE construct

IN

Check whether a value is within a set of values

Returns the smallest value from a list of any number of expressions

Return NULL if expr1 = expr2

CASE

There are two key uses for the CASE expression:

  1. A more generic version where every condition should be a boolean expression evaluated on true or false, and the first evaluated to true determines the result.

  2. Or CaseSearch where a value expression is employed to determine the branch used.

Syntax 1

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

Syntax 2

CASE valueExpression WHEN value1 THEN result1
    [WHEN ...]
    [ELSE result]
END

Description

CASE clauses can be used wherever an expression is valid. condition is an expression that returns a boolean result.

If the result is:

  • True, then the value of the CASE expression is the result that follows the condition.

  • False, any subsequent WHEN clauses are searched in the same manner.

If no WHEN condition is true then the value of the case expression is the result in the ELSE clause.

If the ELSE clause is omitted and no condition matches, the result is NULL.

NuoDB enforces the same type, precision and scale for all the branches of the CASE expression. In situations where the branches do not have the same type, all the branches are coerced to a computed common type. The coercions are enforced during constant folding, which means that when case coercions are applied, a query might fail with a coercion error even if the failing branch would not have been part of the result.

For the generic version of CASE, each predicate in the WHEN clause is type checked in isolation and coercions are applied following the comparison operators model. With the simplified version of the CASE expression, the type of search condition and the types of the WHEN clauses have to be coercible to a common type.

Example

CREATE TABLE tst_table (P int);
INSERT INTO tst_table VALUES (1),(2),(NULL),(20);
SELECT CASE WHEN P = 1 THEN 'one'
  WHEN P is null THEN 'null'
  WHEN P > 10 THEN 'big'
  ELSE 'unknown' END
  from tst_table;

 -------
 one
 unknown
 null
 big

SELECT CASE P
  WHEN 1 THEN 'one'
  WHEN 2 THEN 'two'
  ELSE 'big' END
  from tst_table;

 ---
 one
 two
 big
 big

COALESCE

Syntax

COALESCE(value [, ...])

Description

Returns the first of its arguments that is not null. Null is returned only if all arguments are null. It is often used to substitute a default value for null values when data is retrieved for display.

Like a CASE expression, COALESCE will not evaluate arguments that are not needed to determine the result (that is, arguments to the right of the first non-null argument are not evaluated).

Example

CREATE TABLE t (a int, b int, c int);
INSERT INTO t VALUES (1, 2, 3), (null, 4, 5), (null, null, 6), (null, null, null);
SELECT COALESCE(a, b, c, 7) FROM t;

 COALESCE
 ---------
     1
     4
     6
     7

GREATEST

Syntax

GREATEST(expr1, expr2 [, ...] )

Description

Returns the largest value from a list of two or more expressions.

If the list of expressions is not all of the same data type, expressions in the list will be converted to the common type computed based on the types of the arguments. An error is returned if a common type does not exist or if not all expressions are convertible to the common type.

Returns NULL if any expression in the list is NULL.

Example

SELECT GREATEST('abc', 'ABC') FROM DUAL;

 greatest
 ---------
    abc

SELECT GREATEST('abc', 'ABC', NULL) FROM DUAL;

 greatest
 ---------
  <null>

/* Count teams that have more LOSSES than WINS or TIES */
SELECT COUNT(*) FROM hockey.teams
  WHERE GREATEST(wins, losses, ties) = losses;

 COUNT
 ------
  685

SELECT GREATEST(1,2,'abc') FROM DUAL;
Error 22000: unable to convert string "abc" into type "number"

IFNULL

Syntax

IFNULL(expr1,expr2)

Description

If expr1 is not NULL, IFNULL() returns expr1; otherwise it returns expr2. The returned type of IFNULL is the common type of the two arguments. Both arguments must be coercible to the common type.

Example

SELECT IFNULL(1,0) FROM dual;

 IFNULL
 -------
    1

SQL> SELECT IFNULL(NULL,10) FROM dual;

 IFNULL
 -------
   10

SQL> SELECT 1.0000/10.0000, IFNULL(1.000/null,1.000/10.0000), IFNULL(1.000/null,10.0000), IFNULL(null/10.000,1.000) FROM dual;

                IFNULL     IFNULL    IFNULL
 ------------ ----------- --------- --------
 .10000000000 .1000000000 10.000000 1.000000

IN

Syntax

WHERE column IN (x1,x2,x3 [,...] )

which is equivalent to:

WHERE ( column=x1 OR column=x2 OR column=x3 [OR ... ] )

Description

Checks if the specified column data is a member of the specified list. It can be used with WHERE, CHECK, and creation of views.

The IN operator applies the comparison common type algorithm in two steps, first on the elements in the IN list and then by computing the common type between the search argument and the common type of the IN list. The common type on the list of arguments on the right hand side is computed from left to right by computing the common type between the currently computed common type and the next argument. This makes the IN list not commutative in terms of the types of the arguments.

Example

SELECT * FROM hockey WHERE position IN ('Fan','Goalie');

 ID  NUMBER       NAME      POSITION   TEAM
 --- ------- -------------- --------- ------
 22    35    ANTON KHUDOBIN  Goalie   Bruins
 23    40    TUUKKA RASK     Goalie   Bruins
 24     1    MAX SUMMIT      Fan      Bruins

LEAST

Syntax

LEAST(expr1, expr2 [, ...])

Description

Returns the smallest value from a list of two or more expressions.

If the list of expressions is not all the same data type, NuoDB applies the same type rules as for the GREATEST expression.

Returns NULL if any expression in the list is NULL.

Example

SELECT LEAST('abc', 'ABC') FROM DUAL;

  least
 ------
  ABC

SELECT LEAST('abc', 'ABC', NULL) FROM DUAL;

  least
 ------
  <null>

/* Count teams that have less WINS than LOSSES or TIES */
 SELECT COUNT(*) FROM hockey.teams
  WHERE LEAST(wins, losses, ties) = wins;

 COUNT
 ------
   20

SELECT LEAST(1,2,'abc') FROM DUAL;
Error 22000: unable to convert string "abc" into type "number"

NULLIF

Syntax

NULLIF(value1, value2)

Description

Returns a null value if value1 and value2 are equal; otherwise it returns value1. An error is returned if value1 and value2 do not have the same type and cannot be coerced to a common type.

Example

CREATE TABLE t (a int, b int);
INSERT INTO t VALUES (null, null), (null, 1), (1, null), (1, 1);
SELECT a, b, nullif (a, b) as nullif FROM t;

   A      B    NULLIF
 ------ ------ -------
 <null> <null> <null>
 <null>      1 <null>
      1 <null>      1
      1      1 <null>

SELECT COALESCE (a, 0) as a, COALESCE (b, 0) as b, COALESCE (nullif (a, b), 0) AS nullif FROM t;

 A  B  NULLIF
 -- -- -------
 0  0     0
 0  1     0
 1  0     1
 1  1     0