In Oracle Database, NULL signifies that a “value is unknown” or in a “state of absence.” This is different from “zero” and indicates a state where no data has been entered. This article provides a detailed explanation of the characteristics and usage of NULL, along with specific SQL examples and business scenarios for handling NULL correctly.
- Characteristics of NULL
- SQL Samples Related to NULL
- Summary
Characteristics of NULL
- Exclusion from Calculations NULL is ignored in calculations, but the result of any calculation involving NULL becomes NULL. For example, the result of 5 + NULL is NULL.
- Unequal Identity NULL is not considered equal to any other value, including another NULL. Therefore, the result of the expression NULL = NULL is not FALSE, but UNKNOWN.
- Requires IS NULL or IS NOT NULL To evaluate NULL, you must use
IS NULLorIS NOT NULL. Standard comparison operators (= or !=) will not evaluate NULL correctly. - Behavior in Arithmetic Expressions The result of an arithmetic expression involving NULL is always NULL.
- Setting Default Values To avoid NULL, it is common to specify a default value using functions like
NVLorCOALESCE. - Performance Perspective Queries that frequently evaluate columns containing NULL may not be able to use indexes effectively, so attention to performance is necessary.
SQL Samples Related to NULL
The following are actual samples using the SCOTT schema. The SCOTT sample schema can be imported and used by executing the following:
$ sqlplus / as sysdba
SQL> @?/rdbms/admin/utlsampl.sql
$ sqlplus scott/tiger
Searching for Data Containing NULL
SELECT empno, ename, comm
FROM emp
WHERE comm IS NULL;
This query searches for employees whose comm column is NULL.
[Execution Result]
SQL> SELECT empno, ename, comm
2 FROM emp
3 WHERE comm IS NULL;
EMPNO ENAME COMM
---------- ------------------------------ ----------
7369 SMITH
7566 JONES
7698 BLAKE
7782 CLARK
7839 KING
7900 JAMES
7902 FORD
7934 MILLER
8 rows selected.
When Data is NOT NULL
SELECT empno, ename, comm
FROM emp
WHERE comm IS NOT NULL;
This searches for employees who have a value in the comm column.
[Execution Result]
SQL> SELECT empno, ename, comm
2 FROM emp
3 WHERE comm IS NOT NULL;
EMPNO ENAME COMM
---------- ------------------------------ ----------
7499 ALLEN 300
7521 WARD 500
7654 MARTIN 1400
7844 TURNER 0
Using the NVL Function
The NVL function allows you to specify a substitute value for NULL.
SELECT empno, ename, NVL(comm, 0) AS comm_with_default
FROM emp;
This query returns 0 if the comm column is NULL.
[Execution Result]
SQL> SELECT empno, ename, NVL(comm, 0) AS comm_with_default
2 FROM emp;
EMPNO ENAME COMM_WITH_DEFAULT
---------- ------------------------------ -----------------
7369 SMITH 0
7499 ALLEN 300
7521 WARD 500
7566 JONES 0
7654 MARTIN 1400
7698 BLAKE 0
7782 CLARK 0
7839 KING 0
7844 TURNER 0
7900 JAMES 0
7902 FORD 0
7934 MILLER 0
12 rows selected.
Using the COALESCE Function
The COALESCE function returns the first non-NULL value in a list of expressions.
SELECT empno, ename, COALESCE(comm, 0, sal) AS effective_comm
FROM emp;
If comm is NULL, the value of sal is returned. If both are NULL, 0 is returned.
[Execution Result]
SQL> SELECT empno, ename, COALESCE(comm, 0, sal) AS effective_comm
2 FROM emp;
EMPNO ENAME EFFECTIVE_COMM
---------- ------------------------------ --------------
7369 SMITH 0
7499 ALLEN 300
7521 WARD 500
7566 JONES 0
7654 MARTIN 1400
7698 BLAKE 0
7782 CLARK 0
7839 KING 0
7844 TURNER 0
7900 JAMES 0
7902 FORD 0
7934 MILLER 0
12 rows selected.
Evaluating Data Containing NULL
When evaluating data conditionally based on NULL, use DECODE or CASE.
DECODE Example
SELECT empno, ename, DECODE(comm, NULL, 'No Commission', 'Has Commission') AS comm_status
FROM emp;
This query displays ‘No Commission’ if comm is NULL.
[Execution Result]
SQL> SELECT empno, ename, DECODE(comm, NULL, 'No Commission', 'Has Commission') AS comm_status
2 FROM emp;
EMPNO ENAME COMM_STATUS
---------- ------------------------------ ------------------------------------------
7369 SMITH No Commission
7499 ALLEN Has Commission
7521 WARD Has Commission
7566 JONES No Commission
7654 MARTIN Has Commission
7698 BLAKE No Commission
7782 CLARK No Commission
7839 KING No Commission
7844 TURNER Has Commission
7900 JAMES No Commission
7902 FORD No Commission
7934 MILLER No Commission
12 rows selected.
CASE Statement Example
SELECT empno, ename,
CASE WHEN comm IS NULL THEN 'No Commission'
ELSE 'Has Commission'
END AS comm_status
FROM emp;
A similar implementation is possible using a CASE statement.
[Execution Result]
SQL> SELECT empno, ename,
2 CASE WHEN comm IS NULL THEN 'No Commission'
3 ELSE 'Has Commission'
4 END AS comm_status
5 FROM emp;
EMPNO ENAME COMM_STATUS
---------- ------------------------------ ------------------------------------------
7369 SMITH No Commission
7499 ALLEN Has Commission
7521 WARD Has Commission
7566 JONES No Commission
7654 MARTIN Has Commission
7698 BLAKE No Commission
7782 CLARK No Commission
7839 KING No Commission
7844 TURNER Has Commission
7900 JAMES No Commission
7902 FORD No Commission
7934 MILLER No Commission
12 rows selected.
Arithmetic Expressions Involving NULL
You must be careful, as arithmetic expressions involving NULL will result in NULL. An example is shown below.
SELECT empno, ename, sal + comm AS total_salary
FROM emp;
If comm is NULL, the result of sal + comm will be NULL.
[Execution Result]
SQL> SELECT empno, ename, sal, comm
2 FROM emp;
EMPNO ENAME SAL COMM
---------- ------------------------------ ---------- ----------
7369 SMITH 800
7499 ALLEN 1600 300
7521 WARD 1250 500
7566 JONES 2975
7654 MARTIN 1250 1400
7698 BLAKE 2850
7782 CLARK 2450
7839 KING 5000
7844 TURNER 1500 0
7900 JAMES 950
7902 FORD 3000
7934 MILLER 1300
12 rows selected.
SQL> SELECT empno, ename, sal + comm AS total_salary
2 FROM emp;
EMPNO ENAME TOTAL_SALARY
---------- ------------------------------ ------------
7369 SMITH
7499 ALLEN 1900
7521 WARD 1750
7566 JONES
7654 MARTIN 2650
7698 BLAKE
7782 CLARK
7839 KING
7844 TURNER 1500
7900 JAMES
7902 FORD
7934 MILLER
12 rows selected.
This can be avoided by using the NVL function.
SELECT empno, ename, sal + NVL(comm, 0) AS total_salary
FROM emp;
This performs the calculation by substituting 0 when comm is NULL.
[Execution Result]
SQL> SELECT empno, ename, sal + NVL(comm, 0) AS total_salary
2 FROM emp;
EMPNO ENAME TOTAL_SALARY
---------- ------------------------------ ------------
7369 SMITH 800
7499 ALLEN 1900
7521 WARD 1750
7566 JONES 2975
7654 MARTIN 2650
7698 BLAKE 2850
7782 CLARK 2450
7839 KING 5000
7844 TURNER 1500
7900 JAMES 950
7902 FORD 3000
7934 MILLER 1300
12 rows selected.
Aggregate Functions and NULL
When aggregating data that includes NULL, NULL values are ignored. However, COUNT(*) counts all records, including those with NULL.
SUM Function Example
SELECT SUM(comm) AS total_commission
FROM emp;
If comm is NULL, those values are ignored.
[Execution Result]
SQL> SELECT empno, ename, comm
2 FROM emp;
EMPNO ENAME COMM
---------- ------------------------------ ----------
7369 SMITH
7499 ALLEN 300
7521 WARD 500
7566 JONES
7654 MARTIN 1400
7698 BLAKE
7782 CLARK
7839 KING
7844 TURNER 0
7900 JAMES
7902 FORD
7934 MILLER
12 rows selected.
SQL> SELECT SUM(comm) AS total_commission
2 FROM emp;
TOTAL_COMMISSION
----------------
2200
COUNT Function Example
SELECT COUNT(comm) AS non_null_comm, COUNT(*) AS total_rows
FROM emp;
COUNT(comm) excludes NULLs, while COUNT(*) counts all rows.
[Execution Result]
SQL> SELECT COUNT(comm) AS non_null_comm, COUNT(*) AS total_rows
2 FROM emp;
NON_NULL_COMM TOTAL_ROWS
------------- ----------
4 12
Examples of NULL Usage in Business Scenarios
Sales Management System
In a system that manages sales data, it is necessary to apply a default value if the commission is NULL.
SELECT order_id, customer_id, NVL(commission, 0) AS commission_amount
FROM sales;
If the commission is not set, 0 is applied to simplify calculations.
Updating Employee Information
Setting a default value for columns with NULL values in an employee database.
UPDATE emp
SET comm = NVL(comm, 0)
WHERE comm IS NULL;
This sets the comm column to 0 for all employees where it is currently NULL.
Creating Monthly Reports
Performing calculations while ignoring NULLs when aggregating monthly sales.
SELECT TO_CHAR(order_date, 'YYYY-MM') AS month, SUM(NVL(sales_amount, 0)) AS total_sales
FROM sales
GROUP BY TO_CHAR(order_date, 'YYYY-MM');
If sales are NULL, they are aggregated as 0 to generate accurate monthly data.
Visual Aid
The following diagram illustrates the flow of NULL handling using a CASE statement.
+-----------+ IS NULL? +----------------+
| VALUE | ----------------------> | Return 'X' |
+-----------+ +----------------+
|
| IS NOT NULL
v
+----------------+
| Return 'Y' |
+----------------+
Summary
- NULL is an important concept in Oracle Database.
- Understanding how to use NULL correctly in calculations and evaluations allows you to build accurate SQL.
- Utilize
NVLandCOALESCEto handle NULL appropriately. - Understanding the behavior of aggregate functions and the handling of arithmetic expressions leads to more accurate data processing.
- Let’s streamline data management by referencing real-world business scenarios for using NULL.
- It is recommended to learn by getting hands-on practice with the SCOTT schema tables. With this, you have firmly acquired knowledge about NULL. Try it out with actual data!
[reference]
Oracle Database SQL Language Reference, 19c
コメント