SQL 21 🛢️ Handling NULL Values with IS NULL and IS NOT NULL - KandZ

NULL is the most misunderstood value in SQL. It is not zero. It is not an empty string. It is not a space. It is the absence of a value — the marker that says “this value is unknown, missing, or inapplicable.” Because NULL is not a value, it does not behave like one. Comparing NULL to anything returns NULL, not TRUE or FALSE. Filtering with = NULL returns no rows. The only way to test for NULL is the IS NULL operator, and the only way to test for the absence of NULL is IS NOT NULL.

The previous chapters covered the comparison operators, the logical operators, the range filter, the set filter, and pattern matching. This chapter covers the NULL handling that runs through all of them. NULL is not a special case in one clause. It is a property of the data model that affects every comparison, every aggregation, every join, and every constraint.

Key point: NULL means “unknown” or “not applicable.” It is not a value. The comparison NULL = NULL returns NULL, not TRUE, because two unknowns are not necessarily the same unknown. The expression NULL <> NULL also returns NULL. The only operators that return TRUE or FALSE when NULL is involved are IS NULL and IS NOT NULL. This is three-valued logic: every expression evaluates to TRUE, FALSE, or NULL, and only TRUE satisfies the WHERE clause.

Why NULL Handling Matters

Every table has nullable columns. A middle_name is null for a person who has no middle name. A termination_date is null for an employee who is still employed. A discount is null for a product that has no discount. NULL is the correct representation for these cases. The problem is that NULL does not behave like a value.

The comparison problem. WHERE discount = 0 does not match rows where discount is NULL. WHERE discount <> 0 also does not match them. The NULL row is invisible to both queries. To find it, the query must use IS NULL.

The aggregation problem. Aggregate functions ignore NULL values. COUNT(*) counts all rows. COUNT(discount) counts only the rows where discount is not null. AVG(discount) divides by the count of non-null values, not by the total row count. The difference matters.

The join problem. A join condition that uses = excludes rows where the join key is NULL. A row with a NULL foreign key never matches any row in the parent table. This is usually the correct behavior, but it surprises developers who expect the NULL row to appear.

The constraint problem. A NOT NULL constraint prevents NULL. A UNIQUE constraint allows multiple NULLs in most databases because NULL is not equal to NULL. A CHECK constraint returns NULL for a NULL value, which is not a violation. Each constraint treats NULL differently.

The trade-off. NULL is necessary. It represents data that is genuinely unknown or inapplicable. The alternative — using a sentinel value like 0 or 'N/A' — creates ambiguity. A discount of 0 might mean “no discount” or “the discount is zero.” A NULL discount unambiguously means “no discount.” The cost of NULL is the complexity of three-valued logic.

a. The IS NULL and IS NOT NULL Operators

The IS NULL operator returns TRUE if the operand is NULL and FALSE if it is not. The IS NOT NULL operator returns TRUE if the operand is not NULL and FALSE if it is.

SELECT * FROM customers WHERE city IS NULL;
SELECT * FROM customers WHERE city IS NOT NULL;

The first query selects customers whose city is unknown. The second selects customers whose city is known. Together, they cover every row in the table. Every row either has a city or does not.

The IS NULL operator is the only way to test for NULL. The comparison operators cannot be used.

-- ❌ This returns no rows
SELECT * FROM customers WHERE city = NULL;

-- ✅ This returns the null rows
SELECT * FROM customers WHERE city IS NULL;

The reason is three-valued logic. The expression city = NULL evaluates to NULL, not TRUE. The WHERE clause includes only the rows where the condition is TRUE. A NULL result is excluded. The IS NULL operator is specifically designed to test for NULL and returns TRUE when it finds it.

The IS NULL operator can be combined with other conditions using AND and OR.

SELECT * FROM customers WHERE city IS NULL AND is_active = TRUE;
SELECT * FROM customers WHERE city IS NULL OR city = 'New York';

The first query selects active customers whose city is unknown. The second selects customers whose city is unknown or whose city is New York. The OR operator includes the NULL rows because city IS NULL is TRUE for them.

b. NULL in Aggregations and Expressions

