Conditional Expressions

When executing SQL statements, conditional expressions can be used to filter data that meets specific criteria.

There are several main types of conditional expressions:

• CASE

  A CASE expression is a conditional expression, similar to the CASE statement found in other programming languages.

  Figure 1 shows the syntax of a CASE expression.

  Figure 1 case::=
  

  The CASE clause can be used within a valid expression. A condition is an expression that returns a Boolean data type.

  • If the result is true, the result of the CASE expression corresponds to the matching condition's result.
  • If the result is false, subsequent WHEN or ELSE clauses are processed similarly.
  • If none of the WHEN conditions are set to true, the result of the expression is determined by the ELSE clause's result. If the ELSE clause is omitted and no conditions match, the result is NULL.

  Example:

  1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21

  postgres=#CREATE TABLE case_when_t1(CW_COL1 INT) store AS orc; postgres=#INSERT INTO case_when_t1 VALUES (1), (2), (3); postgres=#SELECT * FROM case_when_t1;SELECT * FROM case_when_t1 order by cw_col1; cw_col1 --------- 1 2 3 (3 rows) postgres=#SELECT CW_COL1, CASE WHEN CW_COL1=1 THEN 'one' WHEN CW_COL1=2 THEN 'two' ELSE 'other' END FROM case_when_t1 order by cw_col1; cw_col1 | case ---------+------- 1 | one 2 | two 3 | other (3 rows) postgres=#DROP TABLE case_when_t1;DROP TABLE case_when_t1;

• DECODE

  Figure 2 shows the syntax of a DECODE expression.

  Figure 2 decode::=
  

  This compares the base_expr with each compare(n). If there is a match, it returns the corresponding value(n). If no matches occur, it returns the default.

  See Conditional Expression Functions for examples.

  1 2 3 4 5

  postgres=#SELECT DECODE('A','A',1,'B',2,0); case ------ 1 (1 row)

• COALESCE

  Figure 3 shows the syntax of a COALESCE expression.

  Figure 3 coalesce::=
  

  COALESCE returns the first non-NULL value among its parameters. If all parameters are NULL, it returns NULL. It is commonly used to substitute NULL with a default value during data display. Similar to a CASE expression, COALESCE only evaluates the necessary parameters to determine the result. Thus, parameters to the right of the first non-null one remain unevaluated.

  Example:

  1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17

  postgres=# CREATE TABLE c_tabl(description varchar(10), short_description varchar(10), last_value varchar(10)) store AS orc; postgres=#INSERT INTO c_tabl VALUES('abc', 'efg', '123'); postgres=#INSERT INTO c_tabl VALUES(NULL, 'efg', '123'); postgres=#INSERT INTO c_tabl VALUES(NULL, NULL, '123'); postgres=#SELECT description, short_description, last_value, COALESCE(description, short_description, last_value) FROM c_tabl ORDER BY 1, 2, 3, 4; description | short_description | last_value | coalesce -------------+-------------------+------------+---------- abc | efg | 123 | abc | efg | 123 | efg | | 123 | 123 (3 rows) postgres=#DROP TABLE c_tabl;

  If description is not NULL, the value of description is returned. Otherwise, the next parameter short_description is evaluated. If short_description is not NULL, the value of short_description is returned. Otherwise, the next parameter last_value is evaluated. If last_value is not NULL, the value of last_value is returned. Otherwise, (none) is returned.

  1 2 3 4 5

  postgres=#SELECT COALESCE(NULL,'Hello World'); coalesce --------------- Hello World (1 row)

• NULLIF

  Figure 4 shows the syntax of a NULLIF expression.

  Figure 4 nullif::=
  

  NULLIF returns NULL only if value1 equals value2. Otherwise, it returns value1.

  Example:

  1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17

  postgres=# CREATE TABLE null_if_t1 ( NI_VALUE1 VARCHAR(10), NI_VALUE2 VARCHAR(10) ) store AS orc; postgres=#INSERT INTO null_if_t1 VALUES('abc', 'abc'); postgres=#INSERT INTO null_if_t1 VALUES('abc', 'efg'); postgres=#SELECT NI_VALUE1, NI_VALUE2, NULLIF(NI_VALUE1, NI_VALUE2) FROM null_if_t1 ORDER BY 1, 2, 3; ni_value1 | ni_value2 | nullif -----------+-----------+-------- abc | abc | abc | efg | abc (2 rows) postgres=#DROP TABLE null_if_t1;

  Returns NULL if value1 equals value2. Otherwise, it returns value1.

  1 2 3 4 5

  postgres=#SELECT NULLIF('Hello','Hello World'); nullif -------- Hello (1 row)

• GREATEST (maximum value) and LEAST (minimum value)

  Figure 5 shows the syntax of a GREATEST expression.

  Figure 5 greatest::=
  

  It selects the largest value from a list of numeric expressions.

  1 2 3 4 5

  postgres=#SELECT greatest(9000,155555,2.01); greatest ---------- 155555 (1 row)

  Figure 6 shows the syntax of a LEAST expression.

  Figure 6 least::=
  

  It selects the smallest value from a list of numeric expressions.

  All numeric expressions must be convertible to a common data type, which becomes the result type.

  NULL values in the list are omitted. The result is NULL only if all expressions evaluate to NULL.

  1 2 3 4 5

  postgres=#SELECT least(9000,2); least ------- 2 (1 row)

  See Conditional Expression Functions for examples.

• NVL

  Figure 7 shows the syntax of an NVL expression.

  Figure 7 nvl::=
  

  If value1 is NULL, value2 is returned. If value1 is not NULL, value1 is returned.

  Example:

  1 2 3 4 5

  postgres=#SELECT nvl(null,1); nvl ----- 1 (1 row)

  1 2 3 4 5

  postgres=#SELECT nvl ('Hello World' ,1); nvl --------------- Hello World (1 row)

• IF

  Figure 8 shows the syntax of an IF expression.

  Figure 8 if::=
  

  If the value of bool_expr is true, expr1 is returned. Otherwise, expr2 is returned.

  See Conditional Expression Functions for examples.

• IFNULL

  Figure 9 shows the syntax of an IFNULL expression.

  Figure 9 ifnull::=
  

  If expr1 is not NULL, expr1 is returned. Otherwise, expr2 is returned.

  See Conditional Expression Functions for examples.