SQLSorting and Limiting Resultsbeginner40 min read

Using IS NULL and IS NOT NULL

Master IS NULL and IS NOT NULL to find missing or present data, filter soft-deleted records, handle nullable join conditions, and compare values safely using IS DISTINCT FROM in PostgreSQL.

In this lesson, you will learn

✓Identify rows containing missing values using IS NULL
✓Filter rows with non-NULL values using IS NOT NULL
✓Explain why = NULL and <> NULL do not correctly test for missing values
✓Distinguish NULL values from empty strings and ordinary values
✓Query active and soft-deleted records using a deleted_at timestamp
✓Understand how NULL behaves in joins and filtering conditions
✓Use IS DISTINCT FROM and IS NOT DISTINCT FROM for null-safe comparisons
✓Understand how indexes can support IS NULL queries in PostgreSQL
✓Recognize common mistakes involving NULL predicates

1. Understanding IS NULL and IS NOT NULL

SQL uses NULL to represent missing, unknown, or inapplicable information. Because NULL is not an ordinary value, you cannot reliably check for it using normal comparison operators.

Use IS NULL to find rows whose selected column contains NULL. Use IS NOT NULL to find rows where that column contains a value.

These predicates are especially useful for optional information, incomplete registrations, missing timestamps, and records that have not yet completed a process.

Find Missing and Present Values

sql

1
2
3
4
5
6
7
8
9
-- Find users who have not verified their email
SELECT user_id, email, email_verified_at
FROM users
WHERE email_verified_at IS NULL;
 
-- Find users with a recorded phone number
SELECT user_id, email, phone_number
FROM users
WHERE phone_number IS NOT NULL;

The first query selects users whose verification timestamp is missing. The second selects users whose phone_number is not NULL. Whether the recorded phone number is valid is a separate question.

2. Why = NULL Does Not Work

In SQL, an ordinary comparison involving NULL evaluates to UNKNOWN when the comparison cannot be determined. Therefore, NULL = NULL does not evaluate to TRUE.

The same issue applies to comparisons such as phone_number = NULL and phone_number <> NULL. In a WHERE clause, UNKNOWN does not select a row.

The correct solution is to use the dedicated NULL predicates.

Incorrect Versus Correct Syntax

sql

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
-- Incorrect: does not find missing email values
SELECT user_id, email
FROM users
WHERE email = NULL;
 
-- Correct
SELECT user_id, email
FROM users
WHERE email IS NULL;
 
-- Incorrect: does not correctly find present email values
SELECT user_id, email
FROM users
WHERE email <> NULL;
 
-- Correct
SELECT user_id, email
FROM users
WHERE email IS NOT NULL;

Use IS NULL and IS NOT NULL rather than = NULL or <> NULL. The incorrect predicates do not select the intended rows because ordinary comparisons with NULL produce UNKNOWN.

3. How WHERE Handles NULL Predicates

A WHERE condition can evaluate to TRUE, FALSE, or UNKNOWN. SQL returns a row only when the complete WHERE condition evaluates to TRUE.

Unlike ordinary comparisons with NULL, IS NULL and IS NOT NULL are designed to produce a definite Boolean result for a row.

Checking the Results

sql

1
2
3
4
5
SELECT
  NULL IS NULL AS first_check,
  NULL IS NOT NULL AS second_check,
  10 IS NULL AS third_check,
  10 IS NOT NULL AS fourth_check;

Output

IS NULL and IS NOT NULL always produce TRUE or FALSE. They do not produce UNKNOWN just because the tested expression is NULL.

4. NULL Is Not an Empty String

An empty string ('') is a valid text value containing zero characters. NULL means that the value is missing or unknown. In PostgreSQL, these are different values and require different checks.

A column containing an empty string will satisfy IS NOT NULL. If your application treats both NULL and empty text as missing, check for both explicitly.

Find NULL and Empty Text Separately

sql

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
-- Only NULL values
SELECT customer_id, email
FROM customers
WHERE email IS NULL;
 
-- Only empty strings
SELECT customer_id, email
FROM customers
WHERE email = '';
 
