NULL Handling

To follow along with the examples in this lesson, use the course dataset from Lesson 8.

What NULL Is

NULL means the absence of a value — not zero, not an empty string, not a space. It means the value is unknown, missing, or not applicable. A product with no rating does not have a rating of 0 — it has no rating at all. A customer with no phone number does not have an empty string — the phone number is simply absent. This distinction matters enormously because NULL behaves differently from every other value in comparisons, arithmetic, and aggregation.

NULL is not a value you can compare with = or !=. Any comparison involving NULL returns neither true nor false but a third state: UNKNOWN. Oracle's WHERE clause only keeps rows where the condition is TRUE. Rows where the condition is UNKNOWN are silently excluded — exactly as if the condition were FALSE. This catches developers constantly: WHERE rating = NULL returns zero rows every time, regardless of the data.

• NULL means unknown or absent — not zero, not empty string, not false
• NULL = NULL evaluates to UNKNOWN, not TRUE — two unknowns are not considered equal
• Any arithmetic with NULL returns NULL — 100 + NULL = NULL, NULL * 5 = NULL
• The only correct way to test for NULL is IS NULL and IS NOT NULL
• Aggregate functions SUM(), AVG(), MIN(), MAX() silently skip NULLs — COUNT(*) does not skip them, but COUNT(column) does
• Oracle and SQL Server behave identically for NULL logic — the differences are in the substitution functions

-- IS NULL and IS NOT NULL are the only correct operators for testing NULL.
-- WHERE rating = NULL always returns zero rows -- = NULL evaluates to UNKNOWN not TRUE.
SELECT product_name, rating
FROM   dataplexa_store.products
WHERE  rating = NULL;          -- WRONG: always returns zero rows
SELECT product_name, rating
FROM   dataplexa_store.products
WHERE  rating IS NULL;         -- CORRECT: returns rows where rating has no value
SELECT product_name, rating
FROM   dataplexa_store.products
WHERE  rating IS NOT NULL
ORDER  BY rating DESC;

• WHERE rating = NULL returns zero rows every time — this is one of the most common SQL mistakes across all databases
• IS NULL and IS NOT NULL are the only operators that correctly detect missing values
• Oracle says rows selected — SQL Server says rows affected

NULL in Arithmetic and String Concatenation

Any arithmetic involving NULL produces NULL. price * NULL, stock + NULL, 100 / NULL — every one returns NULL silently. Oracle's string concatenation operator is ||. Unlike SQL Server where concatenating with NULL produces NULL by default, Oracle treats NULL as an empty string in || concatenation — so 'Hello' || NULL returns 'Hello', not NULL. This is one of the few places where Oracle and SQL Server behave differently with NULL.

SELECT
    product_name,
    rating,
    price,
    price * rating                        AS raw_score,     -- NULL if rating is NULL
    price * NVL(rating, 0)               AS safe_score,    -- NVL replaces NULL with 0 before multiplying
                                                            -- NVL is Oracle's equivalent of SQL Server's ISNULL
    NVL(TO_CHAR(rating), 'No rating')    AS display_rating, -- TO_CHAR converts number to string first
                                                             -- NVL then provides the fallback text
    'Product: ' || product_name          AS label,          -- || is Oracle string concatenation
    'Rating: ' || TO_CHAR(rating)        AS rating_label    -- NULL rating produces 'Rating: ' in Oracle
                                                             -- SQL Server + operator would return NULL instead
FROM   dataplexa_store.products
ORDER  BY rating DESC NULLS LAST;   -- NULLS LAST pushes NULL ratings to the bottom
                                    -- Oracle default for ASC is NULLS LAST
                                    -- Oracle default for DESC is NULLS FIRST -- override with NULLS LAST

• price * rating returns NULL for the two unrated products — NULL in any arithmetic poisons the entire expression
• NVL(rating, 0) substitutes 0 before the multiplication — Oracle equivalent of SQL Server's ISNULL(rating, 0)
• 'Rating: ' || TO_CHAR(rating) returns 'Rating: ' when rating is NULL — Oracle treats NULL as empty string in || concatenation, unlike SQL Server's + operator which returns NULL
• NULLS LAST pushes NULL values to the bottom of a DESC sort — without it, Oracle's DESC default puts NULLs first

NULL in Aggregates — The Silent Skip

All aggregate functions except COUNT(*) silently skip NULL values. AVG(rating) divides by the count of non-NULL rows, not the total row count — so the average can appear higher than expected. COUNT(*) counts every row including those with NULL in any column. COUNT(column) counts only rows where that column is not NULL. Understanding this difference prevents silent errors in reports.

SELECT
    COUNT(*)                      AS total_rows,        -- all rows -- NULL values in columns are irrelevant
    COUNT(rating)                 AS rows_with_rating,  -- only rows where rating is not NULL
    COUNT(*) - COUNT(rating)      AS missing_ratings,   -- clean pattern for counting NULL occurrences
    SUM(rating)                   AS sum_rating,        -- adds only the non-NULL rating values
    AVG(rating)                   AS avg_rating,        -- divides by COUNT(rating) not COUNT(*) -- skips NULLs
    SUM(NVL(rating, 0)) / COUNT(*) AS avg_with_zero,   -- manual average treating NULL as 0 -- lower result
    MIN(rating)                   AS min_rating,
    MAX(rating)                   AS max_rating
