SQL Conditional Expressions

Conditional Expressions Summary Table

Function Description
CASE Case operator
COALESCE Return the first non-NULL argument
GREATEST Returns the largest value from a list of any number of expressions
IFNULL NULL IF/ELSE construct
IN Check whether a value is within a set of values
LEAST Returns the smallest value from a list of any number of expressions
NULLIF Return NULL if expr1 = expr2

CASE

Syntax

CASE WHEN condition THEN result     
    [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:

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.

Example

SELECT CASE 1 WHEN 1 THEN 'one'
    WHEN 2 THEN 'two'
    ELSE 'more'
END
FROM dual;
      
 ---
 one  
 

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, 'something else') FROM t ORDER BY COALESCE(a, b, c, 'something else');
 --------------
 something else
 1             
 4             
 6             

GREATEST

Syntax

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

Description

Returns the largest value from a list of two or more expressions. Comparison is case sensitive.

If the list of expressions are not all the same data type, expressions in the list will be converted to the data type of the first expression for comparison. Returns an SQL error if any expressions in the list cannot be converted to the data type of the first expression.

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;
    unable to convert string "abc" into type "double"

IFNULL

Syntax

IFNULL(expr1,expr2) 

Description

If expr1 is not NULL, IFNULL() returns expr1; otherwise it returns expr2. IFNULL() returns a numeric or string value, depending on the context in which it is used.

Example

SELECT IFNULL(1,0) FROM dual;
 -
 1
 
SELECT IFNULL(NULL,10) FROM dual;
 --
 10
 
SELECT 1.0000/10.0000, IFNULL(1.000/null,1.000/10.0000), IFNULL(1.000/null,10.0000), IFNULL(null/10.000,1.0000) FROM dual;
 ----- ----- ------- ------
 .1000 .1000 10.0000 1.0000

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.

Like COALESCE and CASE, WHERE , IN does 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

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. Comparison is case sensitive.

If the list of expressions are not all the same data type, expressions in the list will be converted to the data type of the first expression for comparison. Returns an SQL error if any expressions in the list cannot be converted to the data type of the first 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 more LOSSES and TIES than WINS */
 SELECT COUNT(*) FROM hockey.teams
  WHERE LEAST(wins, losses, ties) = wins;
 COUNT 
 ------
   20
 
SELECT LEAST(1,2,'abc') FROM DUAL;
  unable to convert string "abc" into type "double"

NULLIF

Syntax

NULLIF(value1, value2) 

Description

Returns a null value if value1 and value2 are equal; otherwise it returns value1

Example

CREATE TABLE t (a int, b int);
INSERT INTO t VALUES (null, null), (null, 1), (1, null), (1, 1);
SELECT COALESCE (a, 'null') as a,
       COALESCE (b, 'null') as b,
       COALESCE (nullif (a, b), 'null') AS nullif FROM t;
 
  A    B   NULLIF 
 ---- ---- -------
 null null  null  
 null 1     null  
 1    null  1     
 1    1     null