🕳️ SQL `IS NULL` – Handling Missing or Unknown Data in SQL

In SQL, NULL doesn’t mean zero or empty—it means unknown.
The IS NULL operator helps you filter rows with missing or undefined values.

Let’s explore how to use IS NULL and IS NOT NULL effectively in your SQL queries.

📌 What is NULL in SQL?

In SQL, NULL represents a missing, unknown, or undefined value. It is not equivalent to 0, an empty string '', or False.

Think of NULL as a placeholder saying:

“We don’t know what this value is (yet).”

This won’t work:

-- ❌ Incorrect
SELECT * FROM users
WHERE email = NULL;

✅ Instead, use:

-- ✅ Correct
SELECT * FROM users
WHERE email IS NULL;

The IS NULL condition returns TRUE if the column contains a NULL value.

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

SELECT task_id, task_name
FROM tasks
WHERE assigned_to IS NULL;

🔍 Returns tasks that haven’t been assigned to any employee.

Use this to find records that do have values:

SELECT customer_id, email
FROM customers
WHERE email IS NOT NULL;

📧 Returns customers who have provided their email addresses.

Even expressions like this return UNKNOWN:

WHERE price > NULL  -- Returns UNKNOWN, not TRUE or FALSE

That’s why always use IS NULL or IS NOT NULL to work with NULLs properly.

Sometimes a LEFT JOIN produces NULL values for unmatched rows:

SELECT employees.name, departments.dept_name
FROM employees
LEFT JOIN departments ON employees.dept_id = departments.id
WHERE departments.id IS NULL;

🎯 This finds employees not assigned to any department.

Task: Write a query to find all orders that do not have a shipping date.

SELECT order_id, customer_id
FROM orders
WHERE shipping_date IS NULL;

The IS NULL and IS NOT NULL operators are crucial for handling real-world data, where not every field is always filled.

✅ Use IS NULL to find missing data
✅ Use IS NOT NULL to filter out blanks
✅ Avoid using = NULL or <> NULL — they won’t work

Mastering NULL logic makes your queries more accurate, robust, and production-ready.