-- Both NULL and empty strings
SELECT customer_id, email
FROM customers
WHERE email IS NULL
   OR email = '';

The combined condition is appropriate when your application treats empty text and NULL as equivalent missing-data cases. It does not automatically remove whitespace-only strings such as ' '.

5. Practical Example: Incomplete User Profiles

Registration forms often collect information gradually. A user may create an account before adding a phone number or profile description. Nullable columns allow the database to represent those incomplete fields.

You can query incomplete profiles with IS NULL and complete specific fields with IS NOT NULL.

Find Profiles Missing a Phone Number

sql

1
2
3
4
5
6
7
8
SELECT
  user_id,
  full_name,
  email,
  phone_number
FROM users
WHERE phone_number IS NULL
ORDER BY user_id ASC;

This returns users whose phone_number is NULL. A user with an empty string in phone_number is not included, because an empty string is not NULL in PostgreSQL.

6. Practical Pattern: Soft Deletion

Applications sometimes need to hide a record without physically removing it from the database. This is called soft deletion.

A common design uses a nullable deleted_at timestamp. When a record is active, deleted_at is NULL. When the application soft-deletes the record, it sets deleted_at to the deletion timestamp.

This can preserve historical records for auditing, recovery, or business workflows. However, soft deletion does not automatically satisfy every legal retention requirement, and applications must consistently filter deleted records where appropriate.

Find Active and Soft-Deleted Articles

sql

1
2
3
4
5
6
7
8
9
10
11
-- Active articles
SELECT article_id, title, published_at
FROM articles
WHERE deleted_at IS NULL
ORDER BY published_at DESC, article_id DESC;
 
-- Soft-deleted articles
SELECT article_id, title, deleted_at
FROM articles
WHERE deleted_at IS NOT NULL
ORDER BY deleted_at DESC, article_id DESC;

The first query returns articles that have not been marked as deleted. The second returns articles with a recorded deletion timestamp. The additional article_id sort key makes the ordering deterministic when timestamps tie.

7. Marking a Record as Soft-Deleted

In a soft-deletion design, the application normally updates the deleted_at field rather than immediately deleting the row. The following PostgreSQL statement marks an article as deleted.

Update the Deletion Timestamp

sql

1
2
3
4
5
UPDATE articles
SET deleted_at = CURRENT_TIMESTAMP
WHERE article_id = 54
  AND deleted_at IS NULL
RETURNING article_id, title, deleted_at;

The additional IS NULL condition avoids updating an article that is already marked as deleted. RETURNING displays the updated row in PostgreSQL. Run this only when you intend to make the change.

8. Restoring a Soft-Deleted Record

If the application supports restoring records, it can set deleted_at back to NULL. This makes the row appear active under the usual soft-deletion filter.

Restore an Article

sql

1
2
3
4
5
UPDATE articles
SET deleted_at = NULL
WHERE article_id = 54
  AND deleted_at IS NOT NULL
RETURNING article_id, title, deleted_at;

The statement restores the article by clearing its deletion timestamp. Your application should check permissions and any related business rules before allowing restoration.

9. Understanding IS DISTINCT FROM

IS DISTINCT FROM is a null-safe comparison supported by PostgreSQL. Unlike the ordinary <> operator, it treats NULL as a comparable state and returns TRUE or FALSE instead of UNKNOWN.

It returns TRUE when the two values differ, including when one is NULL and the other is not. It returns FALSE when the values are equal or both are NULL.

Compare Values Safely

sql

1
2
3
4
5
SELECT
  5 IS DISTINCT FROM 5 AS same_values,
  5 IS DISTINCT FROM 10 AS different_values,
  5 IS DISTINCT FROM NULL AS value_and_null,
  NULL IS DISTINCT FROM NULL AS two_nulls;

Output

The expression returns TRUE when the values are distinct. Two NULL values are not distinct from one another under this operator.

10. IS NOT DISTINCT FROM

IS NOT DISTINCT FROM is the null-safe counterpart to IS DISTINCT FROM. It returns TRUE when the values are equal or both are NULL. It returns FALSE when they differ.