Aggregate functions ignore NULL values. This is the SQL standard, and it is consistent across all major databases.

CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    customer_id INT,
    total DECIMAL(10, 2)
);

INSERT INTO orders VALUES
    (1, 1, 100.00),
    (2, 1, 200.00),
    (3, 2, NULL),
    (4, 3, 150.00);

SELECT COUNT(*) FROM orders;          -- 4
SELECT COUNT(total) FROM orders;      -- 3
SELECT SUM(total) FROM orders;        -- 450.00
SELECT AVG(total) FROM orders;        -- 150.00
SELECT MAX(total) FROM orders;        -- 200.00

The COUNT(*) counts all four rows. The COUNT(total) counts only the three rows where total is not null. The SUM(total) adds the three non-null values. The AVG(total) divides by 3, not by 4, because the NULL row is ignored. The MAX(total) returns the largest non-null value.

The difference between COUNT(*) and COUNT(column) is important. COUNT(*) counts rows. COUNT(column) counts non-null values in the column. They return different results when the column is nullable.

Expressions that involve NULL return NULL. The arithmetic operators, the concatenation operator, and the comparison operators all propagate NULL.

SELECT 100 + NULL;          -- NULL
SELECT 'Hello' || NULL;     -- NULL (in most databases)
SELECT NULL = NULL;         -- NULL
SELECT NULL <> NULL;        -- NULL
SELECT NULL > 0;            -- NULL

The result of any expression that involves NULL is NULL, unless the expression uses a function specifically designed to handle NULL.

c. Handling NULL with COALESCE, NULLIF, and CASE

Several functions and expressions handle NULL explicitly.

COALESCE(value1, value2, ...) returns the first non-null argument. It is the standard way to provide a fallback for NULL.

SELECT COALESCE(discount, 0) FROM products;
SELECT COALESCE(city, 'Unknown') FROM customers;

The first query returns the discount if it is not null, or 0 if it is. The second returns the city if it is not null, or 'Unknown' if it is.

NULLIF(value1, value2) returns NULL if the two arguments are equal, or value1 if they are not. It is the inverse of COALESCE in a sense. It converts a sentinel value into NULL.

SELECT NULLIF(discount, 0) FROM products;

The query returns NULL if the discount is 0, or the discount value if it is not. This is useful when the data uses 0 to mean “no discount” and the query needs to treat it as NULL.

CASE expressions can handle NULL explicitly.

SELECT
    customer_id,
    CASE
        WHEN city IS NULL THEN 'Unknown'
        ELSE city
    END AS city_display
FROM customers;

The CASE expression returns 'Unknown' when the city is null and the city value otherwise. It is the most flexible way to handle NULL.

IS DISTINCT FROM and IS NOT DISTINCT FROM are null-safe comparison operators. IS DISTINCT FROM returns TRUE if the values are different, including when one is null and the other is not. IS NOT DISTINCT FROM returns TRUE if the values are equal, including when both are null.

SELECT * FROM customers WHERE city IS DISTINCT FROM 'New York';
SELECT * FROM customers WHERE city IS NOT DISTINCT FROM NULL;

The first query includes null rows. The second query is equivalent to WHERE city IS NULL.

Function/Operator	Purpose
`IS NULL`	Test for null
`IS NOT NULL`	Test for not null
`COALESCE`	Return the first non-null value
`NULLIF`	Return null if values are equal
`CASE`	Conditional null handling
`IS DISTINCT FROM`	Null-safe inequality
`IS NOT DISTINCT FROM`	Null-safe equality

Complete Example Session

This session demonstrates NULL handling on a small table.

-- ============================================
-- PART 1: THE TABLE
-- ============================================

CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    first_name  VARCHAR(50) NOT NULL,
    last_name   VARCHAR(50) NOT NULL,
    middle_name VARCHAR(50),
    email       VARCHAR(100),
    salary      DECIMAL(10, 2),
    hire_date   DATE NOT NULL,
    termination_date DATE
);

