8.6 IS NULL / IS NOT NULL

Level: 8 | Version: v1.1 | Author: Meptrasoft

Overview

In SQL, NULL represents missing, unknown, or non-existent data.

Because NULL is not a regular value, it cannot be compared using normal operators like = or !=. Instead, SQL provides specialized operators:

IS NULL → Find missing or unknown values
IS NOT NULL → Find present or recorded values

Practice Table

This topic uses an employees table with optional contact information:

IS NULL

IS NULL finds rows where a specific column has no value recorded.

SELECT *
FROM employees
WHERE phone_no IS NULL;

This returns employees whose phone number is missing:

emp_id | emp_name   | phone_no | salary
2      | Emma Brown | NULL     | 60000
4      | Anderson   | NULL     | 55000

IS NULL → Filter for empty/missing entries

IS NOT NULL

IS NOT NULL filters for rows that have an actual value stored.

SELECT *
FROM employees
WHERE phone_no IS NOT NULL;

This returns employees who have a recorded phone number:

emp_id | emp_name    | phone_no   | salary
1      | John Smith  | 9876543210 | 45000
3      | Samuel Reed | 9123456780 | 32000

IS NOT NULL → Filter for present entries

Why = NULL Fails (Three-Valued Logic)

In SQL, comparing anything to NULL using = NULL yields UNKNOWN, never TRUE:

-- ✗ INCORRECT: Returns 0 rows every time!
SELECT * FROM employees WHERE phone_no = NULL;

-- ✓ CORRECT:
SELECT * FROM employees WHERE phone_no IS NULL;

SQL operates on Three-Valued Logic:

1. TRUE
2. FALSE
3. UNKNOWN

Since NULL means "unknown", the database does not know whether one unknown equals another unknown. Therefore, NULL = NULL is UNKNOWN, and rows with UNKNOWN conditions are excluded from WHERE clauses.

       = NULL   ──► ✗ FAILS (Evaluates to UNKNOWN)
      IS NULL   ──► ✓ WORKS (Correct SQL Operator)

Common Beginner Mistakes

Placement Quick Points

IS NULL
→ FINDS UNRECORDED / MISSING VALUES

IS NOT NULL
→ FINDS RECORDED / VALID VALUES

= NULL
→ ALWAYS INVALID IN SQL

• NULL represents missing, unknown, or not-applicable information.
• Never use = NULL or != NULL; always use IS NULL and IS NOT NULL.
• NULL is not zero, not blank space, and not empty string.
• In three-valued logic, comparisons with NULL evaluate to UNKNOWN.

Interview Questions

What does NULL represent in SQL?

NULL signifies missing, unrecorded, or unknown data. It is a marker indicating the absence of a value.

How do aggregate functions like SUM and AVG handle NULLs?

They silently ignore NULL values when calculating aggregate results.

Practice & Hands-On Exercises

Using the employees table:

1. Retrieve all employees who do not have a phone number:

SELECT emp_name FROM employees WHERE phone_no IS NULL;

2. Retrieve all employees who have a verified phone number:

SELECT emp_name, phone_no FROM employees WHERE phone_no IS NOT NULL;

3. Test what happens when you run SELECT * FROM employees WHERE phone_no = NULL;.
4. Explain why NULL = NULL does not evaluate to TRUE.

💡 Tip: Test your queries using the in-browser interactive runner above.

Key Takeaway

IS NULL     → FIND MISSING VALUES
IS NOT NULL → FIND NON-NULL VALUES

= NULL ✗    → NEVER USE EQUALITY WITH NULL

Use IS NULL to check for missing values and IS NOT NULL to find rows with values present.

End of IS NULL / IS NOT NULL