SQL NULL: Common Filtering and Counting Pitfalls

A filter for work “not assigned to employee 7” can return fewer tasks than expected without any SQL error. Unassigned rows may contain NULL. Unknown is not the same as unequal, zero or an empty string.

This PostgreSQL article concerns scalar values and filtering semantics. The example uses SELECT over illustrative data without creating tables or modifying production. Verify behavior in your actual database when using another SQL dialect.

1. Why not-equal does not include every other row

The PostgreSQL comparison documentation explains that ordinary comparisons with NULL yield unknown. WHERE retains true results, so assignee_id <> 7 does not select NULL assignees.

First settle the business question: should “not employee 7” include unassigned work? Include that case explicitly if yes. If the requirement is work already assigned to someone else, the ordinary comparison may be correct.

2. Use explicit NULL predicates

-- Unassigned tasks:
WHERE assignee_id IS NULL

-- Assigned tasks:
WHERE assignee_id IS NOT NULL

-- Someone else, or unassigned:
WHERE assignee_id <> 7 OR assignee_id IS NULL

These are alternative fragments, not one complete query. Do not use = NULL or != NULL to find missing values. Avoid changing database compatibility settings merely to conceal incorrect syntax.

3. Compare with explicit NULL handling

IS DISTINCT FROM returns true for unequal values or when only one side is NULL, and false when both are NULL. Its inverse is IS NOT DISTINCT FROM.

WITH tickets(id, assignee_id) AS (
    VALUES (1, 7), (2, NULL::integer), (3, 9)
)
SELECT id
FROM tickets
WHERE assignee_id IS DISTINCT FROM 7
ORDER BY id;

Expected IDs are 2 and 3. Replacing the condition with assignee_id <> 7 selects only 3. A small fixture exposes the distinction before a change reaches a large query with authorization conditions.

4. Be careful with NOT IN

The subquery documentation describes NULL's effect on NOT IN: 9 NOT IN (7, NULL) does not yield true. Exclusion lists drawn from nullable columns need particular care.

Consider correlated NOT EXISTS where appropriate, but decide how NULL on the main data side should behave too. The expressions are not universally interchangeable. Test empty lists, NULL entries, duplicates and a NULL left-hand value.

5. COUNT must match the question

The aggregate documentation distinguishes row-counting COUNT(*) from COUNT(assignee_id), which counts non-NULL expressions. The fixture produces 3 and 2 respectively.

A dashboard labeled “total tasks” can silently omit unassigned work if it counts the assignee column. That is a metric-definition problem as much as a SQL problem.

6. Do not fill every gap with zero

Document whether NULL means unknown, inapplicable or not yet entered for each field. Consider a separate state if those distinctions matter. Replacing every NULL with zero or an empty string can erase information just to simplify a query.

• Test populated values, NULL and an empty result set.
• Align interface filters with the business definition.
• Inspect nullable exclusion-list data.
• Distinguish rows from populated expressions when counting.
• Preserve tenant and authorization conditions.

Reliable NULL handling starts with modeling unknown information. Clear semantics make operators and tests easier to agree across backend, interface and reporting teams.