This can be useful when comparing optional values, checking whether a value changed, or matching values where NULL should be treated as equivalent to another NULL.

Null-Safe Equality

sql

1
2
3
4
5
SELECT
  5 IS NOT DISTINCT FROM 5 AS equal_values,
  5 IS NOT DISTINCT FROM 10 AS unequal_values,
  5 IS NOT DISTINCT FROM NULL AS value_and_null,
  NULL IS NOT DISTINCT FROM NULL AS both_null;

Output

Unlike =, IS NOT DISTINCT FROM returns TRUE when both sides are NULL. It always produces a definite Boolean result.

11. Comparison Truth Table

Compare ordinary equality with null-safe equality to understand why the latter is useful when NULL is a valid state in your data model.

Find Rows Whose Status Changed

sql

1
2
3
4
5
6
7
8
SELECT
  old_data.record_id,
  old_data.status AS old_status,
  new_data.status AS new_status
FROM old_data
JOIN new_data
  ON new_data.record_id = old_data.record_id
WHERE old_data.status IS DISTINCT FROM new_data.status;

This PostgreSQL query finds records whose statuses differ, including changes between NULL and a non-NULL status. The record_id is assumed to uniquely identify corresponding rows.

Ordinary Equality Versus Null-Safe Equality

Values	a = b	a IS DISTINCT FROM b	a IS NOT DISTINCT FROM b
5 and 5	TRUE	FALSE	TRUE
5 and 10	FALSE	TRUE	FALSE
5 and NULL	UNKNOWN	TRUE	FALSE
NULL and NULL	UNKNOWN	FALSE	TRUE

12. NULL Values in JOIN Conditions

An ordinary equality join such as a.optional_code = b.optional_code does not match rows when both optional_code values are NULL. The equality expression evaluates to UNKNOWN, so it is not a successful join condition.

This behavior is often correct because NULL means the value is unknown. If the application explicitly requires two NULL values to count as a match, PostgreSQL's IS NOT DISTINCT FROM can express that rule. Use it deliberately because it changes the meaning of the join.

Ordinary Join Versus Null-Safe Join

sql

1
2
3
4
5
6
7
8
9
10
11
-- Ordinary equality: NULL does not match NULL
SELECT a.record_id, b.record_id
FROM table_a AS a
JOIN table_b AS b
  ON a.optional_code = b.optional_code;
 
-- Null-safe equality: NULL matches NULL
SELECT a.record_id, b.record_id
FROM table_a AS a
JOIN table_b AS b
  ON a.optional_code IS NOT DISTINCT FROM b.optional_code;

The second query may produce many matches if multiple rows on both sides have NULL in optional_code. Use it only when treating NULL values as equal is intended and the possible match multiplicity is understood.

13. Finding Missing Relationships

A nullable foreign-key column can indicate that a row is not associated with a parent record. For example, an employee may not yet be assigned to a department.

IS NULL can identify those records, while IS NOT NULL finds employees with a department identifier recorded. Note that a non-NULL identifier does not, by itself, prove that the referenced record exists unless the database enforces the appropriate foreign-key constraint.

Employees Without a Department

sql

1
2
3
4
5
6
7
8
SELECT employee_id, full_name, department_id
FROM employees
WHERE department_id IS NULL;
 
-- Employees with a department ID recorded
SELECT employee_id, full_name, department_id
FROM employees
WHERE department_id IS NOT NULL;

The first query finds employees whose department_id is NULL. The second finds those with a non-NULL department_id. A foreign-key constraint can ensure that non-NULL references point to valid departments.

14. Indexes and IS NULL in PostgreSQL

PostgreSQL B-tree indexes can store NULL values. Therefore, an index on a column can sometimes support queries that use IS NULL or IS NOT NULL.

However, having an index does not guarantee that PostgreSQL will use it. If most rows match the predicate, a sequential scan may be cheaper. Table size, data distribution, available indexes, query shape, and statistics all affect the execution plan.

Use EXPLAIN to inspect the plan instead of assuming that a predicate is fast because it has an index.

