Vol. I · No. 47 Saturday, 21 November 2026 Wah Cantt, Pakistan
Oracle with Hassan

No. 46 PL/SQL & SQL

Oracle SQL NULL: 5 Rules for Reliable Queries

Use five practical rules to explain missing rows, avoid NOT IN surprises, choose the right COUNT, and verify your null policy with small SQL examples.

Oracle SQL NULL: 5 Rules for Reliable Queries — Oracle with Hassan

Oracle SQL NULL behavior can change which rows a report returns without raising an error. A status filter removes blank statuses. An exclusion query returns nothing after somebody adds one incomplete record. A dashboard total changes when COUNT(*) becomes COUNT(status). These problems share a cause: a missing value changes the meaning of a SQL expression.

This walkthrough uses Oracle Database 19c semantics and small, read-only examples. Start by deciding what missing data means in the business rule. Then write the predicate and count that express that decision. Replacing every NULL with zero or an empty label can hide the distinction you need to preserve.

1. Understand Oracle SQL NULL and three-valued logic

A condition can be TRUE, FALSE, or UNKNOWN. A WHERE clause keeps rows for which its condition is TRUE. Consequently, status <> ‘CLOSED’ does not automatically mean “everything except closed, including missing statuses.” A missing status produces UNKNOWN for that comparison and is excluded. Oracle documents these rules in Nulls and Logical Conditions.

Negation does not recover the missing rows: NOT UNKNOWN remains UNKNOWN. AND and OR still matter. FALSE AND UNKNOWN is FALSE, while TRUE OR UNKNOWN is TRUE. Rather than memorizing every combination, write an explicit IS NULL branch whenever your requirement includes missing values.

WITH owh_eg_values (id, status) AS (
  SELECT 1, CAST('OPEN' AS VARCHAR2(10)) FROM dual UNION ALL
  SELECT 2, 'CLOSED' FROM dual UNION ALL
  SELECT 3, CAST(NULL AS VARCHAR2(10)) FROM dual UNION ALL
  SELECT 4, '' FROM dual
)
SELECT id,
       CASE WHEN status = 'OPEN' THEN 'TRUE'
            WHEN NOT (status = 'OPEN') THEN 'FALSE'
            ELSE 'UNKNOWN' END AS comparison
FROM owh_eg_values
ORDER BY id;

Expected result: ID 1 is TRUE, ID 2 is FALSE, and IDs 3 and 4 are UNKNOWN. The CASE expression makes an otherwise invisible comparison result readable. These outputs matched the recorded companion test on Oracle AI Database 26ai; repeat them on your target environment.

Oracle 19c treats a zero-length character value as NULL. That is why the fourth row behaves like the third. A single space is a different value, and numeric zero is also different. Oracle cautions against assuming empty-string equivalence for future releases, so document this as Oracle behavior rather than a portable SQL rule.

2. Write the missing-value policy directly

Test an Oracle SQL NULL directly with status IS NULL to find missing statuses, or status IS NOT NULL to find populated ones. Neither status = NULL nor status != NULL performs that test. If a work queue includes both open and unclassified records, use status = ‘OPEN’ OR status IS NULL. Parenthesize that expression before combining it with another condition, such as a department filter.

Be equally careful with display defaults. COALESCE(status, ‘Unclassified’) can be useful in a report column, but using that label as a stored or filtered sentinel creates another business value. The same warning applies to NVL(amount, 0): an unknown amount becomes indistinguishable from a confirmed zero. Choose that behavior only when the calculation is intended to treat missing amounts as zero.

3. Reproduce the NOT IN trap

WITH owh_eg_candidates (id) AS (
  SELECT 1 FROM dual UNION ALL SELECT 2 FROM dual UNION ALL
  SELECT 3 FROM dual
), owh_eg_exclusions (id) AS (
  SELECT 2 FROM dual UNION ALL SELECT CAST(NULL AS NUMBER) FROM dual
)
SELECT c.id
FROM owh_eg_candidates c
WHERE c.id NOT IN (SELECT e.id FROM owh_eg_exclusions e)
ORDER BY c.id;

