Handling NULL Values (IS NULL)0%

Handling NULL Values (IS NULL)

Beginner12 min read•Updated: 2026-09-12

Handling NULL Values: IS NULL, IS NOT NULL, and Three-Valued Logic

No concept in relational databases trips up beginners and seasoned programmers more consistently than NULL. Because NULL represents missing or unknown information, standard equality operators (= and !=) do not work as expected. To inspect, filter, and handle missing values properly, you must use IS NULL and IS NOT NULL.

1. What is `NULL`? (And What It Isn't)

 NULL is NOT equal to 0.         (0 is a known, definitive integer).
 NULL is NOT equal to "" (empty). ("" is a known, definitive string of length 0).
 NULL represents ABSENCE OF DATA / UNKNOWN.

The Fundamental Rule:

Because NULL represents an unknown, nothing can equal NULL—not even another NULL!

1-- Let's test this in MySQL:
2SELECT NULL = NULL;   -- Returns NULL (Not 1, not 0, but UNKNOWN!)
3SELECT NULL != NULL;  -- Returns NULL (UNKNOWN!)
4SELECT NULL = 0;      -- Returns NULL (UNKNOWN!)
5SELECT NULL = '';     -- Returns NULL (UNKNOWN!)

2. The Correct Syntax: `IS NULL` and `IS NOT NULL`

Because standard comparison operators fail on NULL, SQL provides dedicated unary operators:

1-- Find all orders that have NOT shipped yet (shipped_date is unrecorded):
2SELECT order_id, customer_id, order_date
3FROM orders
4WHERE shipped_date IS NULL;
5 
6-- Find all customers who HAVE provided their phone number:
7SELECT customer_id, full_name, phone_number
8FROM customers
9WHERE phone_number IS NOT NULL;

3. The Dangerous Trap: `WHERE column = NULL`

Consider this common junior developer error:

1-- WRONG! NEVER WRITE THIS!
2SELECT * FROM orders WHERE shipped_date = NULL;

What happens when MySQL executes this?

1
For every row, MySQL evaluates shipped_date = NULL.
2
Regardless of whether shipped_date contains '2026-03-10' or NULL, the expression evaluates to UNKNOWN!
3
Because WHERE only returns rows that evaluate strictly to TRUE, this query will return ZERO rows 100% of the time!

4. Sorting Behavior of `NULL` in MySQL

How does ORDER BY handle rows with NULL values?

In MySQL, NULL values are treated as the lowest possible values.
In ORDER BY col ASC, rows containing NULL appear at the very beginning.
In ORDER BY col DESC, rows containing NULL appear at the very end.

1-- Put customers without a referral code at the bottom:
2SELECT customer_id, full_name, referral_code
3FROM customers
4ORDER BY referral_code IS NULL ASC, referral_code ASC;

5. Replacing NULLs in Output: `IFNULL()` and `COALESCE()`

When presenting query results to users or frontend applications, displaying raw NULL values looks unpolished. You can replace NULL with friendly fallback values:

`IFNULL(expression, fallback_value)` (MySQL Specific):

1SELECT 
2    full_name,
3    IFNULL(phone_number, 'No Phone Provided') AS contact_phone
4FROM customers;

`COALESCE(val1, val2, ..., valN)` (ANSI SQL Standard):

Returns the first non-null value in its parameter list:

1SELECT 
2    full_name,
3    COALESCE(work_phone, mobile_phone, home_phone, 'N/A') AS primary_contact
4FROM customer_directory;

6. Best Practices & Common Pitfalls

Avoid Nullable Foreign Keys Where Possible: Making foreign keys nullable allows orphaned child rows with no parent entity. Only make foreign keys nullable when the relationship is genuinely optional.
*Count Gotcha (COUNT(col) vs `COUNT()`):**
COUNT(*) counts all rows regardless of nullability.
COUNT(column_name) counts only rows where that column is NOT NULL!

Interactive Quiz• Knowledge Check

Multiple Choice Questions

Question 1

Which SQL operator correctly checks if a column contains a missing or unrecorded value?

Question 2

What will the query SELECT * FROM employees WHERE bonus = NULL; return?

Question 3

How does MySQL sort NULL values when executing an ORDER BY column ASC statement?

Question 4

Which function returns the first non-NULL value from a comma-separated list of arguments?

Question 5

What is the difference between COUNT(*) and COUNT(commission_pct) on a table with 100 rows where 20 rows have NULL commissions?

Next Lesson

Sorting Data with ORDER BY

Continue learning with hands-on practice, examples, and exercises in the upcoming topic.

Related Lessons

Previous Lesson	Next Lesson
Pattern Matching with LIKE & Wildcards	Sorting Data with ORDER BY

Practice Quiz

Test your understanding of this lesson with 5 questions. Each question has one correct answer.

Previous Next

PreviousPrevNext

MSK Institute • Shikohabad

Loading Page Content...

Preparing courses, live labs, and learning resources.सामग्री लोड हो रही है, कृपया प्रतीक्षा करें...

Connecting serverPlease wait...

💻 Practical Labs🚀 Free Demo📜 Verified Certificates

Sorting Data with ORDER BY

Handling NULL Values (IS NULL)

Beginner12 min read•Updated: 2026-09-12

Study Materials Cheatsheet

Handling NULL Values: IS NULL, IS NOT NULL, and Three-Valued Logic

1. What is `NULL`? (And What It Isn't)

 NULL is NOT equal to 0.         (0 is a known, definitive integer).
 NULL is NOT equal to "" (empty). ("" is a known, definitive string of length 0).
 NULL represents ABSENCE OF DATA / UNKNOWN.

The Fundamental Rule:

Because NULL represents an unknown, nothing can equal NULL—not even another NULL!

1-- Let's test this in MySQL:
2SELECT NULL = NULL;   -- Returns NULL (Not 1, not 0, but UNKNOWN!)
3SELECT NULL != NULL;  -- Returns NULL (UNKNOWN!)
4SELECT NULL = 0;      -- Returns NULL (UNKNOWN!)
5SELECT NULL = '';     -- Returns NULL (UNKNOWN!)

2. The Correct Syntax: `IS NULL` and `IS NOT NULL`

Because standard comparison operators fail on NULL, SQL provides dedicated unary operators:

1-- Find all orders that have NOT shipped yet (shipped_date is unrecorded):
2SELECT order_id, customer_id, order_date
3FROM orders
4WHERE shipped_date IS NULL;
5 
6-- Find all customers who HAVE provided their phone number:
7SELECT customer_id, full_name, phone_number
8FROM customers
9WHERE phone_number IS NOT NULL;

3. The Dangerous Trap: `WHERE column = NULL`

Consider this common junior developer error:

1-- WRONG! NEVER WRITE THIS!
2SELECT * FROM orders WHERE shipped_date = NULL;

What happens when MySQL executes this?

1
For every row, MySQL evaluates shipped_date = NULL.
2
Regardless of whether shipped_date contains '2026-03-10' or NULL, the expression evaluates to UNKNOWN!
3
Because WHERE only returns rows that evaluate strictly to TRUE, this query will return ZERO rows 100% of the time!

4. Sorting Behavior of `NULL` in MySQL

How does ORDER BY handle rows with NULL values?

In MySQL, NULL values are treated as the lowest possible values.
In ORDER BY col ASC, rows containing NULL appear at the very beginning.
In ORDER BY col DESC, rows containing NULL appear at the very end.

1-- Put customers without a referral code at the bottom:
2SELECT customer_id, full_name, referral_code
3FROM customers
4ORDER BY referral_code IS NULL ASC, referral_code ASC;

5. Replacing NULLs in Output: `IFNULL()` and `COALESCE()`

When presenting query results to users or frontend applications, displaying raw NULL values looks unpolished. You can replace NULL with friendly fallback values:

`IFNULL(expression, fallback_value)` (MySQL Specific):

1SELECT 
2    full_name,
3    IFNULL(phone_number, 'No Phone Provided') AS contact_phone
4FROM customers;

`COALESCE(val1, val2, ..., valN)` (ANSI SQL Standard):

Returns the first non-null value in its parameter list:

1SELECT 
2    full_name,
3    COALESCE(work_phone, mobile_phone, home_phone, 'N/A') AS primary_contact
4FROM customer_directory;

6. Best Practices & Common Pitfalls

Avoid Nullable Foreign Keys Where Possible: Making foreign keys nullable allows orphaned child rows with no parent entity. Only make foreign keys nullable when the relationship is genuinely optional.
*Count Gotcha (COUNT(col) vs `COUNT()`):**
COUNT(*) counts all rows regardless of nullability.
COUNT(column_name) counts only rows where that column is NOT NULL!

Interactive Quiz• Knowledge Check

Multiple Choice Questions

Question 1

Which SQL operator correctly checks if a column contains a missing or unrecorded value?

Question 2

What will the query SELECT * FROM employees WHERE bonus = NULL; return?

Question 3

How does MySQL sort NULL values when executing an ORDER BY column ASC statement?

Question 4

Which function returns the first non-NULL value from a comma-separated list of arguments?

Question 5

What is the difference between COUNT(*) and COUNT(commission_pct) on a table with 100 rows where 20 rows have NULL commissions?

Next Lesson

Sorting Data with ORDER BY

Continue learning with hands-on practice, examples, and exercises in the upcoming topic.

Related Lessons

Previous Lesson	Next Lesson
Pattern Matching with LIKE & Wildcards	Sorting Data with ORDER BY

Practice Quiz

Test your understanding of this lesson with 5 questions. Each question has one correct answer.

Previous Next

Handling NULL Values: IS NULL, IS NOT NULL, and Three-Valued Logic

1. What is NULL? (And What It Isn't)

The Fundamental Rule:

2. The Correct Syntax: IS NULL and IS NOT NULL

3. The Dangerous Trap: WHERE column = NULL

What happens when MySQL executes this?

4. Sorting Behavior of NULL in MySQL

5. Replacing NULLs in Output: IFNULL() and COALESCE()

IFNULL(expression, fallback_value) (MySQL Specific):

COALESCE(val1, val2, ..., valN) (ANSI SQL Standard):

6. Best Practices & Common Pitfalls

Multiple Choice Questions

Sorting Data with ORDER BY

Related Lessons

Practice Quiz

Loading Page Content...

Handling NULL Values: IS NULL, IS NOT NULL, and Three-Valued Logic

1. What is NULL? (And What It Isn't)

The Fundamental Rule:

2. The Correct Syntax: IS NULL and IS NOT NULL

3. The Dangerous Trap: WHERE column = NULL

What happens when MySQL executes this?

4. Sorting Behavior of NULL in MySQL

5. Replacing NULLs in Output: IFNULL() and COALESCE()

IFNULL(expression, fallback_value) (MySQL Specific):

COALESCE(val1, val2, ..., valN) (ANSI SQL Standard):

6. Best Practices & Common Pitfalls

Multiple Choice Questions

Sorting Data with ORDER BY

Related Lessons

Practice Quiz

1. What is `NULL`? (And What It Isn't)

2. The Correct Syntax: `IS NULL` and `IS NOT NULL`

3. The Dangerous Trap: `WHERE column = NULL`

4. Sorting Behavior of `NULL` in MySQL

5. Replacing NULLs in Output: `IFNULL()` and `COALESCE()`

`IFNULL(expression, fallback_value)` (MySQL Specific):

`COALESCE(val1, val2, ..., valN)` (ANSI SQL Standard):

1. What is `NULL`? (And What It Isn't)

2. The Correct Syntax: `IS NULL` and `IS NOT NULL`

3. The Dangerous Trap: `WHERE column = NULL`

4. Sorting Behavior of `NULL` in MySQL

5. Replacing NULLs in Output: `IFNULL()` and `COALESCE()`

`IFNULL(expression, fallback_value)` (MySQL Specific):

`COALESCE(val1, val2, ..., valN)` (ANSI SQL Standard):