In Oracle Database, NULL is not just a blank field. It represents a special concept—”an unknown or missing value.” Mishandling NULL values can lead to incorrect query results or calculation errors. Understanding how to handle NULL properly is essential for writing accurate and reliable SQL.
This article explains the nature of NULL, practical SQL examples, and key considerations using the SCOTT schema’s EMP table.
- ✅Setup: SCOTT Schema and Adding NULL Data
- What is NULL? ── Not Zero, Not an Empty String
- How to Search for NULL: Use IS NULL
- NULL Characteristics: Special Behavior in Comparison and Calculation
- IS NOT NULL: Exclude NULL Values
- NULL and LIKE: They Don’t Work Together
- Be Careful with NULL in Arithmetic
- Ordering NULLs: Control with NULLS FIRST or LAST
- Aggregation and NULL: It Affects the Result
- Tricky Behavior in WHERE Clauses with NULL
- ✅Summary: Handle NULL as “Unknown”
✅Setup: SCOTT Schema and Adding NULL Data
🔸Install the SCOTT Schema
Run the following to install the sample schema:
SQL> @?/rdbms/admin/utlsampl.sql
🔸Insert a Row with NULL Value in the EMP Table
INSERT INTO emp (empno, ename, job, mgr, hiredate, sal, deptno)
VALUES (9999, 'NULL_SAL', 'CLERK', 7902, TO_DATE('2024-11-01','YYYY-MM-DD'), NULL, 20);
What is NULL? ── Not Zero, Not an Empty String
In Oracle, NULL means “the value is unknown or not provided.” It’s completely different from 0 or ''.
┌──────────────┬───────────────────────┐
│ Value Type │ Meaning │
├──────────────┼───────────────────────┤
│ 0 │ The value is zero │
│ '' │ An empty string │
│ NULL │ Value is unknown │
└──────────────┴───────────────────────┘
How to Search for NULL: Use IS NULL
To find employees whose salary (sal) is not set:
SELECT empno, ename, sal
FROM emp
WHERE sal IS NULL;
📌 Example result:
EMPNO ENAME SAL
----- ---------- ----
9999 NULL_SAL
※ = NULL will not work (explained below).
NULL Characteristics: Special Behavior in Comparison and Calculation
NULL has unique behavior unlike regular values.
🔸Is NULL = NULL?
Actually, no. In Oracle:
NULL = NULL→ “Unknown”NULL != NULL→ “Unknown”NULL + 100→NULL
So this query returns nothing:
SELECT empno, ename
FROM emp
WHERE sal = NULL; -- Incorrect
The correct way:
SELECT empno, ename
FROM emp
WHERE sal IS NULL;
IS NOT NULL: Exclude NULL Values
To find employees who have a salary value:
SELECT empno, ename, sal
FROM emp
WHERE sal IS NOT NULL;
This excludes any rows where sal is NULL.
NULL and LIKE: They Don’t Work Together
Because NULL is not a string, pattern matching using LIKE doesn’t apply.
SELECT empno, ename
FROM emp
WHERE ename LIKE 'A%';
ename values that are NULL are ignored in this case.
Be Careful with NULL in Arithmetic
When a calculation involves NULL, the result becomes NULL.
SELECT empno, ename, sal, sal + 100 AS sal_with_bonus
FROM emp;
📌 Example:
EMPNO ENAME SAL SAL_WITH_BONUS
----- ---------- ----- ---------------
9999 NULL_SAL NULL
Always account for this behavior when writing calculations.
Ordering NULLs: Control with NULLS FIRST or LAST
Oracle lets you control where NULLs appear when sorting.
🔸Put NULLs at the Top
SELECT empno, ename, sal
FROM emp
ORDER BY sal NULLS FIRST;
Result: NULL_SAL appears first.
🔸Put NULLs at the Bottom
SELECT empno, ename, sal
FROM emp
ORDER BY sal NULLS LAST;
This improves the readability of sorted reports.
Aggregation and NULL: It Affects the Result
Example: Counting employees with salary per department:
SELECT deptno, COUNT(sal) AS num_of_employees
FROM emp
GROUP BY deptno;
📌 Note:
COUNT(sal)excludes NULL values.- Use
COUNT(*)to include all rows regardless of NULL.
DEPTNO NUM_OF_EMPLOYEES
------- ----------------
10 3
20 3
30 6
Tricky Behavior in WHERE Clauses with NULL
This query only finds rows where sal = 0, and does not include NULLs:
SELECT empno, ename, sal
FROM emp
WHERE sal = 0;
🔸To Include NULLs Too:
SELECT empno, ename, sal
FROM emp
WHERE sal = 0 OR sal IS NULL;
Make sure to explicitly include IS NULL where needed.
✅Summary: Handle NULL as “Unknown”
┌────────────────────────────────────────────┐
│ How to Handle NULL Correctly │
├────────────────────────────────────────────┤
│ • Use IS NULL / IS NOT NULL for comparisons │
│ • Calculations with NULL result in NULL │
│ • Aggregation functions may skip NULLs │
│ • Use NULLS FIRST/LAST to control ordering │
└────────────────────────────────────────────┘
NULL is not just “empty” — it’s a meaningful concept that represents “unknown.” Mastering NULL handling will greatly improve the accuracy and reliability of your SQL.
Try out these examples in the SCOTT schema and deepen your understanding!
[reference]
Nulls
コメント