Create and Inspect an Index

sql

1
2
3
4
5
6
7
8
9
-- Create an index on the nullable column
CREATE INDEX idx_users_phone_number
ON users (phone_number);
 
-- Inspect the plan
EXPLAIN
SELECT user_id, email
FROM users
WHERE phone_number IS NULL;

The index may help the query, depending on the table and data distribution. EXPLAIN shows the planned operations. EXPLAIN ANALYZE executes the query and reports actual measurements, so use it thoughtfully.

15. Partial Indexes for Soft-Deleted Data

If an application frequently queries only active articles, a partial index can index only rows whose deleted_at value is NULL. This can reduce the index size compared with indexing every row, particularly when many rows have been soft-deleted.

The index is useful only when its predicate and indexed columns suit the application's queries. It is not automatically the best index for every table.

Index Active Articles

sql

1
2
3
4
5
6
7
8
9
10
CREATE INDEX idx_articles_active_published_at
ON articles (published_at DESC, article_id DESC)
WHERE deleted_at IS NULL;
 
-- Query that matches the partial-index condition
SELECT article_id, title, published_at
FROM articles
WHERE deleted_at IS NULL
ORDER BY published_at DESC, article_id DESC
LIMIT 20;

The partial index contains rows for which deleted_at IS NULL. PostgreSQL may use it for the query because the query includes the matching condition and ordering columns. Verify with EXPLAIN.

16. IS NULL with AND and OR

NULL predicates can be combined with other conditions. Parentheses make the intended logic easier to understand and reduce mistakes when mixing AND and OR.

Filter Missing Emails or Phone Numbers

sql

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
-- Users missing either email or phone
SELECT user_id, email, phone_number
FROM users
WHERE email IS NULL
   OR phone_number IS NULL;
 
-- Users missing both email and phone
SELECT user_id, email, phone_number
FROM users
WHERE email IS NULL
  AND phone_number IS NULL;
 
-- Users with a missing email who are active
SELECT user_id, email, is_active
FROM users
WHERE email IS NULL
  AND is_active = TRUE;

OR selects rows where either missing-value condition is true. AND requires both conditions to be true. The final query also requires is_active to be TRUE.

17. Common Mistakes

Review these common errors before using NULL predicates in production queries.

Incorrect and Correct Queries

sql

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
-- Mistake 1: Use of = NULL
SELECT *
FROM customers
WHERE phone = NULL;
 
-- Correct
SELECT *
FROM customers
WHERE phone IS NULL;
 
 
-- Mistake 2: Negating a NULL comparison
SELECT *
FROM customers
WHERE NOT (phone = NULL);
 
-- Correct
SELECT *
FROM customers
WHERE phone IS NOT NULL;
 
 
-- Mistake 3: Confusing empty text with NULL
SELECT *
FROM customers
WHERE email IS NULL;
 
-- If both NULL and empty text count as missing:
SELECT *
FROM customers
WHERE email IS NULL
   OR email = '';
 
 
-- Mistake 4: Assuming every IS NULL query uses an index
EXPLAIN
SELECT *
FROM users
WHERE phone_number IS NULL;
 
 
-- Mistake 5: Comparing nullable values with ordinary equality
SELECT *
FROM old_data AS o
JOIN new_data AS n
  ON o.status = n.status;
 
-- If null-safe equality is intended in PostgreSQL:
SELECT *
FROM old_data AS o
JOIN new_data AS n
  ON o.status IS NOT DISTINCT FROM n.status;

NOT UNKNOWN remains UNKNOWN, so NOT (phone = NULL) is not a valid way to find present phone values. Use IS NOT NULL. For joins, choose ordinary or null-safe equality according to the intended business rule.

18. Guided Practice

Try each exercise before looking at the solutions. Assume the tables and columns mentioned exist in PostgreSQL.

Practice Tasks

sql

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
-- Task 1:
-- Find users whose email_verified_at is NULL.
 
-- Task 2:
-- Find products whose description is not NULL.
 
-- Task 3:
-- Find active articles where deleted_at is NULL.
 