INSERT INTO employees VALUES
    (1, 'Alice', 'Johnson', NULL, 'alice@example.com', 50000, '2020-01-15', NULL),
    (2, 'Bob', 'Smith', 'James', 'bob@example.com', 60000, '2019-03-20', '2026-01-15'),
    (3, 'Carol', 'Williams', NULL, NULL, 55000, '2021-06-10', NULL),
    (4, 'Dave', 'Brown', 'Lee', 'dave@example.com', NULL, '2022-09-05', NULL);

-- ============================================
-- PART 2: FINDING NULL VALUES
-- ============================================

SELECT * FROM employees WHERE middle_name IS NULL;

-- Output: Alice, Carol

SELECT * FROM employees WHERE email IS NULL;

-- Output: Carol

SELECT * FROM employees WHERE salary IS NULL;

-- Output: Dave

-- ============================================
-- PART 3: FINDING NON-NULL VALUES
-- ============================================

SELECT * FROM employees WHERE middle_name IS NOT NULL;

-- Output: Bob, Dave

SELECT * FROM employees WHERE termination_date IS NOT NULL;

-- Output: Bob

-- ============================================
-- PART 4: THE NULL COMPARISON TRAP
-- ============================================

SELECT * FROM employees WHERE middle_name = NULL;

-- Output: empty
-- The = NULL comparison returns NULL, not TRUE.

SELECT * FROM employees WHERE middle_name IS NULL;

-- Output: Alice, Carol
-- The IS NULL operator returns TRUE.

-- ============================================
-- PART 5: NULL IN AGGREGATIONS
-- ============================================

SELECT COUNT(*) FROM employees;          -- 4
SELECT COUNT(salary) FROM employees;     -- 3
SELECT AVG(salary) FROM employees;       -- 55000
SELECT SUM(salary) FROM employees;       -- 165000

-- COUNT(*) counts all rows.
-- COUNT(salary) counts only the non-null salaries.
-- AVG(salary) divides by 3, not 4.

-- ============================================
-- PART 6: COALESCE
-- ============================================

SELECT
    employee_id,
    COALESCE(middle_name, '') AS middle_name,
    COALESCE(email, 'no email') AS email,
    COALESCE(salary, 0) AS salary
FROM employees;

-- The COALESCE function provides a fallback for each null value.

-- ============================================
-- PART 7: NULLIF
-- ============================================

SELECT
    employee_id,
    NULLIF(salary, 0) AS salary_nonzero
FROM employees;

-- NULLIF returns null if the salary is 0.
-- Since no salary is 0 in this table, all values are returned.

-- ============================================
-- PART 8: CASE
-- ============================================

SELECT
    employee_id,
    first_name,
    CASE
        WHEN termination_date IS NULL THEN 'Active'
        ELSE 'Terminated'
    END AS status
FROM employees;

-- Output:
-- Alice: Active
-- Bob: Terminated
-- Carol: Active
-- Dave: Active

-- ============================================
-- PART 9: IS DISTINCT FROM
-- ============================================

SELECT * FROM employees WHERE middle_name IS DISTINCT FROM 'James';

-- Output: Alice, Carol, Dave
-- The IS DISTINCT FROM operator includes the null rows.

SELECT * FROM employees WHERE middle_name IS NOT DISTINCT FROM NULL;

-- Output: Alice, Carol
-- Equivalent to WHERE middle_name IS NULL.

-- ============================================
-- PART 10: THE NULL SUMMARY
-- ============================================

-- NULL is not a value. It is the absence of a value.
-- IS NULL and IS NOT NULL are the only operators that test for NULL.
-- Comparison operators return NULL when NULL is involved.
-- Aggregate functions ignore NULL.
-- COALESCE provides a fallback.
-- NULLIF converts a sentinel value to NULL.
-- CASE handles NULL explicitly.

The ten parts cover the table, finding null values, finding non-null values, the null comparison trap, null in aggregations, COALESCE, NULLIF, CASE, IS DISTINCT FROM, and the summary.

Quick Reference

The NULL Operators

Operator	Purpose
`IS NULL`	Value is null
`IS NOT NULL`	Value is not null
`IS DISTINCT FROM`	Null-safe inequality
`IS NOT DISTINCT FROM`	Null-safe equality

The NULL Functions

