WHERE with IS NULL and IS NOT NULL in SQL - Deep Dive

Overview - WHERE with IS NULL and IS NOT NULL

What is it?

In SQL, the WHERE clause filters rows based on conditions. IS NULL and IS NOT NULL are special conditions used to check if a column has no value (NULL) or has some value (NOT NULL). These help find missing or present data in tables. They are essential because NULL means unknown or missing data, which is different from empty or zero.

Why it matters

Without IS NULL and IS NOT NULL, you cannot properly find or exclude rows with missing information. This makes data analysis incomplete or incorrect. For example, if you want to find customers without phone numbers, you need IS NULL. Without it, you might miss important insights or make wrong decisions.

Where it fits

Before learning this, you should understand basic SQL SELECT and WHERE clauses. After this, you can learn about more complex filtering, joins, and handling NULLs in expressions and functions.

Mental Model

Core Idea

IS NULL checks if a value is missing, and IS NOT NULL checks if a value is present, letting you filter data based on unknown or known values.

Think of it like...

Think of a guest list where some guests didn’t RSVP. IS NULL is like checking who didn’t reply at all, while IS NOT NULL is checking who did reply.

┌───────────────┐
│   Table Rows  │
├───────────────┤
│ Name | Phone │
├───────────────┤
│ Alice| 12345 │
│ Bob  | NULL  │
│ Carol| 67890 │
│ Dave | NULL  │
└───────────────┘

Query: SELECT * FROM table WHERE Phone IS NULL;
Result: Rows for Bob and Dave (missing phone numbers)

Build-Up - 7 Steps

1

Foundation

Understanding NULL in SQL

Concept: Learn what NULL means in SQL and how it differs from empty or zero values.

NULL means no data or unknown value in a column. It is not the same as zero (0) or empty string (''). For example, a phone number column can have NULL if the number is not provided. NULL is a special marker for missing information.

Result

You understand that NULL is a unique value representing missing data, not zero or empty.

Knowing that NULL is a special marker helps you realize why normal comparisons (=, <>) don’t work with NULL.

2

Foundation

Basic WHERE Clause Filtering

Concept: Learn how WHERE filters rows based on conditions.

The WHERE clause selects rows that meet a condition. For example, WHERE age > 30 returns rows where age is more than 30. But WHERE age = NULL does not work because NULL is not equal to anything, even NULL itself.

Result

You can filter rows with normal values but not with NULL using = or <>.

Understanding WHERE helps you see why special syntax is needed for NULL checks.

3

Intermediate

Using IS NULL to Find Missing Data

🤔Before reading on: do you think WHERE column = NULL will find missing values? Commit to yes or no.

Concept: IS NULL is the correct way to check if a column has missing data.

To find rows where a column is missing data, use WHERE column IS NULL. For example, WHERE phone IS NULL finds rows without phone numbers. Using = NULL does not work because NULL is not equal to anything.

Result

You can correctly find rows with missing values using IS NULL.

Understanding IS NULL prevents a common mistake that leads to missing rows with NULL values.

4

Intermediate

Using IS NOT NULL to Find Present Data

🤔Before reading on: does WHERE column <> NULL find rows with data? Commit to yes or no.

Concept: IS NOT NULL checks if a column has any value, excluding missing data.

To find rows where a column has data, use WHERE column IS NOT NULL. For example, WHERE phone IS NOT NULL finds rows with phone numbers. Using <> NULL does not work because NULL comparisons always return unknown.

Result

You can filter rows that have actual data, ignoring missing values.

Knowing IS NOT NULL helps you include only rows with known values, improving data quality.

5

Intermediate

Combining IS NULL with Other Conditions

🤔Before reading on: can you combine IS NULL with AND/OR in WHERE? Commit to yes or no.

Concept: You can combine IS NULL or IS NOT NULL with other conditions to filter data precisely.

For example, WHERE phone IS NULL AND city = 'New York' finds missing phone numbers only in New York. Using parentheses helps group conditions. This lets you build complex filters involving missing data.

Result

You can write queries that find missing or present data with other filters.

Combining IS NULL with other conditions allows precise data selection in real scenarios.

6

Advanced

NULL Behavior in Logical Expressions

🤔Before reading on: does NULL behave like false in AND/OR conditions? Commit to yes or no.

Concept: NULL in logical expressions results in unknown, affecting how WHERE filters rows.

In SQL, NULL is unknown, so expressions like (column = value) AND (column IS NOT NULL) can behave unexpectedly. For example, WHERE column = 'x' OR column IS NULL returns rows where column is 'x' or missing. Understanding three-valued logic (true, false, unknown) is key.

Result

You understand why some rows with NULL may be excluded or included unexpectedly.

Knowing NULL’s effect on logic prevents bugs in complex filters.

7

Expert

Indexing and Performance with IS NULL Checks

🤔Before reading on: do indexes always speed up IS NULL queries? Commit to yes or no.

Concept: How databases handle IS NULL in indexes affects query speed and optimization.

Some databases store NULLs in indexes, others don’t. Queries with IS NULL may or may not use indexes efficiently. Understanding your database’s indexing strategy helps write faster queries. Sometimes adding NOT NULL constraints or default values improves performance.

Result

You can write IS NULL queries that perform well in production.

