NULL functions ( NVL, NVL2, NULLIF and COALESCE) in Oracle

SQL> COLUMN RESULT FORMAT A10
SQL> SELECT NVL('X','Y') AS RESULT FROM DUAL;

RESULT
----------
X

SQL> SELECT NVL(NULL,'Y') AS RESULT FROM DUAL;

RESULT
----------
Y

SQL> SELECT NVL(NULL,NULL) AS RESULT FROM DUAL;

RESULT
----------


SQL>
SQL> SELECT NVL2('X','Y','Z') AS RESULT FROM DUAL;

RESULT
----------
Y

SQL> SELECT NVL2(NULL,'Y','Z') AS RESULT FROM DUAL;

RESULT
----------
Z

SQL> SELECT NVL2('X',NULL,'Z') AS RESULT FROM DUAL;

RESULT
----------


SQL> SELECT NVL2(NULL,'Y',NULL) AS RESULT FROM DUAL;

RESULT
----------


SQL> -- both parameters are same then returns NULL else first parameter as output
SQL> SELECT NULLIF('X','X') AS RESULT FROM DUAL;

RESULT
----------


SQL> SELECT NULLIF('X','Y') AS RESULT FROM DUAL;

RESULT
----------
X

SQL> SELECT NULLIF(NULL,'Y') AS RESULT FROM DUAL;
SELECT NULLIF(NULL,'Y') AS RESULT FROM DUAL
              *
ERROR at line 1:
ORA-00932: inconsistent datatypes: expected - got CHAR


SQL> SELECT NULLIF('X',NULL) AS RESULT FROM DUAL;

RESULT
----------
X

SQL> -- returns first not null parameter value
SQL> SELECT COALESCE('A','B','C','D') AS RESULT FROM DUAL;

RESULT
----------
A

SQL> SELECT COALESCE(NULL,'B','C','D') AS RESULT FROM DUAL;

RESULT
----------
B

SQL> SELECT COALESCE(NULL,NULL,'C','D') AS RESULT FROM DUAL;

RESULT
----------
C

SQL> SELECT COALESCE(NULL,NULL,NULL,'D') AS RESULT FROM DUAL;

RESULT
----------
D

SQL> SELECT COALESCE(NULL,NULL,NULL,NULL) AS RESULT FROM DUAL;

RESULT
----------


SQL>