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.

Practice

1. Which SQL clause correctly finds rows where the email column has no value stored (is missing)?

easy

A. WHERE email = NULL

B. WHERE email = ''

C. WHERE email IS NULL

D. WHERE email IS NOT NULL

Solution

1. Step 1: Understand NULL meaning in SQL

   NULL means no value or missing data, not an empty string or zero.

2. Step 2: Use correct syntax to check NULL

   To check for NULL, use IS NULL, not = NULL or empty string.

3. Final Answer:

   WHERE email IS NULL -> Option C

4. Quick Check:

   Check NULL with IS NULL = WHERE email IS NULL [OK]

Hint: Use IS NULL to find missing values, never = NULL [OK]

Common Mistakes:

• Using = NULL instead of IS NULL
• Confusing empty string '' with NULL
• Using IS NOT NULL to find missing values

2. Which of the following is the correct syntax to select rows where the phone column is NOT missing?

easy

A. SELECT * FROM contacts WHERE phone IS NOT NULL;

B. SELECT * FROM contacts WHERE phone = NOT NULL;

C. SELECT * FROM contacts WHERE phone != NULL;

D. SELECT * FROM contacts WHERE phone NOT NULL;

Solution

1. Step 1: Recall correct syntax for NOT NULL check

   SQL uses IS NOT NULL to find rows where value exists.

2. Step 2: Identify syntax errors in other options

   Options B, C, D use invalid syntax like = NOT NULL or != NULL or missing IS.

3. Final Answer:

   SELECT * FROM contacts WHERE phone IS NOT NULL; -> Option A

4. Quick Check:

   Use IS NOT NULL to find existing values = SELECT * FROM contacts WHERE phone IS NOT NULL; [OK]

Hint: Use IS NOT NULL, not = NOT NULL or != NULL [OK]

Common Mistakes:

• Using = NOT NULL instead of IS NOT NULL
• Using != NULL which is invalid
• Omitting IS keyword

3. Given the table employees with columns id, name, and manager_id where some employees have no manager (NULL), what will this query return?

SELECT name FROM employees WHERE manager_id IS NULL;

medium

A. All employees who have a manager assigned

B. All employees who do not have a manager assigned

C. All employees including those with and without managers

D. Syntax error due to IS NULL usage

Solution

1. Step 1: Understand IS NULL condition

   The condition manager_id IS NULL filters rows where manager_id has no value (missing).

2. Step 2: Interpret query result

   The query returns names of employees without any manager assigned (NULL manager_id).

3. Final Answer:

   All employees who do not have a manager assigned -> Option B

4. Quick Check:

   IS NULL filters missing values = All employees who do not have a manager assigned [OK]

Hint: IS NULL finds missing values, so returns employees without managers [OK]

Common Mistakes:

• Thinking IS NULL finds rows with values
• Confusing NULL with empty string
• Assuming syntax error with IS NULL

4. Identify the error in this query that tries to select rows where address is NOT NULL:

SELECT * FROM customers WHERE address != NULL;

medium

A. Should use = NULL instead of != NULL

B. Missing quotes around NULL

C. No error; query is correct

D. Using != NULL is invalid; should use IS NOT NULL

Solution

1. Step 1: Check NULL comparison syntax

   In SQL, NULL cannot be compared with = or != operators.

2. Step 2: Use correct syntax for NOT NULL

   The correct way is IS NOT NULL to check for non-missing values.

3. Final Answer:

   Using != NULL is invalid; should use IS NOT NULL -> Option D

4. Quick Check:

   Use IS NOT NULL, not != NULL [OK]

Hint: Never use = or != with NULL; use IS (NOT) NULL [OK]

Common Mistakes:

• Using != NULL or = NULL
• Adding quotes around NULL
• Assuming != NULL works like other values

5. You have a table orders with columns order_id, customer_id, and shipped_date. Some orders are not shipped yet and have shipped_date as NULL. Which query lists all orders that have been shipped?

hard

A. SELECT * FROM orders WHERE shipped_date IS NOT NULL;

B. SELECT * FROM orders WHERE shipped_date IS NULL;

C. SELECT * FROM orders WHERE shipped_date != NULL;

D. SELECT * FROM orders WHERE shipped_date = NULL;

Solution

1. Step 1: Understand meaning of shipped_date NULL

   NULL means order not shipped yet, so shipped orders have non-NULL dates.

2. Step 2: Use correct syntax to find shipped orders

   Use IS NOT NULL to find rows where shipped_date has a value.

3. Final Answer:

   SELECT * FROM orders WHERE shipped_date IS NOT NULL; -> Option A

4. Quick Check:

   IS NOT NULL finds shipped orders = SELECT * FROM orders WHERE shipped_date IS NOT NULL; [OK]

Hint: IS NOT NULL finds rows with values, perfect for shipped orders [OK]

Common Mistakes:

• Using = NULL or != NULL which are invalid
• Using IS NULL to find shipped orders
• Confusing NULL with empty string