Sql · Filtering Results
NULL Values
The full text of this chapter, to read or revisit. To practise, do the exercises.
▶ Do the exercisesExercise 1 — Understanding ```NULL``` Values
In SQL,
NULL
represents a missing or unknown value. It's not the same as zero, an empty string, or false.
NULL
is a special value that requires special handling.
Look at our Codémon table above. Some Codémons have
NULL
values for type, level, or power. For example:
- Florachu has a
NULL
level
- Pyrochu has a
NULL
power
- Shadowchu has a
NULL
type
What NULL Really Means
NULL means "we don't know" or "not applicable". For example, a
NULL
power might mean the Codémon's power is unknown or hasn't been determined.
Exercise 2 — Filtering for ```NULL``` Values
To filter rows where a column is
NULL
, you must use the
IS NULL
operator.
SELECT column1, column2, ...
FROM table_name
WHERE column_name IS NULL;
Let's find all Codémons with a
NULL
power:
SELECT * FROM codemon
WHERE power IS NULL;
Common Mistake
Using
= NULL
or
!= NULL
will not work as expected.
NULL
is not a value, so it cannot be compared with
=
or
!=
. Always use
IS NULL
or
IS NOT NULL
instead.
Exercise 3 — Filtering for Non-NULL Values
To find rows where a column is
NOT NULL
, use the
IS NOT NULL
operator.
SELECT column1, column2, ...
FROM table_name
WHERE column_name IS NOT NULL;
Let's find all Codémons with a non-NULL level:
SELECT * FROM codemon
WHERE level IS NOT NULL;
This query returns Codémons where we know their level (it's not unknown).
Exercise 4 — NULL Comparison Quiz
Which of the following queries will correctly find Codémons with a
NULL
type?
Options
• SELECT * FROM codemon
WHERE type IS NULL;
• SELECT * FROM codemon
WHERE type = NULL;
• SELECT * FROM codemon
WHERE type = 'NULL';
• SELECT * FROM codemon
WHERE type == NULL;
Hint : Remember that NULL requires special operators. You cannot use = or == to check for NULL values.
Exercise 5 — Complete the NULL Filter
Complete the SQL query to find all Codémons that have both a
NULL
level and a NULL
power
.
SELECT *
FROM codemon
WHERE level ____ ____ AND power ____ ____
Options: IS = = IS NOT NULL IS NOT IS NULL
Hint : To check if a column contains NULL, you must use the IS NULL operator, not = NULL.
Exercise 6 — Combining NULL and Non-NULL Conditions
You can combine
NULL
conditions with other types of conditions using
AND
and
OR
operators. This allows for more complex filtering.
For example, let's find Codémons that are either 'water' type OR have a NOT NULL level:
SELECT * FROM codemon
WHERE type = 'water' OR level IS NOT NULL;
Now, let's find Codémons that are 'plant' type AND have a NULL level:
SELECT * FROM codemon
WHERE type = 'plant' AND level IS NULL;
Order of Evaluation
Remember that AND has higher precedence than OR. If you combine them, use parentheses to ensure your conditions are evaluated in the right order.
Exercise 7 — Match NULL Queries with Results
Match each SQL query with its expected result from our Codémon table. The queries all start with
SELECT * FROM codemon
.
Items to match
• WHERE type IS NULL;
• WHERE level IS NOT NULL;
• WHERE power IS NULL;
• WHERE level IS NULL AND power IS NULL;
Definitions
• Returns only Shadowchu
• Returns Aquachu, Pyrochu, Sparkle, and Shadowchu
• Returns Pyrochu and Rockachu
• Returns only Rockachu