Lab 6.3 NULLIF and COALESCE Functions

Lab Objectives

After this Lab, you will be able to:

Use the NULLIF Function

Use the COALESCE Function

The NULLIF and COALESCE functions are defined by the ANSI 1999 standard to be "CASE abbreviations." Both functions can be used as a variety of the CASE expression.

NULLIF Function

The NULLIF function compares two expressions. If they are equal, then the function returns NULL; otherwise , it returns the value of the first expression. The NULLIF has the following structure:

 NULLIF (  expression1, expression2  ) 

If expression1 is equal to expression2, then NULLIF returns NULL. If expression1 does not equal expression2, NULLIF returns expression1. Note that the NULLIF function does the opposite of the NVL function. If the first expression is NULL, then NVL returns the second expression. If the first expression is not NULL, then NVL returns the first expression.

The NULLIF function is equivalent to the following CASE expression:

 CASE    WHEN  expression1 = expression2  THEN NULL    ELSE  expression1  END 

Consider the following example of NULLIF:

FOR EXAMPLE

 DECLARE    v_num NUMBER := &sv_user_num;    v_remainder NUMBER; BEGIN    -- calculate the remainder and if it is zero return a NULL    v_remainder := NULLIF(MOD(v_num,2),0);    DBMS_OUTPUT.PUT_LINE ('v_remainder: 'v_remainder); END; 

This is example is somewhat similar to an example that you have seen earlier in this chapter. A value is assigned to the variable v_num at run-time. Next , this value is divided by 2, and its remainder is compared to 0 via the NULLIF function. If the remainder equals 0, the NULLIF function returns NULL; otherwise it returns the remainder. The value returned by the NULLIF function is stored in the variable v_remainder and displayed on the screen via the DBMS_OUTPUT.PUT_LINE statement. When run, the example produces the output shown below. For the first run, 5 is assigned to the variable v_num :

  Enter value for sv_user_num: 5   old   2:    v_num NUMBER := &sv_user_num;   new   2:    v_num NUMBER := 5;   v_remainder: 1   PL/SQL procedure successfully completed.  

For the second run, 4 is assigned to the variable v_num :

  Enter value for sv_user_num: 4   old   2:    v_num NUMBER := &sv_user_num;   new   2:    v_num NUMBER := 4;   v_remainder:   PL/SQL procedure successfully completed.  

In the first run, 5 is not divisible by 2, and the NULLIF function returns the value of the remainder. In the second run, 4 is divisible by 2, and the NULLIF function returns NULL as the value of the remainder.

The NULLIF function has a restriction: You cannot assign a literal NULL to expression1. You learned about literals in Chapter 3. Consider another output produced by the preceding example. For this run, the variable v_num is assigned NULL:

  Enter value for sv_user_num: NULL   old   2:    v_num NUMBER := &sv_user_num;   new   2:    v_num NUMBER := NULL;   v_remainder:   PL/SQL procedure successfully completed.  

When NULL is assigned to the variable v_num , both the MOD and NULLIF functions return NULL. This example does not produce any errors because the literal NULL is assigned to the variable v_num , and it is not used as the first expression of the NULLIF function. Next, consider this modified version of the preceding example:

FOR EXAMPLE

 DECLARE    v_remainder NUMBER; BEGIN    -- calculate the remainder and if it is zero return a NULL  v_remainder := NULLIF(NULL,0);  DBMS_OUTPUT.PUT_LINE ('v_remainder: 'v_remainder); END; 

In the previous version of this example, the MOD function is used as expression1. In this version, the literal NULL is used in place of the MOD function, and as a result, this example produces the following syntax error:

  v_remainder := NULLIF(NULL,0);   *   ERROR at line 5:   ORA-06550: line 5, column 26:   PLS-00619: the first operand in the NULLIF expression must   not be NULL   ORA-06550: line 5, column 4:   PL/SQL: Statement ignored  

COALESCE Function

The COALESCE function compares each expression to NULL from the list of expressions and returns the value of the first non-null expression. The COALESCE function has the following structure:

 COALESCE (  expression1, expression2, , expressionN  ) 

If expression1 evaluates to NULL, then expression2 is evaluated. If expression2 does not evaluate to NULL, then the function returns expression2. If expression2 also evaluates to NULL, then the next expression is evaluated. If all expressions evaluate to NULL, the function returns NULL.

Note that the COALESCE function is like a nested NVL function:

 NVL(  expression1  , NVL(  expression2  , NVL(  expression3  ,...))) 

The COALESCE function can also be used as an alternative to a CASE expression. For example,

 COALESCE (  expression1, expression2  ) 

is equivalent to

 CASE    WHEN  expression1  IS NOT NULL THEN  expression1  ELSE  expression2  END 

If there are more than two expressions to evaluate, then

 COALESCE (  expression1, expression2, , expressionN  ) 

is equivalent to

 CASE    WHEN  expression1  IS NOT NULL THEN  expression1  ELSE COALESCE (  expression2  , ...,  expressionN  ) END 

Consider the following example of the COALESCE function:

FOR EXAMPLE

 SELECT e.student_id, e.section_id, e.final_grade,        g.numeric_grade,        COALESCE(e.final_grade, g.numeric_grade, 0) grade   FROM enrollment e, grade g  WHERE e.student_id = g.student_id    AND e.section_id = g.section_id    AND e.student_id = 102    AND g.grade_type_code = 'FI'; 

This SELECT statement returns the following output:

  STUDENT_ID SECTION_ID FINAL_GRADE NUMERIC_GRADE      GRADE   ---------- ---------- ----------- ------------- ----------   102         86                        85         85   102         89          92            92         92  

The value of GRADE equals the value of the NUMERIC_GRADE in the first row. The COALESCE function compares the value of the FINAL_GRADE to NULL. If it is NULL, then the value of the NUMERIC_GRADE is compared to NULL. Because the value of the NUMERIC_GRADE is not NULL, the COALESCE function returns the value of the NUMERIC_GRADE. The value of GRADE equals the value of FINAL_GRADE in the second row. The COALESCE function returns the value of FINAL_GRADE because it is not NULL.

The COALESCE function shown in the previous example is equivalent to the following NVL statement and CASE expression:

  NVL(e.final_grade, NVL(g.numeric_grade, 0))   CASE   WHEN e.final_grade IS NOT NULL THEN e.final_grade   ELSE COALESCE(g.numeric_grade, 0)   END  

The COALESCE function has the following restriction: At least one of its expressions must not contain a literal NULL. Consider the following example and its output:

FOR EXAMPLE

 SELECT COALESCE(NULL, 3, 8)   FROM DUAL;  COALESCE(NULL,3,8)   ------------------   3  

Next, consider this modified version of the same SELECT statement and the syntax error it generates

FOR EXAMPLE

 SELECT COALESCE(NULL, NULL, NULL)   FROM DUAL;  SELECT COALESCE(NULL, NULL, NULL)   *   ERROR at line 1:   ORA-00938: not enough arguments for function  

The SELECT statement causes a syntax error because all of the expressions in the COALESCE function contain the literal NULL.