NULL Handling

NULL represents a missing or unknown value.

Concept

NULL represents a missing or unknown value. It is a marker, not a value: it is not zero, not an empty string, not FALSE, and not equal to anything — including another NULL. SQL therefore uses three-valued logic (3VL): TRUE, FALSE, UNKNOWN.

Why It Matters

NULL is the single most common source of subtle SQL bugs: missing rows in filters, wrong averages, broken NOT IN, surprising join results, and incorrect CHECK behavior. Mastering NULL is a sign of SQL maturity in interviews.

Syntax

col IS NULL
col IS NOT NULL
COALESCE(a, b, ...)
NULLIF(a, b)
a IS [NOT] DISTINCT FROM b        -- null-safe comparison (standard; PostgreSQL, SQL Server 2022+, SQLite 3.39+)

Example

40.1 What NULL means Meaning of NULL customers.email NULL Email not provided / unknown employees.manager_id NULL Not applicable (the CEO has no manager) orders.shipped_at NULL Event has not happened yet diagnostic_dtc.cleared_at NULL

Designs sometimes need to distinguish "unknown" from "not applicable"; if the difference matters, add an explicit status column rather than overloading NULL.

40.2 NULL vs zero vs empty string Oracle exception: Oracle treats the empty string '' as NULL in VARCHAR2 columns, so note = '' is never true there and '' inserted becomes NULL.

40.3 IS NULL and IS NOT NULL 40.4 NULL in comparisons and three-valued logic NULL AND TRUE NULL AND FALSE NULL OR TRUE NULL OR FALSE NOT NULL (the boolean) NULL + 5, NULL * 0, 'a' || NULL NULL (Oracle: 'a' || NULL = 'a') NULL IN (1, 2) 1 IN (1, NULL) 2 IN (1, NULL) 2 NOT IN (1, NULL) Where each clause treats UNKNOWN:

UNKNOWN treated as WHERE, HAVING, JOIN... ON Rejected (like FALSE) CHECK constraint Accepted (like TRUE) Not matched (falls through)

Consequence: the rows matching WHERE x = 1 and WHERE x <> 1 do not add up to the whole table when x has NULLs.

Null-safe comparison:

40.5 COALESCE and NULLIF COALESCE returns the data type with highest precedence among arguments; mixing types (COALESCE(int_col, 'N/A')) fails in strict DBMSs — cast first.

40.6 NULL in aggregate functions All aggregates except COUNT(*) ignore NULLs.

SUM/AVG/MIN/MAX of zero non-NULL values return NULL.

COUNT(DISTINCT col) ignores NULLs; SELECT DISTINCT and GROUP BY treat all NULLs as one group.

40.7 NULL in joins Rows whose join key is NULL never match in an equality join (Module 13.11).

Outer joins produce NULLs for missing matches.

To tell "no match" apart from a genuinely NULL column in the right table, test the right table's primary key: WHERE d.dept_id IS NULL.

40.8 NULL in sorting, uniqueness, and NOT IN Sorting: NULLs first or last depending on DBMS (Module 9.4).

UNIQUE: multiple NULLs allowed in most DBMSs; SQL Server allows one (Module 5.4).

NOT IN (subquery with NULL) returns no rows; use NOT EXISTS (Module 14.3).

40.9 NULL-related functions per DBMS COALESCE, IFNULL COALESCE, ISNULL COALESCE, NVL IS NOT DISTINCT FROM IS NOT DISTINCT FROM (2022+) DECODE(a, b, 1, 0) = 1 If null then x else y IF(col IS NULL, x, y) IIF(col IS NULL, x, y) NVL2(col, y, x)

CREATE TABLE null_demo (id INT, qty INT, note VARCHAR(20));
INSERT INTO null_demo VALUES (1, 0, ''), (2, NULL, NULL), (3, 5, 'ok');

SELECT id,
       qty = 0          AS qty_is_zero,      -- PostgreSQL/MySQL boolean output; SQL Server needs CASE
       qty IS NULL      AS qty_is_null,
       note = ''        AS note_is_empty,
       note IS NULL     AS note_is_null
FROM null_demo;

SELECT name FROM customers WHERE email IS NULL;          -- Diya Patel
SELECT name FROM customers WHERE email = NULL;           -- no rows, ever
SELECT name FROM customers WHERE NOT (email = NULL);     -- no rows either (NOT UNKNOWN = UNKNOWN)

SELECT
  (SELECT COUNT(*) FROM employees WHERE dept_id = 1)  AS eq,         -- 4
  (SELECT COUNT(*) FROM employees WHERE dept_id <> 1) AS neq,        -- 3
  (SELECT COUNT(*) FROM employees)                    AS total;      -- 8  (Kiran is in neither)

-- Standard
SELECT * FROM employees WHERE dept_id IS DISTINCT FROM 1;       -- 4 rows (includes Kiran)
-- MySQL
SELECT * FROM employees WHERE NOT (dept_id <=> 1);

-- Replace NULL with a default for display or calculation
SELECT first_name, COALESCE(CAST(dept_id AS VARCHAR(10)), 'Unassigned') AS dept FROM employees WHERE emp_id IN (6, 7);

-- NULLIF turns a sentinel value into NULL
SELECT NULLIF(TRIM(note), '') AS clean_note FROM null_demo;      -- '' becomes NULL
SELECT total / NULLIF(count, 0) AS avg_value FROM stats;          -- avoid divide-by-zero

SELECT COUNT(*)        AS rows_total,     -- 3
       COUNT(qty)      AS qty_non_null,   -- 2
       SUM(qty)        AS qty_sum,        -- 5
       AVG(qty)        AS qty_avg,        -- 2.5  (5 / 2, NULL ignored)
       AVG(COALESCE(qty, 0)) AS qty_avg_0,-- 1.6667 (5 / 3)
       MAX(note)       AS max_note        -- 'ok'
FROM null_demo;

SELECT e.first_name, d.dept_name
FROM employees e
LEFT JOIN departments d ON d.dept_id = e.dept_id
WHERE d.dept_name IS NULL;     -- employees without a (matching) department → Kiran

Expected Result

SELECT customer_id, name,
       CASE WHEN email IS NULL THEN 'MISSING' ELSE 'OK' END AS email_status
FROM customers
ORDER BY customer_id;

Common Mistakes

= NULL / <> NULL.

NOT IN with nullable subqueries.

Forgetting NULL rows in <> filters.

COUNT(col) when COUNT(*) was intended.

Assuming CHECK constraints reject NULL.

Arithmetic producing NULL silently (price * discount where discount is NULL).

Using magic values (-1, 'N/A', '1900-01-01') instead of NULL — or NULL where a real default makes more sense.

Best Practices

Make columns NOT NULL unless absence is meaningful.

Use COALESCE at presentation boundaries, not to hide data problems.

Use IS DISTINCT FROM / <=> for change detection (WHERE old.x IS DISTINCT FROM new.x).

Test every query with NULL-containing sample rows.

Interview Relevance

Very high: "What is NULL?", "NULL vs 0 vs ''", "Why does = NULL fail?", "How do aggregates handle NULL?", "Explain three-valued logic", "NOT IN vs NOT EXISTS with NULL".

Practice Questions

Predict: SELECT COUNT(*) FROM customers WHERE email <> '[email protected]';

Predict: SELECT 1 WHERE NULL IS NULL; and SELECT 1 WHERE NULL = NULL;

Write a query listing employees whose manager_id differs from 1, including those with NULL.

Compute the average score treating missing scores as 0 and as unknown; explain the difference.

Why does CHECK (discount <= 50) accept a NULL discount?