FROM   dataplexa_store.products;

• COUNT(*) returns 7 — all rows — while COUNT(rating) returns 5 — only rows where rating is not NULL
• AVG(rating) divides 22.30 by 5, giving 4.46 — the two NULL rows are excluded from both the sum and the divisor
• The manual average treating NULL as 0 gives 3.19 — very different — choose the behaviour that is correct for your report
• COUNT(*) - COUNT(rating) is a clean pattern for counting how many rows have a missing value in any column

NVL, NVL2, COALESCE, and NULLIF

Oracle provides four functions for working with NULL. NVL(expression, replacement) is Oracle's two-argument NULL substitution function — the direct equivalent of SQL Server's ISNULL(). NVL2(expression, if_not_null, if_null) is Oracle-only — it returns one value when the expression is not NULL and a different value when it is. COALESCE(val1, val2, ...) is the ANSI-standard function supported by both Oracle and SQL Server — it accepts any number of arguments and returns the first non-NULL. NULLIF(a, b) returns NULL when two values are equal and the first value otherwise — used most often to prevent divide-by-zero.

-- NVL: Oracle equivalent of SQL Server's ISNULL -- two arguments only
SELECT
    product_name,
    NVL(brand, 'Unbranded')      AS brand,    -- 'Unbranded' when brand is NULL
    NVL(rating, 0.0)             AS rating    -- 0.0 when rating is NULL
FROM   dataplexa_store.products;
-- NVL2: Oracle-only -- no SQL Server equivalent
-- Returns second argument when expression is NOT NULL, third when it IS NULL
SELECT
    product_name,
    NVL2(rating, 'Rated', 'Unrated')  AS rating_status  -- 'Rated' if rating exists, 'Unrated' if NULL
FROM   dataplexa_store.products;
-- COALESCE: ANSI standard -- works in both Oracle and SQL Server
-- Returns the first non-NULL value from the list
SELECT
    first_name,
    last_name,
    COALESCE(phone_number, email, 'No contact info')  AS contact_method
FROM   dataplexa_store.customers;
-- NULLIF: returns NULL when the two arguments are equal -- prevents divide-by-zero
-- Oracle raises ORA-01476 on divide-by-zero -- NULLIF converts 0 to NULL so division returns NULL instead
SELECT
    category,
    SUM(total_amount)                          AS revenue,
    COUNT(*)                                   AS orders,
    SUM(total_amount) / NULLIF(COUNT(*), 0)    AS avg_order_value  -- safe -- returns NULL if orders = 0
FROM   dataplexa_store.orders o
JOIN   dataplexa_store.order_items oi ON o.order_id = oi.order_id
GROUP  BY category;

• NVL(rating, 0.0) is Oracle's two-argument equivalent of SQL Server's ISNULL(rating, 0.0)
• NVL2(rating, 'Rated', 'Unrated') has no SQL Server equivalent — it branches on whether the first argument is NULL or not
• COALESCE(phone_number, email, 'No contact info') tries each argument left to right and returns the first non-NULL — identical behaviour in Oracle and SQL Server
• NULLIF(COUNT(*), 0) returns NULL when the count is zero — dividing by NULL returns NULL instead of raising ORA-01476

NULL in JOINs and WHERE with AND / OR

NULL values in JOIN columns never match anything — including other NULLs. A row with NULL in the join column will silently disappear from an INNER JOIN. A LEFT JOIN preserves it but returns NULL for every column from the right table. In WHERE conditions, NULL's three-valued logic produces surprising results: TRUE AND UNKNOWN = UNKNOWN, FALSE OR UNKNOWN = UNKNOWN — so a condition involving a NULL column can silently exclude rows you expected to include.

-- Products with NULL category_id disappear silently from an INNER JOIN.
-- LEFT JOIN preserves them -- NVL then provides a readable fallback.
SELECT
    p.product_name,
    p.price,
    NVL(c.category_name, 'Uncategorised')  AS category   -- NVL replaces NULL from unmatched LEFT JOIN rows
FROM   dataplexa_store.products    p
LEFT JOIN dataplexa_store.categories c ON p.category_id = c.category_id;
-- WITHOUT the OR IS NULL check, unrated products are silently excluded
SELECT product_name, rating
FROM   dataplexa_store.products
WHERE  rating < 4.5;               -- NULL rating evaluates to UNKNOWN -- row silently excluded
-- CORRECT: explicitly include NULL rows
SELECT product_name, rating
FROM   dataplexa_store.products
WHERE  rating < 4.5
   OR  rating IS NULL;             -- now unrated products are included

• INNER JOIN silently drops Standing Desk and USB-C Hub — their NULL category_id matches nothing in the categories table
• LEFT JOIN preserves them — NVL(c.category_name, 'Uncategorised') then replaces the NULL with a readable label
• Without OR rating IS NULL, the two unrated products are silently excluded from the WHERE result — no error, just missing data
• NOT IN (subquery) returns zero rows if the subquery contains even one NULL — use NOT EXISTS as the safe alternative in Oracle exactly as in SQL Server

Lesson Summary

Practice Questions

Quiz

Next up — Table Relationships — How primary keys, foreign keys, and cardinality define the connections between tables and enforce data integrity in Oracle.