🧠 Understanding SQL IS NULL and IS NOT NULL: A Beginner’s Guide

In SQL, NULL represents missing, undefined, or unknown data. It’s not the same as zero, an empty string, or false — it literally means nothing. Handling NULL values correctly is crucial when you’re querying a database. That’s where IS NULL and IS NOT NULL come into play.

In this article, we’ll explore what NULL means in SQL, how to use IS NULL and IS NOT NULL in queries, and why they matter. Whether you’re cleaning data, filtering rows, or doing advanced reporting — this concept is essential.

🔍 What is NULL in SQL?

NULL is a special marker used in SQL to indicate that a data value does not exist in the database. It’s not a value like 0 or an empty string (''), but a placeholder for unknown or missing information.

🧾 Example:

Let’s say we have a students table:

Here, Bob’s email is NULL, meaning it hasn’t been entered or is unknown.

✅ IS NULL – Finding Missing Values

The IS NULL operator is used in SQL to find records where a column has a NULL value.

🧾 Syntax:

SELECT column1, column2
FROM table_name
WHERE column_name IS NULL;

🧠 Example:

To find students without an email:

SELECT name
FROM students
WHERE email IS NULL;

This will return:

❌ Common Mistake: Using = for NULL

You cannot use the = operator to compare with NULL.

Wrong:

WHERE email = NULL;  -- This won't work!

Correct:

WHERE email IS NULL;

The reason is: in SQL, NULL is not equal to anything, not even to another NULL. That’s why you must use IS NULL.

🚫 IS NOT NULL – Finding Present Values

The IS NOT NULL operator helps you filter rows where the column has an actual value (not missing).

🧾 Syntax:

SELECT column1, column2
FROM table_name
WHERE column_name IS NOT NULL;

🧠 Example:

To list students who have an email address:

SELECT name, email
FROM students
WHERE email IS NOT NULL;

Result:

⚙️ Use Case: Filtering Clean Data

Often, you may want to run reports or analytics only on records that have complete data.

Example:

SELECT COUNT(*)
FROM orders
WHERE delivery_date IS NOT NULL;

This counts only the orders that have been delivered.

🔄 Use with UPDATE or DELETE

You can also use IS NULL and IS NOT NULL with UPDATE or DELETE commands to manage missing data.

Update all NULL values:

UPDATE students
SET email = 'noemail@school.com'
WHERE email IS NULL;

This replaces missing emails with a placeholder value.

Delete rows with missing critical info:

DELETE FROM employees
WHERE salary IS NULL;

🧮 NULLs in Aggregations

When you perform calculations like AVG, SUM, MAX, etc., SQL automatically ignores NULL values.

Example:

SELECT AVG(score)
FROM exams;

This will calculate the average based on rows where score IS NOT NULL.

🧩 Real-World Examples

✅ 1. Get customers without phone numbers

SELECT customer_name
FROM customers
WHERE phone IS NULL;

✅ 2. Find all users who filled out optional comments

SELECT user_id
FROM feedback
WHERE comment IS NOT NULL;

✅ 3. Replace NULLs using COALESCE

To display a default value in place of NULL, use COALESCE:

SELECT name, COALESCE(email, 'No Email Provided') AS contact_email
FROM students;

📋 Summary Table

🧠 Pro Tips

• Always use IS NULL / IS NOT NULL for checking NULLs — never = or !=.
• NULLs are ignored in most aggregate functions (AVG, SUM, COUNT).
• Use COALESCE() or IFNULL() to replace NULLs in the result set.
• Be cautious with NOT IN and NULL — it can return no rows if NULL is present in the list.

✅ Final Thoughts

Understanding and properly using IS NULL and IS NOT NULL in SQL is key to writing accurate and meaningful queries. Whether you’re identifying missing data, cleaning up a dataset, or ensuring accurate reporting, knowing how to work with NULL will make you a more powerful SQL user.

By mastering these operators, you’ll be better equipped to handle real-world datasets, which often contain incomplete or missing values.