5.3 General NULL-Handling Functions

5.3 General NULL-Handling Functions

In relational database theory and Oracle SQL, NULL represents a state of missing, unknown, unassigned, or inapplicable data. Because NULL does not represent a known value, standard arithmetic operations and comparison operators exhibit distinct three-valued logic ($TRUE$, $FALSE$, $UNKNOWN$):

• Any arithmetic operation involving NULL yields NULL (e.g., salary + commission_pct yields NULL if commission is null).
• Standard equality comparisons against NULL yield UNKNOWN rather than TRUE (e.g., commission_pct = NULL never matches any row).

To manage null values safely in calculations, reports, and logical predicates, Oracle SQL provides four general NULL-handling functions: NVL, NVL2, NULLIF, and COALESCE.

---

1. The NVL Function

The NVL(expr1, expr2) function replaces a NULL value with a meaningful alternate value.

                          +------------------------+
                          |    NVL(expr1, expr2)   |
                          +------------------------+
                                      |
                     +----------------+----------------+
                     |                                 |
             [expr1 IS NOT NULL]                 [expr1 IS NULL]
                     |                                 |
                     v                                 v
              Returns: expr1                    Returns: expr2

Syntax & Datatype Rules:

NVL(expr1, expr2)

1. If expr1 is NOT NULL, NVL returns expr1.
2. If expr1 is NULL, NVL returns expr2.
3. Datatype Compatibility Rule: The datatypes of expr1 and expr2 must be compatible. If they differ, Oracle implicitly converts expr2 to the datatype of expr1 before returning a value.

-- Calculating total compensation (replacing NULL commission with 0):
SELECT employee_id, 
       salary + (salary * NVL(commission_pct, 0)) AS total_compensation
FROM   employees;

-- Datatype coercion example: '100' is implicitly converted to NUMBER (datatype of expr1):
SELECT NVL(100, '200') FROM dual; --> Returns 100 (NUMBER)

-- Datatype mismatch error: Oracle tries to convert 'None' to NUMBER and fails:
SELECT NVL(commission_pct, 'None') FROM employees;
-- Error: ORA-01722: invalid number

-- Correct approach with explicit conversion:
SELECT NVL(TO_CHAR(commission_pct), 'None') FROM employees;

Exam Watchout: NVL does not short-circuit. Both expr1 and expr2 are always evaluated by the SQL engine, even when expr1 is not null.

---

2. The NVL2 Function

The NVL2(expr1, expr2, expr3) function provides three-parameter conditional substitution based on whether the first expression is null or not null.

                       +------------------------------+
                       |   NVL2(expr1, expr2, expr3)  |
                       +------------------------------+
                                      |
                     +----------------+----------------+
                     |                                 |
             [expr1 IS NOT NULL]                 [expr1 IS NULL]
                     |                                 |
                     v                                 v
              Returns: expr2                    Returns: expr3

Syntax & Datatype Rules:

NVL2(expr1, expr2, expr3)

1. If expr1 is NOT NULL, NVL2 returns expr2.
2. If expr1 is NULL, NVL2 returns expr3.
3. Datatype Rule: expr1 can be of any datatype. The return datatype of the function is determined by expr2:
   • If expr2 is numeric, expr3 is converted to numeric.
   • If expr2 is character data, expr3 is converted to character (VARCHAR2).

-- Displaying compensation status:
SELECT last_name, 
       salary, 
       NVL2(commission_pct, 'Salary + Comm', 'Salary Only') AS income_type,
       NVL2(commission_pct, salary + (salary * commission_pct), salary) AS net_pay
FROM   employees;

Exam Trap: In NVL2, the second parameter (expr2) corresponds to the NOT NULL state, and the third parameter (expr3) corresponds to the NULL state. Do not invert the arguments!

---

3. The NULLIF Function

The NULLIF(expr1, expr2) function compares two expressions and returns NULL if they are equal, or expr1 if they are not equal.

                          +------------------------+
                          |   NULLIF(expr1, expr2) |
                          +------------------------+
                                      |
                     +----------------+----------------+
                     |                                 |
               [expr1 = expr2]                  [expr1 != expr2]
                     |                                 |
                     v                                 v
               Returns: NULL                    Returns: expr1

Syntax & Critical Restrictions:

NULLIF(expr1, expr2)

1. If expr1 equals expr2, NULLIF returns NULL.
2. If expr1 does not equal expr2, NULLIF returns expr1.
3. The Literal NULL Restriction: The first argument expr1 CANNOT be the literal keyword NULL. Writing NULLIF(NULL, expr2) causes an immediate syntax error (ORA-00932: inconsistent datatypes or ORA-00904).
4. expr1 and expr2 must be of comparable datatypes.

Practical Applications of NULLIF:

• Preventing Division-by-Zero Errors (ORA-01476):

  SELECT department_id,
         total_sales / NULLIF(staff_count, 0) AS sales_per_rep
  FROM   department_stats;
  

  If staff_count is 0, NULLIF(staff_count, 0) returns NULL, and total_sales / NULL safely yields NULL instead of crashing the query with a division-by-zero error.

• Cleansing Sentinel / Default Values:

  -- Converting dummy text 'N/A' into true NULL:
  SELECT customer_id, NULLIF(phone_number, 'N/A') AS clean_phone 
  FROM customers;
  

---

4. The COALESCE Function

The COALESCE(expr1, expr2, ..., exprn) function evaluates a list of expressions from left to right and returns the first non-null expression encountered.

+-------------------------------------------------------------------------+
|                   COALESCE(expr1, expr2, expr3, ..., exprN)             |
+-------------------------------------------------------------------------+
|  Evaluate expr1 -> NOT NULL? ------> RETURN expr1                       |
|         | (is NULL)                                                     |
|         v                                                               |
|  Evaluate expr2 -> NOT NULL? ------> RETURN expr2                       |
|         | (is NULL)                                                     |
|         v                                                               |
|  Evaluate expr3 -> NOT NULL? ------> RETURN expr3                       |
|         | (is NULL)                                                     |
|         v                                                               |
|  ... All NULL? --------------------> RETURN NULL                        |
+-------------------------------------------------------------------------+

Syntax & Characteristics:

COALESCE(expr1, expr2, ..., exprn)

1. Requires at least two arguments.
2. Returns the value of the first expression that does not evaluate to NULL.
3. If all arguments evaluate to NULL, COALESCE returns NULL.
4. Short-Circuit (Lazy) Evaluation: COALESCE stops evaluating expressions as soon as a non-null value is found. Expressions appearing after the first non-null argument are never executed, offering performance savings and error prevention.
5. Datatype Consistency: All expressions in the parameter list must share a compatible datatype with the first expression (expr1).

-- Cascading contact lookup:
SELECT customer_id,
       COALESCE(mobile_phone, business_phone, home_phone, 'No Contact Available') AS contact_info
FROM   customer_contacts;

---

Comprehensive NULL-Handling Functions Comparison