In Oracle SQL, NULL means a value is absent or unavailable—not zero, an empty string, or an ordinary value you can compare with =. Use IS NULL or IS NOT NULL to test it, and decide deliberately whether a missing value should be replaced or prohibited.
What does NULL mean in Oracle?
Oracle describes SQL NULL as typically representing absent information: data that is missing, unknown, or inapplicable. SQL does not distinguish which of those reasons applies. That distinction matters to your application and data model, because a missing commission and a commission that is not applicable may need different business treatment even though both can be stored as NULL. See Oracle’s JSON Developer’s Guide for its explanation of SQL NULL.
| # | Preview | Product | Price | |
|---|---|---|---|---|
| 1 |
|
Oracle SQL and Pl/Sql | $50.50 | Buy on Amazon |
| 2 |
|
Mastering Oracle SQL, 2nd Edition | $20.80 | Buy on Amazon |
| 3 |
|
Murach's Oracle SQL and PL/SQL for Developers | $32.28 | Buy on Amazon |
| 4 |
|
Oracle SQL By Example (Prentice Hall PTR Oracle) | $36.48 | Buy on Amazon |
| 5 |
|
SQL Pocket Guide: A Guide to SQL Usage | $21.34 | Buy on Amazon |
NULL is not zero or an ordinary value
A numeric zero is a known number; NULL indicates that there is no SQL value available. Because NULL is not an ordinary value, comparisons such as commission_pct = NULL do not test for it. A condition involving NULL can evaluate to unknown rather than true or false.
Oracle treats a zero-length character value as NULL
For Oracle SQL character values, a zero-length string is treated as NULL. This can affect applications and migrations from databases that distinguish an empty string from a missing value. Oracle’s documentation describes this behavior in its JSON Developer’s Guide.
What’s actually slowing this PC down?
Pick the symptom - the matching free tool is one click away.
#1 Best Overall
How do you check for NULL in Oracle SQL?
Use IS NULL to find rows with no value and IS NOT NULL to find rows with a value. For example, this query returns employees whose commission percentage is missing:
SELECT employee_id
FROM employees
WHERE commission_pct IS NULL;To find employees with a commission percentage, change the predicate to WHERE commission_pct IS NOT NULL. Oracle-base also summarizes these predicates in its NULL-Related Functions reference.
Rank #2
Why not use equals?
WHERE commission_pct = NULL does not match rows with NULL commission values. The comparison is unknown, so the row does not satisfy the WHERE condition. Use the dedicated NULL predicate instead.
How do NVL and COALESCE handle NULL?
Fallback functions substitute another expression when an input is NULL. Which fallback is appropriate depends on what NULL means for the data, not just on what makes an expression easier to calculate.
Rank #3
| Function | Typical use | Behavior |
|---|---|---|
NVL(a, b) |
A common two-expression fallback | Returns the fallback expression when the first expression is NULL. |
COALESCE(a, b, 0) |
Selecting from several candidate expressions | Returns the first non-NULL expression in the list. |
For example, you can replace a missing commission with zero for a particular calculation:
SELECT salary + NVL(commission_pct, 0) AS adjusted_value
FROM employees;This changes the calculation’s interpretation: it treats an absent commission as zero. Do that only if zero is the intended business meaning, rather than using it automatically for every missing value. The Oracle-base NULL-Related Functions reference covers practical NULL-related functions. Oracle Analytics Cloud’s Pixel-Perfect Reports guide also shows COALESCE in a multi-value parameter pattern.
Rank #4
For display names, for example, you might use the first available candidate:
SELECT COALESCE(nickname, preferred_name, legal_name) AS display_name
FROM people;Can a CHECK constraint allow NULL?
Yes. In Oracle, a CHECK constraint does not reject a row when its condition evaluates to unknown because of NULL; a check is violated only when its condition is false. Thus, CHECK (salary > 0) by itself does not prohibit a NULL salary. Oracle’s Maintaining Data Integrity in Database Applications explains the treatment of unknown check conditions.
Best Value
Require presence separately from a valid range
If salary must both exist and be greater than zero, declare both requirements:
salary NUMBER NOT NULL CHECK (salary > 0)NOT NULL prohibits NULL values; the CHECK rule enforces the range for values that are present. If neither NULL nor NOT NULL is specified for a column, Oracle defaults to allowing NULL, as described in the same Oracle data-integrity guide.
Is JSON null the same as SQL NULL?
No. SQL NULL is the absence of an SQL value. JSON null is a scalar value in the JSON data format and can exist inside a non-NULL SQL value that contains JSON. Oracle documents that SQL IS NULL returns false for such a JSON null, while IS NOT NULL returns true. Therefore, SQL NULL predicates test the SQL value; they do not by themselves test whether JSON content contains the JSON literal null. See Oracle’s JSON Developer’s Guide.
Quick Recap
Product prices and availability are accurate as of the date/time indicated and are subject to change. Any price and availability information displayed on Amazon at the time of purchase will apply.