Function	Purpose
`COALESCE(a, b, c)`	First non-null value
`NULLIF(a, b)`	Null if a = b
`CASE WHEN ... THEN ... END`	Conditional handling

The NULL Behavior in Comparisons

Expression	Result
`NULL = NULL`	`NULL`
`NULL <> NULL`	`NULL`
`NULL > 0`	`NULL`
`NULL IS NULL`	`TRUE`
`NULL IS NOT NULL`	`FALSE`

The NULL Behavior in Aggregations

Aggregate	Behavior
`COUNT(*)`	Counts all rows
`COUNT(col)`	Counts non-null values
`SUM(col)`	Sums non-null values
`AVG(col)`	Divides by non-null count
`MAX(col)`	Largest non-null value
`MIN(col)`	Smallest non-null value

Best Practices

✅ Do This:

-- Use IS NULL to test for null
WHERE city IS NULL                                              -- ✅

-- Use COALESCE for fallback values
SELECT COALESCE(discount, 0) FROM products                      -- ✅

-- Use IS NOT NULL to require a value
WHERE email IS NOT NULL                                         -- ✅

-- Use COUNT(*) when counting rows
SELECT COUNT(*) FROM orders                                     -- ✅

-- Use IS DISTINCT FROM for null-safe comparison
WHERE city IS DISTINCT FROM 'New York'                          -- ✅

❌ Don’t Do This:

-- Don't use = NULL
WHERE city = NULL  -- never true                                -- ❌

-- Don't use <> NULL
WHERE city <> NULL  -- never true                               -- ❌

-- Don't assume COUNT(col) counts all rows
SELECT COUNT(email) FROM users  -- counts only non-null         -- ⚠️

-- Don't use NOT IN with a nullable subquery
WHERE id NOT IN (SELECT id FROM t)  -- null poisons the result   -- ❌

Common Pitfalls

Pitfall	Why It Happens	Fix
`= NULL` returns no rows	Null comparison returns null	Use `IS NULL`
Null rows excluded from `<>`	Null comparison returns null	Use `IS DISTINCT FROM`
`COUNT(col)` wrong	Null values ignored	Use `COUNT(*)` for rows
`AVG` divides wrong	Null values ignored	Check the denominator
`NOT IN` returns no rows	Null in subquery	Use `NOT EXISTS`

Real-World Examples

1. Find Null Values

SELECT * FROM customers WHERE city IS NULL;

2. Find Non-Null Values

SELECT * FROM customers WHERE city IS NOT NULL;

3. Count Rows

SELECT COUNT(*) FROM orders;

4. Count Non-Null Values

SELECT COUNT(discount) FROM products;

5. Fallback Value

SELECT COALESCE(discount, 0) FROM products;

6. Convert Sentinel to Null

SELECT NULLIF(discount, 0) FROM products;

7. Conditional Handling

SELECT CASE WHEN city IS NULL THEN 'Unknown' ELSE city END FROM customers;

8. Null-Safe Inequality

SELECT * FROM customers WHERE city IS DISTINCT FROM 'New York';

9. Null-Safe Equality

SELECT * FROM customers WHERE city IS NOT DISTINCT FROM NULL;

10. Null in a Join

SELECT * FROM orders o JOIN customers c ON o.customer_id = c.customer_id;
-- Rows with null customer_id do not match.

Visual

The NULL Behavior

┌──────────────────────────────────────────────┐
│  NULL IS NOT A VALUE                         │
│                                              │
│  NULL = NULL    → NULL                       │
│  NULL <> NULL   → NULL                       │
│  NULL > 0       → NULL                       │
│                                              │
│  Only IS NULL and IS NOT NULL return         │
│  TRUE or FALSE when NULL is involved.        │
│                                              │
└──────────────────────────────────────────────┘

The Three-Valued Logic

┌──────────────────────────────────────────────┐
│  THREE-VALUED LOGIC                          │
│                                              │
│  TRUE  → row included in WHERE               │
│  FALSE → row excluded                        │
│  NULL  → row excluded                        │
│                                              │
│  WHERE includes only TRUE.                   │
│  A NULL result is not TRUE.                  │
│                                              │
└──────────────────────────────────────────────┘

