1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Oracle NULL handling functions

Discussion in 'SQL PL/SQL' started by SBH, Nov 8, 2010.

  1. SBH

    SBH Forum Expert

    Messages:
    99
    Likes Received:
    6
    Trophy Points:
    160
    Location:
    Bangalore
    Overview

    NO VALUE state of a column is known as NULL in Oracle. It does not belongs to any data type family. The article explains the NULL handling functions provided by Oracle.

    Introduction

    In Oracle, NULL means NO VALUE. It can be understood as a state of a cell, which does not, has any value to store. Its handling is very crucial from programming point of view as it can produce hazardous results, if left unhandled.
    Below are few function provided by Oracle to bypass the effects of NULL value. Main objective of NULL functions is to provide an alternate value to the NULL valued column.

    1. NVL

    The NVL function returns an alternate fixed value for NULL columns.
    Syntax:
    Code (Text):
    NVL( Arg1, replace_with )
    NVL is an overloaded function, which can work with all types of data types. Data type of original column and the alternative must be in compatible state. Both the parameters are mandatory ones; else oracle throws error ORA-00909.

    Below SQL statement will display 'N/A' if the employee is not assigned any job i.e. JOB ID is NULL. Otherwise, it would return the JOB_ID value.

    Code (SQL):
    SELECT
    NVL(JOB, 'N/A')
    FROM EMPLOYEE
    Below SQL statement calculates the average of commission distributed to employees of department 10.
    Code (SQL):


    SELECT AVG(SALARY * NVL(COMM))
    FROM EMPLOYEE
    WHERE DEPARTMENT_ID = 10
    Above handling is very important, else it may yield wrong results.

    2. NVL2

    It allows user to provide alternate value not only for NULL columns values but also for NOT NULL columns. LONG is not supported type for the replacement value.

    Syntax
    Code (Text):
    NVL2( string1, value_if_NOT_null, value_if_null )
    Below SQL statement displays the status of an employee, which can be either JOB or BENCH

    Code (SQL):
    SQL> SELECT
    NVL2(JOB, 'JOB', 'BENCH')
    FROM EMPLOYEE

    3. NULLIF

    It compares two operands and returns NULL, if both satisfy equality condition, otherwise returns first operand. Unlike the other null handling function, first argument can’t be NULL. Both the parameters are mandatory for the function to execute.

    Syntax:
    Code (Text):
    NULLIF (expr1, expr2)
    The below query returns NULL since both the input values, 12 are equal.

    Code (SQL):
    SQL> SELECT
    NULLIF (12, 12) RESULT
    FROM DUAL;

    RESULT
    ------

     
    Similarly, below query return ‘SUN’ since both the strings are not equal.

    Code (SQL):
    SQL> SELECT
    NULLIF ('SUN', 'MOON') RESULT
    FROM DUAL

    RESULT
    ------------
    SUN
    4. COALESCE

    COALESCE function is again a granule form of NVL as it returns the first non-null operand in the argument list. It can take minimum two mandatory parameters but upper bound has no limit.
    Syntax
    Code (Text):
    COALESCE (expr1, expr2, ... expr_n )
    Below query returns the first Not Null address line of an Employee

    Code (SQL):
    SELECT
    COALESCE (address1, address2, address3) RESULT
    FROM   EMPLOYEE;
    Above query can be also be written using IF...ELSE conditions as below

    Code (SQL):
    IF address1 IS NOT NULL THEN
         RESULT := address1;
    ELSIF address2 IS NOT NULL THEN
        RESULT := address2;
    ELSIF address3 IS NOT NULL THEN
        RESULT := address3;
    ELSE
        RESULT := NULL;
    END IF;

    5. LNNVL

    The function was introduced in Oracle 10g in undocumented version.
    It evaluates a condition in the WHERE clause, including NULL values of any of the columns used as operand.

    Syntax:
    Code (Text):
    LNNVL(Condition)
    It returns BOOLEAN value based on below truth table.

    [TABLE]Condition logical result LNNVL output
    TRUE FALSE
    FALSE TRUE
    UKNOWN TRUE[/TABLE]

    Example

    Below SQL displays the employee id and name for those whose commission on salary exceeds 1000. Purpose of LNNVL is that if commission is NULL for any employee, it would be considered and would be displayed in the final result

    Code (SQL):
    SELECT EMPLOYEE_ID, ENAME
    FROM EMPLOYEE
    WHERE LNNVL(SAL * COMM > 1000)
     
  2. kiran.marla

    kiran.marla Forum Genius

    Messages:
    403
    Likes Received:
    52
    Trophy Points:
    505
    Location:
    Khammam
    My million dollar question is , where we use NULLIF in real time?
    Can you provide one example here??
     
  3. SBH

    SBH Forum Expert

    Messages:
    99
    Likes Received:
    6
    Trophy Points:
    160
    Location:
    Bangalore
    Best scenario for NULLIF is handling of ORA-01476 i.e. Divide by zero exception. Take a look

    Code (SQL):
    CREATE OR REPLACE FUNCTION F_DIV(A NUMBER, B NUMBER)
    RETURN NUMBER
    IS
    BEGIN
    RETURN A/B;
    END;
    F_DIV(10,0) will raise ORA-01476:divide by zero exception.

    Instead, if NULLIF is handled in the function, it would bypass the exception by giving NULL output.

    Code (SQL):
    CREATE OR REPLACE FUNCTION F_DIV(A NUMBER, B NUMBER)
    RETURN NUMBER
    IS
    BEGIN
    RETURN (A/NULLIF(B,0));
    END;
    F_DIV(10,0) would return NULL, instead of raising exception.


    It can be achieved by other ways too, but this shows thereal time use of NULLIF