Which of the following KEYWORDS will return the first NON-NULL value from a list of columns?
Explanation
COALESCE function evaluates the arguments one by one and returns the value of the first expression that does not evaluate to NULL.
Theory
  • General Functions

    The objective of the general NULL handling functions is to replace the NULL values with an alternate value.
    • NVL - The NVL function substitutes an alternate value for a NULL value.
    • NVL2 - As an enhancement over NVL, Oracle introduced a function to substitute value not only for NULL columns values but also for NOT NULL columns.
    • NULLIF - The NULLIF function compares two arguments expr1 and expr2. If expr1 and expr2 are equal, it returns NULL; else, it returns expr1. First argument can't be NULL.
    • COALESCE - COALESCE function, a more generic form of NVL, returns the first non-null expression in the argument list.

    Read more: Using Conversion Functions
  • COALESCE

    COALESCE function, a more generic form of NVL, returns the first non-null expression in the argument list. It takes minimum two mandatory parameters but maximum arguments has no limit.

    Syntax

    COALESCE (expr1, expr2, ... expr_n )
    
    Consider the below SELECT query. It selects the first not null value fed into address fields for an employee.
    SELECT COALESCE (address1, address2, address3) Address
    FROM  employees;
    
    The working of COALESCE function is similar to IF..ELSIF..ENDIF construct. The query above can be re-written as:
    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;
    
    Read more: Conv. Func.

Follow CodeGalaxy

Mobile Beta

Get it on Google Play
Send Feedback
Keep exploring
SQL quizzes
Cosmo
Sign Up Now
or Subscribe for future quizzes