IS NULL / IS NOT NULL

Level 3 — CRUD Operations (The Four Pillars of SQL) The specialized SQL comparison operators used to filter query results based on the presence (IS NOT NULL) or absence (IS NULL) of data.

1. Prerequisites

• NULL — Understanding the absent state.
• Comparison & Logical Operators — How basic SQL comparisons work.

2. Term Category

SQL Command / Clause (Null State Comparison Predicate): IS NULL and IS NOT NULL test for the presence or absence of NULL states in table columns.

3. Explanation

Environment Context

• Universal Standard (Enforced in all relational SQL query engines. Standardized by the ANSI-SQL spec).

(1) Design Motivation — "Why did we design this?"

When filtering data rows, you frequently need to check for missing information:

• Find all orders that have not shipped yet (where shipped_at is empty).
• Find all users who haven't completed email verification.
• Find all transactions that do not have a discount applied.

In programming languages, you compare values directly to null (e.g. if (value == null)).

However, as learned in Level 2 (null.md), NULL in SQL is a special marker, not a value.

If you try to write standard comparisons:

• shipped_at = NULL
• shipped_at != NULL

The database engine returns UNKNOWN for every single row. Because WHERE clauses only return rows where the filter evaluates strictly to TRUE, these queries fail silently, returning zero rows.

To solve this, SQL introduced the dedicated operators IS NULL and IS NOT NULL.

They check the state of the column cell directly, bypassing standard math value comparisons.

(2) Reality Metaphor

Imagine sorting mailboxes:

• WHERE letters = NULL is like walking up to a mailbox, opening a blank piece of paper, and asking: "Is the text printed on this paper equal to the concept of absolute vacancy?" The paper has no text, so you get no answer.
• WHERE letters IS NULL is like looking at the mailbox door from the outside and asking: "Is this mailbox physically empty?" You can answer that with a clear "Yes" or "No."

(3) Code Examples

Locating Missing Data

CREATE TABLE support_tickets (
  id INT PRIMARY KEY,
  subject VARCHAR(100),
  resolved_at TIMESTAMPTZ -- NULL if ticket is open
);

-- Find all unresolved (open) tickets
SELECT subject 
FROM support_tickets 
WHERE resolved_at IS NULL;

Locating Completed Data

-- Find all resolved (closed) tickets
SELECT subject 
FROM support_tickets 
WHERE resolved_at IS NOT NULL;

The Silent Failure Demo

-- WRONG: This query executes successfully but returns ZERO rows, 
-- even if you have hundreds of open tickets!
SELECT subject FROM support_tickets WHERE resolved_at = NULL;

4. Common Mistakes & Pitfalls

Mistake 1: Using = NULL or != NULL inside query scripts

The mistake: Writing queries like WHERE status = NULL or WHERE discount_percent != NULL inside your backend application queries.

Why it's wrong: SQL engines process = NULL as an unknown equation. It will never return a row, making your application behave as if the database is completely empty.

Fix: Train yourself to replace = NULL with IS NULL, and != NULL (or <> NULL) with IS NOT NULL.

Mistake 2: Using Equality Operators (= NULL) to Filter Null Values

The mistake: Writing SELECT * FROM users WHERE phone = NULL;.

Why it's wrong: In SQL 3-valued logic, anything = NULL evaluates to NULL (Unknown), returning 0 rows! Always use IS NULL or IS NOT NULL.

Incorrect:

SELECT * FROM users WHERE phone = NULL; -- ❌ Always returns 0 rows!

Fix:

SELECT * FROM users WHERE phone IS NULL; -- Correct NULL predicate

Mistake 3: Using != NULL or <> NULL to Check Non-Null Values

The mistake: Writing SELECT * FROM users WHERE phone != NULL;.

Why it's wrong: anything != NULL evaluates to NULL (Unknown). Use IS NOT NULL.

Incorrect:

SELECT * FROM users WHERE phone != NULL; -- ❌ Returns 0 rows!

Fix:

SELECT * FROM users WHERE phone IS NOT NULL;

5. Practice Exercises

Exercise 1: Querying Null vs Non-Null Rows

Scenario: Query orders for unpaid invoices where paid_at IS NULL vs paid invoices where paid_at IS NOT NULL.

Requirements:

1. Execute SELECT with IS NULL and IS NOT NULL.

-- Unpaid Invoices
SELECT id, customer_id, total_cents 
FROM invoices 
WHERE paid_at IS NULL;

-- Paid Invoices
SELECT id, customer_id, paid_at 
FROM invoices 
WHERE paid_at IS NOT NULL;

Exercise 2: Partial Indexing over Nullable Columns

Scenario: Create a partial index over invoices for unpaid orders (WHERE paid_at IS NULL).

Requirements:

1. Execute CREATE INDEX ON invoices (customer_id) WHERE paid_at IS NULL.

CREATE INDEX idx_unpaid_invoices 
ON invoices (customer_id) 
WHERE paid_at IS NULL;

Exercise 3: Combining IS NULL with Fallback Projections

Scenario: Select users where phone IS NULL and display fallback label 'No Phone Number'.

Requirements:

1. Combine CASE WHEN phone IS NULL or COALESCE(phone, 'No Phone Number').

SELECT 
  username, 
  COALESCE(phone, 'No Phone Number') AS contact_phone 
FROM users;

6. Related Terms

• NULL — The parent absent state.
• WHERE Clause — The query filter wrapper.
• Comparison & Logical Operators — Related concept: Comparison & Logical Operators.

7. Key Takeaways

• You cannot use = or != to compare columns to NULL.
• Direct comparisons with NULL yield UNKNOWN, which filters out the rows.
• Use IS NULL to query rows where a column contains missing or blank states.
• Use IS NOT NULL to query rows where a column has valid data.
• Ensure all nullable column filters in scripts use correct check syntax to avoid empty outputs.