Expected result: no rows. For candidate 1, the condition effectively requires both 1 != 2 and 1 != NULL. The second comparison is UNKNOWN, so the row does not pass. Candidate 2 is excluded by its known match, and candidate 3 encounters the same unknown comparison as candidate 1. This example concerns a single-column NOT IN; do not generalize it carelessly to composite comparisons.

When the requirement is “keep candidates for which no matching exclusion exists,” a correlated NOT EXISTS is clearer. It checks whether a matching row exists using the equality condition inside the subquery. A NULL exclusion does not match a known candidate ID. Oracle explains the underlying membership behavior in the IN condition reference.

-- Replace the final SELECT in the preceding CTE example:
SELECT c.id
FROM owh_eg_candidates c
WHERE c.id IS NOT NULL
  AND NOT EXISTS (
    SELECT 1
    FROM owh_eg_exclusions e
    WHERE e.id = c.id
  )
ORDER BY c.id;

Expected result: IDs 1 and 3. The outer IS NOT NULL states that an unknown candidate ID is ineligible. Without that predicate, NOT EXISTS would also retain a NULL candidate because ordinary equality would find no matching exclusion. That may be valid for another business rule, but it should be deliberate.

Filtering NULLs from the NOT IN subquery is another possible solution. Keep the outer null policy explicit as well, especially because an empty exclusion set behaves differently from a populated set. Prefer the version that communicates the actual requirement; neither syntax is a universal performance winner. Compare execution plans and representative data before making a performance claim.

4. Choose the count that answers the question

-- Use the first owh_eg_values CTE with this SELECT:
SELECT COUNT(*) AS total_rows,
       COUNT(status) AS known_statuses,
       COUNT(DISTINCT status) AS distinct_statuses,
       COUNT(*) - COUNT(status) AS missing_statuses,
       COUNT(CASE WHEN status = 'OPEN' THEN 1 END) AS open_rows
FROM owh_eg_values;

Expected result: 4 total rows, 2 known statuses, 2 distinct populated statuses, 2 missing statuses, and 1 open row. COUNT(expression) counts non-null expression results; COUNT(*) counts rows. COUNT(DISTINCT status) does not invent a separate NULL category. These distinctions are defined in Oracle’s COUNT reference.

Oracle SQL NULL behavior also explains why the conditional count deliberately omits ELSE. Rows that are not open produce NULL and are not counted. Adding ELSE 0 would make every CASE result non-null and therefore count every row. In a dashboard, name metrics precisely: “Tickets” and “Tickets with a status” are different measures even when today’s data makes them equal.

5. Verify Oracle SQL NULL edge cases before using the query

Run the supplied examples without changing existing objects; their OWH_EG names are statement-local common table expressions. There is no setup DDL, transaction to commit, or cleanup DROP. Then add a NULL candidate, remove every exclusion, and repeat the tests. Check missing statuses, empty strings, a literal space, duplicate statuses, and a completely empty input set.

Finally, review joins and aggregates together. An outer join can introduce NULLs that were absent from the base table. COUNT(*) may then count the preserved parent row while COUNT(child_id) counts only matching children, assuming child_id is non-null for real children. A correct filter and a misleading aggregate can coexist. Verify the row-level result first, then the displayed total.

Keep these Oracle SQL NULL cases in a regression test whenever an exclusion table or optional column changes. A new NOT NULL constraint can simplify a query, while removing one can invalidate an assumption that was previously safe. The practical question is whether the schema guarantees the value, whether the query guarantees it, or whether neither does. Record that answer beside the business rule.

Prepared with AI-assisted research and drafting. The companion examples were executed on Oracle AI Database 26ai Enterprise Edition 23.26.1.0.0 through APEX 26.1.0 on October 13, 2026; expected outputs matched. Oracle Database 19c is the documentation baseline, not an executed test environment.

Further reading

Full archive →
  1. 45 PL/SQL & SQL ORA-01821: Date Format Not Recognized – Causes and Fixes 14 min
  2. 38 APEX Plugins Oracle APEX Signature Verification Fails in 3 Shocking Ways 10 min
  3. 32 Oracle APEX APEXlang Tutorial: How to Edit, Validate, and Import Oracle APEX Apps Without Touching Page Designer 12 min
  4. 31 Oracle Database Stop Running These 5 SQL Queries on Your Production Database 12 min