-- Task 4:
-- Find customers whose email is either NULL
-- or an empty string.
 
-- Task 5:
-- Compare old_data.status and new_data.status
-- and return rows where the values differ,
-- including differences involving NULL.
 
-- Task 6:
-- Find employees without a department assignment.
 
-- Task 7:
-- Write a query that matches two nullable
-- optional_code columns using null-safe equality.
 
-- Task 8:
-- Create a partial index for active articles
-- ordered by published_at and article_id.

Choose the appropriate predicate for each task. For Task 5, use IS DISTINCT FROM. For Task 7, use IS NOT DISTINCT FROM if NULL-to-NULL matching is intended.

19. Practice Solutions

Compare your queries with these solutions and make sure you understand why each predicate is appropriate.

Solution Queries

sql

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
-- Task 1
SELECT user_id, email
FROM users
WHERE email_verified_at IS NULL;
 
-- Task 2
SELECT product_id, description
FROM products
WHERE description IS NOT NULL;
 
-- Task 3
SELECT article_id, title
FROM articles
WHERE deleted_at IS NULL
ORDER BY published_at DESC, article_id DESC;
 
-- Task 4
SELECT customer_id, email
FROM customers
WHERE email IS NULL
   OR email = '';
 
-- Task 5
SELECT o.record_id, o.status, n.status
FROM old_data AS o
JOIN new_data AS n
  ON n.record_id = o.record_id
WHERE o.status IS DISTINCT FROM n.status;
 
-- Task 6
SELECT employee_id, full_name
FROM employees
WHERE department_id IS NULL;
 
-- Task 7
SELECT a.record_id, b.record_id
FROM table_a AS a
JOIN table_b AS b
  ON a.optional_code IS NOT DISTINCT FROM b.optional_code;
 
-- Task 8
CREATE INDEX idx_articles_active_published_at
ON articles (published_at DESC, article_id DESC)
WHERE deleted_at IS NULL;

Task 5 assumes record_id identifies corresponding rows in both tables. Task 7 can create multiple matches when many rows contain NULL, so use null-safe joins only when that behavior is intended.

20. Multiple-Choice Questions

Select one best answer for each question.

Questions 1–5

sql

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
-- Q1. Which predicate finds NULL values?
-- A. = NULL
-- B. IS NULL
-- C. == NULL
-- D. <> NULL
 
-- Q2. What does IS NOT NULL return?
-- A. TRUE when the value is NULL
-- B. TRUE when the value is not NULL
-- C. UNKNOWN for every row
-- D. An integer
 
-- Q3. In PostgreSQL, is an empty string the same as NULL?
-- A. Yes
-- B. No
-- C. Only for numeric columns
-- D. Only in a WHERE clause
 
-- Q4. What is NULL = NULL?
-- A. TRUE
-- B. FALSE
-- C. UNKNOWN
-- D. Zero
 
-- Q5. What does NOT UNKNOWN evaluate to?
-- A. TRUE
-- B. FALSE
-- C. UNKNOWN
-- D. NULL error

These questions test NULL predicates, three-valued logic, and the difference between NULL and empty text.

21. Quiz Answer Key

Check your answers after attempting the questions.

Questions 6–12

sql

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
-- Q6. Which operator provides null-safe difference?
-- A. <>
-- B. =
-- C. IS DISTINCT FROM
-- D. LIKE
-- Answer: C
 
-- Q7. What is NULL IS DISTINCT FROM NULL?
-- A. TRUE
-- B. FALSE
-- C. UNKNOWN
-- D. An error
-- Answer: B
 
-- Q8. What is NULL IS NOT DISTINCT FROM NULL?
-- A. TRUE
-- B. FALSE
-- C. UNKNOWN
-- D. An error
-- Answer: A
 
-- Q9. What does deleted_at IS NULL commonly mean
-- in a soft-deletion design?
-- A. The record is always physically deleted
-- B. The record has not been marked as deleted
-- C. The record is invalid
-- D. The record cannot be updated
-- Answer: B
 