The Aggregate Behavior

┌──────────────────────────────────────────────┐
│  AGGREGATE BEHAVIOR                          │
│                                              │
│  Data: 100, 200, NULL, 150                   │
│                                              │
│  COUNT(*)    → 4 (all rows)                  │
│  COUNT(col)  → 3 (non-null values)           │
│  SUM(col)    → 450 (non-null values)         │
│  AVG(col)    → 150 (450 / 3, not 450 / 4)    │
│                                              │
│  NULL values are ignored by aggregates.      │
│                                              │
└──────────────────────────────────────────────┘

The NULL Handling Functions

┌──────────────────────────────────────────────┐
│  COALESCE(discount, 0)                       │
│    └─ Returns discount if not null, else 0   │
│                                              │
│  NULLIF(discount, 0)                         │
│    └─ Returns null if discount is 0          │
│                                              │
│  CASE WHEN city IS NULL THEN 'Unknown'       │
│       ELSE city END                          │
│    └─ Conditional handling                   │
│                                              │
└──────────────────────────────────────────────┘

Summary

Item	Value
NULL meaning	Unknown or inapplicable
`IS NULL`	Test for null
`IS NOT NULL`	Test for not null
`= NULL`	Returns NULL, not TRUE
`<> NULL`	Returns NULL, not TRUE
`COUNT(*)`	Counts all rows
`COUNT(col)`	Counts non-null values
`AVG(col)`	Divides by non-null count
`COALESCE`	First non-null value
`NULLIF`	Null if values are equal
`IS DISTINCT FROM`	Null-safe inequality
`IS NOT DISTINCT FROM`	Null-safe equality

Key takeaways:

NULL is the absence of a value, not a value itself. It means “unknown” or “not applicable.” It is not zero, not an empty string, and not a space. It is a marker that says the data is missing .
The comparison operators return NULL when NULL is involved. The expression NULL = NULL returns NULL, not TRUE. The WHERE clause includes only the rows where the condition is TRUE. A NULL result is excluded .
The IS NULL and IS NOT NULL operators are the only way to test for NULL. The IS NULL operator returns TRUE when the operand is null. The IS NOT NULL operator returns TRUE when the operand is not null. Together they cover every row .
Aggregate functions ignore NULL values. The COUNT(*) function counts all rows. The COUNT(column) function counts only the non-null values. The AVG(column) function divides by the non-null count, not the total row count .
Expressions that involve NULL return NULL. The arithmetic operators, the concatenation operator, and the comparison operators all propagate NULL. The result of any expression that involves NULL is NULL, unless the expression uses a function designed to handle NULL .
COALESCE provides a fallback for NULL. The function returns the first non-null argument. The COALESCE(discount, 0) expression returns the discount if it is not null, or 0 if it is .
NULLIF converts a sentinel value to NULL. The function returns NULL if the two arguments are equal. The NULLIF(discount, 0) expression returns NULL if the discount is 0, or the discount value if it is not. The IS DISTINCT FROM and IS NOT DISTINCT FROM operators provide null-safe comparisons.

Remember: NULL is the absence of a value. It is not a value. The comparison operators cannot test for it. The IS NULL and IS NOT NULL operators are the only way. Aggregate functions ignore it. Expressions propagate it. COALESCE provides a fallback. NULLIF converts a sentinel. IS DISTINCT FROM is the null-safe comparison. Three-valued logic is the rule. The WHERE clause includes only TRUE. NULL is not TRUE. NULL is not FALSE. NULL is NULL.

Stop using slow, ad-bloated tool sites! 🤮

🔎 Search “KandZ Tools” on Google to use many professional utilities for free.

KandZ.me is the ultimate minimalist hub for:
✅ Finance (Mortgage, Interest, Inflation)
✅ Tech (Base64, JSON, Dev Suite, IP)
✅ Health (BMI, BMR, TDEE)
✅ Productivity (Timer, Workspace, QR)

⚡️ Fast & Private
🔒 No data leaves your device
💎 100% Free

🔗 Use it now: https://tools.kandz.me
🔖 Bookmark it—you’ll need it later!