When working with databases, handling missing or undefined values is crucial. In SQL, NULL represents an unknown or missing value, and checking for its presence (or absence) is often necessary in queries. This is where IS NOT NULL comes into play.
Understanding NULL in SQL
Before diving into IS NOT NULL, it’s essential to understand what NULL represents. Unlike an empty string ('') or zero (0), NULL signifies that a value is unknown or missing. In SQL, any operation involving NULL usually results in NULL.
Using IS NOT NULL in SQL
The IS NOT NULL condition filters out NULL values from query results. If a column contains NULL values and you only want rows where the column has actual data, IS NOT NULL is the way to go.
The basic syntax of IS NOT NULL is:
SELECT column_name
FROM table_name
WHERE column_name IS NOT NULL;Practical Examples of IS NOT NULL
1. Filtering Out NULL Values
Imagine you have a table employees with the following data:
| id | name | |
|---|---|---|
| 1 | John Doe | john.doe@example.com |
| 2 | Jane Smith | NULL |
| 3 | Mike Johnson | mike.johnson@example.com |
To retrieve only employees who have an email address:
SELECT name, email
FROM employees
WHERE email IS NOT NULL;This will return:
| name | |
|---|---|
| John Doe | john.doe@example.com |
| Mike Johnson | mike.johnson@example.com |
2. Using IS NOT NULL with Multiple Conditions
You can combine IS NOT NULL with other conditions using AND or OR.
SELECT id, name
FROM employees
WHERE email IS NOT NULL AND name LIKE 'J%';This query fetches employees whose name starts with “J” and have a non-null email.
Common Mistakes When Using IS NOT NULL
1. Using = NULL Instead of IS NULL
One of the biggest mistakes is trying to compare NULL using = NULL. This doesn’t work because NULL isn’t a specific value but a placeholder for missing data.
Incorrect:
SELECT * FROM employees WHERE email = NULL;Correct:
SELECT * FROM employees WHERE email IS NULL;2. Ignoring That NULLs Can Affect Aggregations
Many aggregate functions ignore NULL values. For example:
SELECT AVG(salary) FROM employees;If some salary values are NULL, they are not included in the average calculation.
When to Use IS NOT NULL
You should use IS NOT NULL in queries when:
- You need to fetch only rows where a column has valid (non-null) values.
- You’re performing calculations that require known values.
- You’re filtering data to ensure complete records are used.
Conclusion
Understanding how IS NOT NULL works in SQL is essential for accurate data retrieval. Since NULL represents missing or unknown data, using IS NOT NULL helps ensure that queries return only meaningful values. Keep in mind that NULL handling affects everything from filtering to aggregate functions, so using IS NOT NULL correctly is key to writing effective SQL queries.
Other interesting article:
How IS NULL works in SQL? Best IS NULL examples