-- Q10. Can PostgreSQL B-tree indexes contain NULL values?
-- A. No
-- B. Yes
-- C. Only in primary keys
-- D. Only in text columns
-- Answer: B
 
-- Q11. Does an index guarantee that IS NULL is fast?
-- A. Yes, always
-- B. No; the query plan and data distribution matter
-- C. Only with LIMIT
-- D. Only with DISTINCT
-- Answer: B
 
-- Q12. What does NOT (column = NULL) do?
-- A. Correctly finds non-NULL values
-- B. Deletes NULL values
-- C. Still evaluates to UNKNOWN for NULL comparisons
-- D. Converts NULL to FALSE
-- Answer: C

IS DISTINCT FROM and IS NOT DISTINCT FROM return definite Boolean results. Indexes can help NULL predicates, but their usefulness depends on the query and execution plan.

22. SQL Interview Questions and Answers

Practice explaining these concepts clearly, then write a small query for each one.

Interview Revision

sql

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
-- Q1. What is IS NULL?
-- A predicate that tests whether an expression is NULL.
 
-- Q2. What is IS NOT NULL?
-- A predicate that tests whether an expression is not NULL.
 
-- Q3. Why does = NULL not work?
-- Ordinary comparison with NULL evaluates to UNKNOWN.
 
-- Q4. Why does NOT (column = NULL) not work?
-- NOT UNKNOWN remains UNKNOWN.
 
-- Q5. What is the difference between NULL and ''?
-- NULL represents missing/unknown data; '' is empty text.
 
-- Q6. What is soft deletion?
-- Marking a record as deleted without physically
-- removing its row, often with a deleted_at timestamp.
 
-- Q7. How do you find active soft-deleted records?
-- Active: WHERE deleted_at IS NULL.
-- Soft-deleted: WHERE deleted_at IS NOT NULL.
 
-- Q8. What is IS DISTINCT FROM?
-- A null-safe comparison that returns TRUE when
-- values differ, including when only one is NULL.
 
-- Q9. What is IS NOT DISTINCT FROM?
-- A null-safe comparison that returns TRUE when
-- values are equal or both NULL.
 
-- Q10. Can NULL values be stored in PostgreSQL B-tree indexes?
-- Yes, although index use depends on the query plan.
 
-- Q11. What is a partial index?
-- An index that includes only rows satisfying its predicate.
 
-- Q12. How can a partial index help soft deletion?
-- It can index only rows with deleted_at IS NULL.
 
-- Q13. Do ordinary equality joins match NULL to NULL?
-- No. The equality comparison evaluates to UNKNOWN.
 
-- Q14. Can IS NOT DISTINCT FROM match NULL to NULL?
-- Yes. It provides null-safe equality semantics.
 
-- Q15. Does IS NOT NULL guarantee a valid email or phone?
-- No. It only guarantees that the value is not NULL.
 
-- Q16. Does an index guarantee faster execution?
-- No. PostgreSQL chooses a plan based on estimated cost.
 
-- Q17. Why use EXPLAIN?
-- To inspect the planned execution steps of a query.
 
-- Q18. What should you consider before null-safe joining?
-- Whether NULL-to-NULL matches are correct and whether
-- multiple NULL rows could produce many joined results.

Interviewers may ask you to distinguish a missing value from an invalid value. IS NOT NULL proves only that a value exists, not that it is correct.

23. Lesson Summary

Use IS NULL to identify missing values and IS NOT NULL to find values that are present. Ordinary comparisons such as = NULL and <> NULL do not test for missing values because they evaluate to UNKNOWN. Negating such a comparison does not fix the problem.

Soft-deletion designs commonly use deleted_at IS NULL for active records and deleted_at IS NOT NULL for records marked as deleted. PostgreSQL's IS DISTINCT FROM and IS NOT DISTINCT FROM operators allow null-safe comparisons when NULL values need explicit comparison semantics.

PostgreSQL B-tree indexes can contain NULL values, and partial indexes may improve recurring queries such as active-record lookups. However, always inspect the execution plan rather than assuming an index will be used. Finally, ordinary equality joins do not match NULL to NULL, so use null-safe equality only when it matches the application's intended behavior.

