A Comprehensive Guide to NULLIF and COALESCE Functions: Practical Applications

1. Introduction

Handling NULL values is unavoidable when working with Oracle databases. This article provides a detailed explanation of two convenient functions for manipulating NULL values—NULLIF and COALESCE—covering everything from the basics to business applications.

2. The NULLIF Function

What is NULLIF?

NULLIF is a simple function that returns NULL if two values are equal, and returns the first value as is if they are different.

Illustration

Value A: 3000 | Value B: 3000 ↓ If Equal Result: NULL

Value A: 3000 | Value B: 2000 ↓ If Different Result: 3000

Syntax

NULLIF(expr1, expr2)

Use Cases

Identifying and invalidating duplicate data.
Excluding default values.

Practical Example: Eliminating Duplicate Values

As an example, if the salary (salary) and commission (commission) are the same, treat the commission as NULL (effectively flagging it or ignoring the duplication).

SELECT 
    emp_id,
    emp_name,
    salary,
    commission,
    NULLIF(salary, commission) AS adjusted_commission
FROM 
    employees;

3. The COALESCE Function

What is COALESCE?

COALESCE evaluates multiple values in order and returns the first non-NULL value.

Illustration

Value A: NULL | Value B: 2000 | Value C: 3000 ↓ Choose the first non-NULL value Result: 2000

Syntax

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

Use Cases

Setting default values.
Prioritizing dynamic values.

Practical Example: Alternative Value When Commission is Missing

An example where the salary is used if the commission is not set, and if that is also NULL, 0 is returned.

SELECT 
    emp_id,
    emp_name,
    salary,
    commission,
    COALESCE(commission, salary, 0) AS final_value
FROM 
    employees;

4. Preparing Sample Table and Data

Use the following SQL to create the employees table and insert data.

Creating the Table

CREATE TABLE employees (
    emp_id NUMBER,
    emp_name VARCHAR2(50),
    salary NUMBER,
    commission NUMBER
);

Inserting Data

INSERT INTO employees (emp_id, emp_name, salary, commission) VALUES (1, 'John', 3000, NULL);
INSERT INTO employees (emp_id, emp_name, salary, commission) VALUES (2, 'Jane', 4000, 500);
INSERT INTO employees (emp_id, emp_name, salary, commission) VALUES (3, 'Alice', NULL, NULL);
INSERT INTO employees (emp_id, emp_name, salary, commission) VALUES (4, 'Bob', 2000, 300);
COMMIT;

5. Execution Results

Execute the following SQL and check the results.

Result of NULLIF

SELECT 
    emp_id,
    emp_name,
    salary,
    commission,
    NULLIF(salary, commission) AS adjusted_commission
FROM 
    employees;

SQL> SELECT
  2      emp_id,
  3      emp_name,
  4      salary,
  5      commission,
  6      NULLIF(salary, commission) AS adjusted_commission
  7  FROM
  8      employees;

    EMP_ID EMP_NAME                 SALARY COMMISSION ADJUSTED_COMMISSION
---------- -------------------- ---------- ---------- -------------------
         1 John                       3000                           3000
         2 Jane                       4000        500                4000
         3 Alice
         4 Bob                        2000        300                2000

Explanation of Results: NULLIF The key point here is the criterion: “Are the two values equal?” The expression NULLIF(salary, commission) operates with the following logic:

If values differ: Returns the value of the 1st argument (salary) as is.
If values are equal: Returns NULL.

In this data, since there were no employees where salary and commission were the same amount, the result basically displays the salary.

Case of John, Jane, Bob: Since salary and commission values are different (or one is NULL), the value of the 1st argument salary (3000, 4000, 2000) is displayed as is in the ADJUSTED_COMMISSION column.
Case of Alice: The 1st argument salary itself is NULL. If the 1st argument is NULL, NULLIF returns NULL without performing a comparison.

This function is extremely useful in cases where you want to “invalidate (nullify) specific values.”

Result of COALESCE

SELECT 
    emp_id,
    emp_name,
    salary,
    commission,
    COALESCE(commission, salary, 0) AS final_value
FROM 
    employees;

SQL> SELECT
  2      emp_id,
  3      emp_name,
  4      salary,
  5      commission,
  6      COALESCE(commission, salary, 0) AS final_value
  7  FROM
  8      employees;

    EMP_ID EMP_NAME                 SALARY COMMISSION FINAL_VALUE
---------- -------------------- ---------- ---------- -----------
         1 John                       3000                   3000
         2 Jane                       4000        500         500
         3 Alice                                                0
         4 Bob                        2000        300         300

Explanation of Results: COALESCE This is a powerful function that “searches for and adopts a non-NULL value.” The expression COALESCE(commission, salary, 0) checks values in order from left to right and returns the first “non-NULL” value found. Imagine it searching for candidates like “1st choice, 2nd choice, if not, then 3rd choice…”.

Executing this SQL yields the following results:

Case of Jane, Bob (People with commission): It checks commission first. Since there is a value here (500, 300), that value is adopted immediately. The subsequent salary is ignored.
Case of John (Person without commission): commission is NULL. So it looks at the next candidate, salary. Since there is a value of 3000, this is adopted.
Case of Alice (Person with neither salary nor commission): This is the true value of COALESCE.
1. commission is NULL → Next
2. salary is also NULL → Next
3. Adopts 0, which is the last resort! As a result, 0 is returned. A major benefit of COALESCE is that it can be written much more cleanly than nesting NVL functions.

6. Application Examples in Business

Case 1: Bonus Calculation

Bonus is 10% of salary, but prioritize commission if it exists.

SELECT 
    emp_id,
    emp_name,
    COALESCE(commission, salary * 0.1, 0) AS bonus
FROM 
    employees;

SQL> SELECT
  2      emp_id,
  3      emp_name,
  4      COALESCE(commission, salary * 0.1, 0) AS bonus
  5  FROM
  6      employees;

    EMP_ID EMP_NAME                  BONUS
---------- -------------------- ----------
         1 John                        300
         2 Jane                        500
         3 Alice                         0
         4 Bob                         300

Case 2: Organizing Customer Data

Display “No Information” if contact information is missing.

SELECT 
    customer_id,
    COALESCE(email, phone, 'No Information') AS contact_info
FROM 
    customers;

7. Points to Note and Best Practices

Notes on NULLIF Be careful, as returning NULL may cause errors in subsequent calculations.

Performance of COALESCE If there are many arguments, it evaluates everything until the first non-NULL value is found, so performance needs to be considered.

8. Summary

NULLIF and COALESCE are powerful tools for handling NULL values in databases. By utilizing them appropriately, you can improve data quality and operational efficiency. Please try incorporating them into your practice using this article as a reference!

[reference]
Oracle Database SQL Language Reference, 19c