In Oracle Database, the biggest cause of discrepancies in SQL aggregation and calculation results lies in the handling of “NULL”. Since arithmetic operations involving NULL in Oracle result in NULL for everything, appropriate handling using NVL or NVL2 functions is essential.
This article explains everything from the basics of NULL replacement to usage in calculation formulas and workarounds for common errors, using sample data that works on an actual machine.
Conclusion/Quick Study: Distinguishing between NVL and NVL2
Use the following functions for NULL countermeasures depending on the situation.
- NVL Function: Performs a simple replacement (Basic)
- Syntax:
NVL(target_column, replacement_value_if_null) - Use Case: Convert to ‘0’ or ‘Not Applicable’ if NULL.
- Syntax:
- NVL2 Function: Branches processing based on whether it is NULL or not (Applied)
- Syntax:
NVL2(target_column, return_value_if_not_null, return_value_if_null) - Use Case: Display ‘Available’ if there is a value, ‘None’ if not, etc.
- Syntax:
- Note: The data type of the replacement value must match the target column (or be implicitly convertible).
- 1. Basic Knowledge of NULL Values and Arithmetic Expressions
- 2. Practice: Creating Sample Data
- 3. Avoiding Calculation Errors with the NVL Function
- 4. Conditional Branching with the NVL2 Function
- 5. Troubleshooting (Common Errors)
- 6. Operational/Implementation Cautions and Best Practices
- 7. FAQ: Frequently Asked Questions
- 8. Summary
1. Basic Knowledge of NULL Values and Arithmetic Expressions
What is NULL Propagation?
NULL in Oracle Database refers to “a state where no value exists (unknown).” Therefore, performing a calculation like 100 + NULL results in NULL, not 100. This is called “NULL propagation.”
Oracle Master Exam Tip:
SQL> set null '(null)'
SQL> select 100+NULL from dual;
100+NULL
----------
(null)
Due to this characteristic, accidents occur where total values disappear if some columns are NULL in sales calculations or payroll calculations. The NVL function prevents this.
2. Practice: Creating Sample Data
We will create a table EMPLOYEE_DATA for explanation.
You can execute this as is in your verification environment (Oracle 19c, etc.).
Prerequisites:
The connecting user must have CREATE TABLE and UNLIMITED TABLESPACE privileges on the target schema.
-- Delete if table exists (Be careful in a real environment)
-- DROP TABLE EMPLOYEE_DATA PURGE;
-- 1. Create Table
CREATE TABLE EMPLOYEE_DATA (
EMP_ID NUMBER PRIMARY KEY, -- Employee ID
EMP_NAME VARCHAR2(50), -- Employee Name
SALARY NUMBER, -- Salary
BONUS NUMBER -- Bonus (Allows NULL)
);
-- 2. Insert Data (Patterns including NULL)
INSERT INTO EMPLOYEE_DATA VALUES (1, 'Alice', 5000, 300); -- Has both
INSERT INTO EMPLOYEE_DATA VALUES (2, 'Bob', NULL, 200); -- Salary is NULL
INSERT INTO EMPLOYEE_DATA VALUES (3, 'Charlie', 4000, NULL); -- Bonus is NULL
INSERT INTO EMPLOYEE_DATA VALUES (4, 'Diana', NULL, NULL); -- Both are NULL
INSERT INTO EMPLOYEE_DATA VALUES (5, 'Eve', 6000, 400);
INSERT INTO EMPLOYEE_DATA VALUES (6, 'Frank', NULL, 100);
-- 3. Commit
COMMIT;
-- 4. Check Current State
SELECT * FROM EMPLOYEE_DATA ORDER BY EMP_ID;
Result:
SQL> set null '(null)'
SQL> SELECT * FROM EMPLOYEE_DATA ORDER BY EMP_ID;
EMP_ID EMP_NAME SALARY BONUS
---------- -------------------- ---------- ----------
1 Alice 5000 300
2 Bob (null) 200
3 Charlie 4000 (null)
4 Diana (null) (null)
5 Eve 6000 400
6 Frank (null) 100
6 rows selected.
3. Avoiding Calculation Errors with the NVL Function
First, let’s compare the case where calculations are done without caring for NULL versus using NVL.
Execution Example: Calculating Total of Salary + Bonus
SELECT
EMP_ID,
EMP_NAME,
-- [NG Example] If added as is, result disappears if either is NULL
SALARY + BONUS AS TOTAL_NG,
-- [OK Example] Convert NULL to 0 with NVL then add
NVL(SALARY, 0) + NVL(BONUS, 0) AS TOTAL_OK
FROM EMPLOYEE_DATA
ORDER BY EMP_ID;
Result:
SQL> set null '(null)'
SQL> SELECT
2 EMP_ID,
3 EMP_NAME,
4 SALARY + BONUS AS TOTAL_NG,
5 NVL(SALARY, 0) + NVL(BONUS, 0) AS TOTAL_OK
6 FROM EMPLOYEE_DATA
7 ORDER BY EMP_ID;
EMP_ID EMP_NAME TOTAL_NG TOTAL_OK
---------- -------------------- ---------- ----------
1 Alice 5300 5300
2 Bob (null) 200
3 Charlie (null) 4000
4 Diana (null) 0
5 Eve 6400 6400
6 Frank (null) 100
6 rows selected.
Execution Result and Explanation
EMP_ID EMP_NAME TOTAL_NG TOTAL_OK
------ ---------- ---------- ----------
1 Alice 5300 5300 <- Matches as both have values
2 Bob (null) 200 <- NULL+200=NULL(NG) / 0+200=200(OK)
3 Charlie (null) 4000 <- 4000+NULL=NULL(NG) / 4000+0=4000(OK)
4 Diana (null) 0 <- Becomes 0 even if both are NULL
...
Explanation: NVL(BONUS, 0) performs the process “If BONUS is NULL, return 0; otherwise, return the value of BONUS.” This makes the arithmetic operation valid.
4. Conditional Branching with the NVL2 Function
NVL2 is convenient when you want to return completely different values for “when there is a value” and “when it is NULL.”
Execution Example: Calculating Allowance Based on Bonus Existence
- Person with bonus: Bonus amount + 500 (Special addition)
- Person without bonus: Flat 0
SELECT
EMP_ID,
EMP_NAME,
BONUS,
-- If 1st arg (BONUS) is not NULL return 2nd arg, if NULL return 3rd arg
NVL2(BONUS, BONUS + 500, 0) AS ADJUSTED_BONUS
FROM EMPLOYEE_DATA
ORDER BY EMP_ID;
Result:
SQL> set null '(null)'
SQL> SELECT
2 EMP_ID,
3 EMP_NAME,
4 BONUS,
5 NVL2(BONUS, BONUS + 500, 0) AS ADJUSTED_BONUS
6 FROM EMPLOYEE_DATA
7 ORDER BY EMP_ID;
EMP_ID EMP_NAME BONUS ADJUSTED_BONUS
---------- -------------------- ---------- --------------
1 Alice 300 800
2 Bob 200 700
3 Charlie (null) 0
4 Diana (null) 0
5 Eve 400 900
6 Frank 100 600
6 rows selected.
Execution Result
EMP_ID EMP_NAME BONUS ADJUSTED_BONUS
------ ---------- ------ --------------
1 Alice 300 800 <- 300 + 500
2 Bob 200 700 <- 200 + 500
3 Charlie (null) 0 <- 0 because it is NULL
...
Point: Although it can be written with DECODE or CASE statements, NVL2 can be written more simply because it specializes in NULL checking.
5. Troubleshooting (Common Errors)
Here are common errors encountered when using NVL/NVL2 and how to deal with them.
| Error Code | Cause | Solution |
| ORA-01722: invalid number | Specified a string that cannot be converted (e.g., ‘None’) as the replacement value for a numeric column. | Make the replacement value a number, or convert the target column to a string with TO_CHAR. |
| Data Type Mismatch | The types of the 1st and 2nd arguments differ (e.g., specifying numeric 0 for replacement of a date type column). | Match the types. If it’s a date type, use SYSDATE or a date literal. |
Example resulting in error (Type Mismatch)
-- Specifying a string for replacement value even though SALARY is NUMBER type
SELECT NVL(SALARY, 'Unpaid') FROM EMPLOYEE_DATA;
-- Result: ORA-01722: invalid number
Correction Proposal: If for display purposes, convert the left side to a string.
SELECT NVL(TO_CHAR(SALARY), 'Unpaid') FROM EMPLOYEE_DATA;
6. Operational/Implementation Cautions and Best Practices
Pros and Cons
- Pros: SQL becomes concise, reducing NULL check processing on the application side.
- Cons: Being a proprietary function, modification may be required when migrating to other DBMSs (PostgreSQL, MySQL, etc.) in the future.
Difference from COALESCE Function (Important)
The standard SQL (ANSI) compliant COALESCE function behaves similarly, but with the following differences:
- Number of arguments: NVL takes up to 2. COALESCE allows multiple (returns the first non-NULL value from the left).
- Evaluation Process (Performance):
- NVL: Evaluates (calculates) the 2nd argument even if the 1st argument is not NULL.
- COALESCE: If the 1st argument is non-NULL, it does not evaluate the 2nd argument (Short-circuit).
- Note: If putting heavy function processing in the 2nd argument,
COALESCEmight be faster.
Avoidance via Table Design (Recommended)
Rather than doing NVL every time in SQL, it is preferable from the perspective of data integrity and performance to add a DEFAULT 0 constraint and impose a NOT NULL constraint at the time of table design.
-- Recommended Table Definition Example
CREATE TABLE NEW_EMP (
SALARY NUMBER DEFAULT 0 NOT NULL,
BONUS NUMBER DEFAULT 0 NOT NULL
);
7. FAQ: Frequently Asked Questions
Q1. Can I use NVL inside NVL? (Nesting)
A. Yes, it is possible. However, if you want to check multiple candidate values in order, using COALESCE(colA, colB, colC, 0) is more readable and standard.
Q2. Can empty strings (”) be replaced with NVL?
A. Yes. In Oracle Database, since “empty strings (string of length 0)” are treated as equivalent to NULL, NVL(”, ‘Replacement’) returns ‘Replacement’. (Note that behavior differs in other DBs).
Q3. Does using NVL in the WHERE clause make it slow?
Oracle Master Exam Tip:
A. Indexes may become ineffective. If you do WHERE NVL(COL, 0) = 0, the index on the COL column is not used. To avoid this, you need to devise a plan such as creating a function-based index or changing the condition expression to WHERE COL IS NULL OR COL = 0.
8. Summary
- NVL is a basic function that converts NULL to a specific value (0 or string) to prevent calculation errors and display omissions.
- NVL2 allows simple writing of branching logic for “when present” and “when absent.”
- Type matching is mandatory. Attempting to replace a numeric item with a string results in an error.
- Consider using COALESCE if you are conscious of standard SQL.
NULL handling is the first step to accurate data aggregation. Start by mastering the NVL(column, 0) pattern.
[reference]
Oracle Database SQL Language Reference, 19c
コメント