NVL variations - NVL2, LNNVL, NULLIF, COALESCE

We all know about NVL, the SQL function that converts a value into another value if the value is null.  There are other variations on NVL that are not so well known - NVL2, LNNVL, NULLIF and COALESCE (some of these are undocumented features in older versions of Oracle)...
For completeness, Ill also include DECODE and CASE here as those function are also of interest in this discussion of null related functions

1)  NVL	NVL (COMMISSION, 0)
2)  NVL2	NVL2 ( string1, value_if_NOT_null, value_if_null) returns second parameter if value_1 is not null returns third parameter     if value_1 is null Example         SELECT NVL2 (COMMISSION, 'Commission','No Commission') This is equivelent to         DECODE (COMMISSION, NULL, 'No Commission', 'Commission'); NVL2 first appeared in the 9i documentation, but it appears in most (all ?) 8.x versions of Oracle as an undocumented feature. This function is not well understood, so it might be better for you to use CASE instead to avoid confusion
3)  LNNVL	LNNVL (condition) returns TRUE if the condition is FALSE or NULL return FALSE if the condition is TRUE There is something fairly unique about LNNVL - it is one of the few functions that can be used by SQL that use a BOOLEAN value or condition. The can contain compound logic including AND / OR as well as BETWEEN Documented in 10gr1 Exists in 8i, 9i as an undocumented feature. I would be careful using this feature as it appears to be unstable in 10gr1 and 10gr2.  Other people have reported that the following query crashes the SQL session - try it for yourself    ... SELECT * FROM DUAL WHERE NOT LNNVL(1=1) LNNVL is similar to IS NOT TRUE and IS FALSE in the SQL/Foundation 2003 SQL standard. LNNVL is similar to NOT, except NOT converts NULL to NULL LNNVL treats FALSE as NULL This function can only be used in the WHERE section, not in the SELECT <columns> section LNNVL was first documented in 10gr2 at least.  It works in earlier versions as an undocumented and unsupported feature (I have tested and verified it works in Oracle 8.1.7) Oracle Database sometimes uses the LNNVL function internally to rewrite NOT IN conditions as NOT EXISTS conditions Example    SELECT  *  from    EMP  where   LNNVL(SAL > 1000) This is the same as ...    SELECT *  from   EMP  where  NOT (SAL > 1000) Truth Table               NOT   LNNVL       ===== ===== TRUE  FALSE FALSE FALSE TRUE  TRUE NULL  NULL  TRUE This function  is not well understood, so it might be better to CASE or NOT to avoid confusion read more "This was added by Oracle as un undocumented feature to deal with problems arising from the SQL generated by a query co-ordinator for its parallel query slaves when Oracle was trying to get partition elimination on partition views."                                -  Johnathon Lewis
4)  NULLIF	NULLIF (A, B) return NULL if A = B, else return A SELECT NULLIF ('A', 'A'),  -- returns NULL        NULLIF ('A', ''),   -- returns A        NULLIF ('', 'A'),   -- returns NULL        NULLIF ('A', NULL), -- returns A        NULLIF ('', '')     -- returns NULL from DUAL
5)  COALESCE	COALESCE (my_variable, last_name, salary, <n>) returns the first not null value in the list SELECT COALESCE (null,null,30,null,40) -- this returns 30 FROM   DUAL SELECT COALESCE (SALARY, BONUS, COMM) FROM   EMP
6)  DECODE	One for the old school fans.  DECODE has gradually fallen out of use and CASE now tends to be used instead of DECODE.  SELECT decode (daily_rate               ,null ,'No Value'               ,1    ,'Low'               ,2    ,'High'               ,daily_rate               ) SELECT ename,        comm,        DECODE (comm,                NULL, 'No Commission',                'Commission'                ), from   emp CASE does not match NULL DECODE does matches NULL - old school works better !
7)  CASE	SELECT ename,        job,        CASE job           WHEN 'PRESIDENT' THEN 'Grand Pooh Bah'           WHEN 'CLERK'     THEN 'Worker Bee           ELSE 'Other Jobs'        END from   emp SELECT last_name,        CASE status          WHEN 'A' THEN 'Active'          WHEN 'I' THEN 'Inactive'          ELSE 'Unknown'        END from   employees There is a minor difference between CASE and DECODE CASE       does not match NULL DECODE matches NULL