SQL - Conditional Functions

NULLIF

returns NULL if two expressions are equal, otherwise it returns the first expression.

Expression: NULLIF(expr1, expr2)
NULLIF('hello','hello')
-- returns NULL
NULLIF('hello','hellooo')
-- returns 'hello'

NVL

takes in 3 arguments and returns the 2nd if the first is not null, otherwise, it returns the 3rd argument

NVL2(expr1, expr2, expr3)
SELECT NVL2(col1, 'Not Null', 'Null') FROM table1;

DECODE

  • takes in a series of arguments and works like CASE or IF-THEN-ELSE logic
  • it decodes expression after comparing it to each search value
DECODE(col|expression, search1, result1 [, search2, result2,...,][, default])
SELECT DECODE(1,1,'Equal');
-- returns 'Equal'
 
SELECT DECODE(1,2, 'Equal');
-- returns NULL
 
SELECT DECODE(1, 2, 'Equal', 'Not Equal');
-- returns 'Not Equal'
 
SELECT DECODE(col1, 'A', 1,'B', 2,'C', 3, 4);
-- if col1 is A, return 1, else if col1 is B, return 2
-- else if col1 is C, return 3, else return 4
 
SELECT last_name, job_id, salary,
   DECODE(job_id, ’IT_PROG’, 1.10*salary,
    ’ST_CLERK’, 1.15*salary,
    ’SA_REP’, 1.20*salary, salary) REVISED_SALARY
FROM employees;

COALESCE

  • takes in multiple arguments and returns the first non-null value in the list
  • if the first expression is not null, it returns that expression; Otherwise, repeat for the next expression
COALESCE (expr_1, expr_2, ... expr_n)
SELECT last_name, COALESCE(commission_pct, salary, 10) comm
FROM employees ORDER BY commission_pct;