Knowing index behavior with NULL helps optimize real-world database queries.

Under the Hood

Internally, NULL is a special marker indicating missing or unknown data. SQL uses three-valued logic: true, false, and unknown. Comparisons with NULL return unknown, so WHERE filters exclude those rows unless IS NULL or IS NOT NULL is used. The database engine treats IS NULL as a direct check for this marker, bypassing normal comparison rules.

Why designed this way?

SQL was designed to handle incomplete data, common in real life. Using NULL and three-valued logic allows databases to represent unknowns explicitly. Alternatives like using special values (e.g., 0 or empty string) were rejected because they can be valid data, causing confusion.

┌───────────────┐
│   Query Row   │
├───────────────┤
│ Column Value  │
├───────────────┤
│ NULL          │
└───────┬───────┘
        │
        ▼
┌─────────────────────────────┐
│ Comparison with = or <>      │
│ Result: UNKNOWN (not true)   │
└─────────────┬───────────────┘
              │
              ▼
┌─────────────────────────────┐
│ IS NULL or IS NOT NULL check │
│ Directly tests NULL marker   │
│ Result: TRUE or FALSE        │
└─────────────────────────────┘

Myth Busters - 4 Common Misconceptions

Quick: Does WHERE column = NULL find rows with missing data? Commit to yes or no.

Common Belief: Using WHERE column = NULL finds rows where the column is missing.

Reality: WHERE column = NULL never returns true because NULL is not equal to anything, even NULL itself.

Why it matters: This mistake causes queries to return no rows when trying to find missing data, leading to wrong results.

Quick: Does WHERE column <> NULL find rows with data? Commit to yes or no.

Common Belief: Using WHERE column <> NULL finds rows where the column has any value.

Reality: WHERE column <> NULL always returns unknown, so no rows are returned based on this condition.

Why it matters: This leads to missing all rows with data, causing confusion and errors in filtering.

Quick: Does IS NULL mean the same as empty string or zero? Commit to yes or no.

Common Belief: IS NULL means the value is empty or zero.

Reality: IS NULL means the value is missing or unknown, which is different from empty string ('') or zero (0).

Why it matters: Confusing NULL with empty or zero causes wrong data interpretation and incorrect query results.

Quick: Does IS NOT NULL include rows with empty strings? Commit to yes or no.

Common Belief: IS NOT NULL excludes empty strings or zero values.

Reality: IS NOT NULL includes any value that is not NULL, including empty strings and zero.

Why it matters: Misunderstanding this causes wrong assumptions about what data is included or excluded.

Expert Zone

1

Some databases treat NULLs differently in indexes, affecting query plans and performance subtly.

2

IS NULL checks can behave differently in outer joins, where NULLs may represent unmatched rows rather than missing data.

3

Using COALESCE or NULLIF functions can transform NULLs for more flexible filtering, but changes logic subtly.

When NOT to use

Avoid relying solely on IS NULL for data validation; use NOT NULL constraints or default values when possible. For filtering unknowns in complex expressions, consider using functions like COALESCE. In some cases, application logic or ETL processes should handle missing data instead of SQL filters.

Production Patterns

In production, IS NULL is often used to find incomplete records for cleanup or alerts. Combined with NOT NULL constraints, it helps maintain data quality. Queries often combine IS NULL with joins and aggregates to handle missing data gracefully. Index tuning considers NULL storage for performance.

Connections

Three-Valued Logic

IS NULL and IS NOT NULL rely on SQL’s three-valued logic system (true, false, unknown).

Understanding three-valued logic clarifies why NULL comparisons behave differently and why IS NULL is needed.

Data Cleaning

IS NULL is a key tool in identifying missing data during data cleaning processes.

Knowing how to find NULLs helps prepare data for analysis or machine learning by handling missing values properly.

Philosophy of Knowledge (Epistemology)

NULL represents unknown information, similar to the concept of unknown knowledge in philosophy.

Recognizing NULL as unknown rather than zero or empty helps appreciate the importance of uncertainty in data.

Common Pitfalls

#1 Trying to find missing values using = NULL.

Wrong approach:

SELECT * FROM users WHERE phone = NULL;

Correct approach:

SELECT * FROM users WHERE phone IS NULL;

Root cause:

Misunderstanding that NULL is not equal to anything, so = NULL never matches.

#2 Trying to find present values using <> NULL.

Wrong approach:

SELECT * FROM users WHERE phone <> NULL;

Correct approach:

SELECT * FROM users WHERE phone IS NOT NULL;

Root cause:

Assuming NULL behaves like a normal value in comparisons, which it does not.

#3 Confusing NULL with empty string or zero.

Wrong approach:

SELECT * FROM users WHERE phone = ''; -- expecting to find missing phones

Correct approach:

SELECT * FROM users WHERE phone IS NULL; -- finds missing phones

Root cause:

Not realizing NULL means unknown, not empty or zero.

Key Takeaways

NULL in SQL means missing or unknown data, different from zero or empty.

You must use IS NULL and IS NOT NULL to check for missing or present values correctly.

Normal comparison operators (=, <>) do not work with NULL because of SQL’s three-valued logic.

Combining IS NULL with other conditions allows precise filtering of data with missing values.

Understanding how NULL affects query logic and indexing is key for writing correct and efficient SQL.