24. Final Checklist

Before moving on, make sure you can:

- Use IS NULL and IS NOT NULL correctly.
- Explain why = NULL and NOT (column = NULL) are incorrect checks.
- Distinguish NULL from an empty string.
- Filter active and soft-deleted records using deleted_at.
- Explain the difference between IS DISTINCT FROM and IS NOT DISTINCT FROM.
- Understand how NULL affects ordinary equality joins.
- Explain how B-tree indexes and partial indexes can help some queries.
- Use EXPLAIN to inspect query plans.
- Explain why IS NOT NULL does not prove that data is valid.

Common pitfalls to avoid

Real debugging lessons and exact syntax contrasts to avoid runtime bugs

#1 Using = NULL to find missing values

Why it happens: Developers assume NULL behaves like an ordinary value in comparisons.

Incorrect Approach

sql

SELECT * FROM customers WHERE phone = NULL;

Correct Solution

sql

SELECT * FROM customers WHERE phone IS NULL;

Key takeaway: Ordinary comparisons involving NULL evaluate to UNKNOWN. Use IS NULL to test for missing values.

#2 Using NOT (column = NULL) to find non-NULL values

Why it happens: Developers assume NOT reverses the comparison into a successful missing-value check.

Incorrect Approach

sql

SELECT * FROM customers WHERE NOT (phone = NULL);

Correct Solution

sql

SELECT * FROM customers WHERE phone IS NOT NULL;

Key takeaway: NOT UNKNOWN is still UNKNOWN. The WHERE clause therefore does not select the intended rows.

#3 Assuming an empty string is NULL

Why it happens: Both can represent missing text from the application's perspective.

Incorrect Approach

sql

SELECT * FROM customers WHERE email IS NULL;

Correct Solution

sql

SELECT * FROM customers WHERE email IS NULL OR email = '';

Key takeaway: Use the combined condition only if the application treats both NULL and empty text as missing. Whitespace-only text needs additional handling if it should also count as missing.

#4 Assuming IS NOT NULL means the data is valid

Why it happens: A present value can still be malformed or inaccurate.

Incorrect Approach

sql

SELECT * FROM users WHERE email IS NOT NULL;

Correct Solution

sql

SELECT * FROM users WHERE email IS NOT NULL AND email <> '';

Key takeaway: The corrected query also excludes empty strings, but it still does not validate email format. Presence and validity are different requirements.

#5 Assuming an index always makes IS NULL fast

Why it happens: Developers expect an index to be used whenever a column appears in WHERE.

Incorrect Approach

sql

-- Assuming the index will always be used
SELECT * FROM users WHERE phone_number IS NULL;

Correct Solution

sql

EXPLAIN SELECT * FROM users WHERE phone_number IS NULL;

Key takeaway: PostgreSQL decides whether an index is worthwhile based on estimated cost, data distribution, and the query. Inspect the plan to verify the actual strategy.

Ready for hands-on challenges?

Test your skills with shuffled coding challenges in the Practice Arena.

Practice Arena →

Key takeaways

IS NULL checks whether a value is NULL; IS NOT NULL checks whether it is not NULL.
Ordinary comparisons such as = NULL and <> NULL evaluate to UNKNOWN rather than TRUE or FALSE.
A WHERE clause returns only rows whose condition evaluates to TRUE.
An empty string is not NULL in PostgreSQL.
Soft-deletion systems often use deleted_at IS NULL to identify records that have not been soft-deleted.
IS DISTINCT FROM provides null-safe comparison and always returns TRUE or FALSE.
IS NOT DISTINCT FROM treats two NULL values as not distinct from one another.
PostgreSQL B-tree indexes can contain NULL values, but an index does not guarantee that every IS NULL query will be fast.
Partial indexes can help queries that repeatedly filter on a condition such as deleted_at IS NULL.
NULL values in join columns require careful handling because ordinary equality does not match NULL to NULL.

Was this lesson helpful?

Found an error? Report an issue