Today we’re gonna keep digging into NULL — the invisible hero of databases. If you still think NULL is just "nothing", you’re right, but not totally. In this lecture, we’ll learn how to check if there’s a NULL in our data, and what to do about it. So, ready to hunt down missing values?
Let’s start simple — imagine you’re working with an online store database. You’ve got an orders table, and for some orders, nobody added a comment, while others have notes. If you try to find all orders with empty notes using the usual = or <> comparisons, you’ll be surprised... Why? Because NULL is a special case!
In SQL, checking for the presence or absence of NULL is done with IS NULL and IS NOT NULL. These operators help us deal with NULL and get the data we want.
Checking values with IS NULL
IS NULL is used to check if a column or expression contains a NULL value.
SELECT *
FROM orders
WHERE comment IS NULL;
This query will return all rows where the comment column is NULL. Super handy if you want to find orders with no notes.
Here’s an example of the orders table:
| id | customer_name | total_amount | comment |
|---|---|---|---|
| 1 | Otto Art | 1500 | "Urgent delivery" |
| 2 | Maria Chi | 3000 | NULL |
| 3 | Alex Lin | 2000 | '' |
| 4 | Anna Song | 5000 | NULL |
Query:
SELECT id, customer_name
FROM orders
WHERE comment IS NULL;
Result:
| id | customer_name |
|---|---|
| 2 | Maria Chi |
| 4 | Anna Song |
Notice that rows with an empty string '' aren’t included here, because '' isn’t NULL.
Checking values with IS NOT NULL
IS NOT NULL works the other way around; it checks if a value is not NULL. For example, if you want to get all orders with comments:
SELECT *
FROM orders
WHERE comment IS NOT NULL;
This query will only return rows where the comment column has data (including empty strings '').
Example
The orders table stays the same.
| id | customer_name | total_amount | comment |
|---|---|---|---|
| 1 | Otto Art | 1500 | "Urgent delivery" |
| 2 | Maria Chi | 3000 | NULL |
| 3 | Alex Lin | 2000 | '' |
| 4 | Anna Song | 5000 | NULL |
Let’s run the query:
SELECT id, customer_name, comment
FROM orders
WHERE comment IS NOT NULL;
Result:
| id | customer_name | comment |
|---|---|---|
| 1 | Otto Art | "Urgent delivery" |
| 3 | Alex Lin | '' |
Notice that the row with the empty string '' is included. SQL treats this value as "not empty".
When should you use IS NULL and IS NOT NULL?
Here are a few scenarios:
- Filtering data: you want to exclude incomplete records where values are missing.
- Error handling: sometimes
NULLcan mean a data entry error, and you need to isolate those rows. - Data analysis: counting records with missing values helps you understand data quality.
Practical use
Let’s try a couple of practical tasks:
Task 1: Select students without a birth date
Let’s say you have a students table:
| id | name | birth_date |
|---|---|---|
| 1 | Otto Art | 2000-05-10 |
| 2 | Maria Chi | NULL |
| 3 | Alex Lin | 1998-12-30 |
| 4 | Anna Song | NULL |
Query:
SELECT name
FROM students
WHERE birth_date IS NULL;
Result:
| name |
|---|
| Maria Chi |
| Anna Song |
This query is useful for finding students whose birth date needs to be clarified.
Task 2: Select orders with comments
| id | customer_name | total_amount | comment |
|---|---|---|---|
| 1 | Otto Art | 1500 | "Urgent delivery" |
| 2 | Maria Chi | 3000 | NULL |
| 3 | Alex Lin | 2000 | '' |
| 4 | Anna Song | 5000 | NULL |
For the orders table, we can find orders with filled comments:
SELECT customer_name, comment
FROM orders
WHERE comment IS NOT NULL;
Result:
| customer_name | comment |
|---|---|
| Otto Art | "Urgent delivery" |
| Alex Lin | '' |
Comparison with regular operators
Now let’s take a step aside and try a "wrong" query to check NULL:
SELECT *
FROM orders
WHERE comment = NULL;
Surprised? The query won’t return any rows, even those where comment is clearly NULL. That’s because you can’t compare NULL using standard operators. For these comparisons, you have to use IS NULL.
GO TO FULL VERSION