IS NULL Operator in MySQL
Introduction
Missing data creates unique filtering challenges beyond simple comparisons.
THE IS NULL operator specifically tests for NULL values in MySQL.
It is widely used for:
- Finding missing data
- Data quality checks
- Cleaning incomplete records
What is the IS NULL Operator
IS NULL returns TRUE only for NULL values.
Regular = NULL or != NULL never works with NULLs.
Basic Syntax
sql
SELECT column_name
FROM table_name
WHERE column_name IS NULL;
Example
sql
SELECT * FROM Students
WHERE phone IS NULL;
Returns students with no phone number entered.
IS NOT NULL
Finds non-missing values.
sql
SELECT * FROM Students
WHERE phone IS NOT NULL;
NULL vs Empty String
NULL ≠ empty string in MySQL.
sql
SELECT * FROM Students
WHERE email IS NULL OR email = '';
Catches both missing and empty emails.
IS NULL with Multiple Columns
Multiple NULL checks.
sql
SELECT * FROM Students
WHERE phone IS NULL, AND email IS NULL;
IS NULL with NOT
Equivalent to IS NOT NULL.
sql
SELECT * FROM Students
WHERE NOT (phone IS NULL); -- Same as phone IS NOT NULL
IS NULL with AND Conditions
Combine with other filters.
sql
SELECT * FROM Students
WHERE marks IS NULL AND status != 'Dropped';
IS NULL with ORDER BY
Sort with NULL handling.
sql
SELECT * FROM Students
WHERE phone IS NOT NULL
ORDER BY marks DESC;
IS NULL with GROUP BY
Aggregate excluding NULLs.
sql
SELECT city, AVG(marks)
FROM Students
WHERE marks IS NOT NULL
GROUP BY city;
IS NULL with JOIN
Handle NULL foreign keys.
sql
SELECT s.name, c.course_name
FROM Students s
LEFT JOIN Courses c ON s.course_id = c.id
WHERE s.course_id IS NULL; -- Students without courses
IS NULL in Aggregate Functions
Aggregates ignore NULLs automatically.
sql
SELECT city, COUNT(*), AVG(marks)
FROM Students
GROUP BY city;
AVG skips NULL marks.
NULL Sorting Behaviour
NULLs sort first in ASC, last in DESC.
sql
SELECT * FROM Students
ORDER BY marks ASC; -- NULL marks first
IS NULL Performance Issues
Cannot use indexes on IS NULL conditions.
sql
-- Full table scan
WHERE phone IS NULL;
-- Index works
WHERE phone IS NOT NULL;
Execution Order with IS NULL
Evaluated in the WHERE clause after FROM.
FROM → WHERE (IS NULL) → GROUP BY → HAVING → SELECT → ORDER BY → LIMIT
Performance Considerations
- IS NULL prevents index usage
- IS NOT NULL can use an index
- Track NULL counts with COUNT(*) vs COUNT(column)
- Consider default values instead of NULLs
Important Notes
- = NULL or != NULL NEVER works
- NULL means "unknown/missing" (not zero/empty)
- COUNT(*) counts NULLs, COUNT(col) skips them
- All comparisons with NULL return NULL
- IS NULL / IS NOT NULL are special operators
Example Scenario
Data quality report excluding incomplete records:
sql
SELECT name, city, marks, phone
FROM Students
WHERE marks IS NOT NULL
AND city IS NOT NULL
AND city != ''
AND phone IS NOT NULL
ORDER BY marks DESC
LIMIT 100;
Common Mistakes
- Using = NULL instead of IS NULL
- Expecting != NULL to find non-NULLs
- NULL vs empty string confusion
- Performance surprise with IS NULL indexes
- Forgetting aggregates ignore NULLs
Key Points to Remember
- Use IS NULL / IS NOT NULL (never = NULL)
- NULL ≠ empty string ≠ 0
- IS NULL prevents index usage
- Aggregates automatically skip NULLs
- NULLs sort first (ASC) or last (DESC)
- COUNT(*) counts NULL rows, COUNT(col) skips them
