Database Reference
In-Depth Information
mysql>
SELECT subject, test, IF(score IS NULL,'Unknown', score) AS 'score'
->
FROM expt;
+---------+------+---------+
| subject | test | score |
+---------+------+---------+
| Jane | A | 47 |
| Jane | B | 50 |
| Jane | C | Unknown |
| Jane | D | Unknown |
| Marvin | A | 52 |
| Marvin | B | 45 |
| Marvin | C | 53 |
| Marvin | D | Unknown |
+---------+------+---------+
This
IF()
-based mapping technique works for any kind of value, but it's especially useful
with
NULL
values because
NULL
tends to be given a variety of meanings: unknown, miss‐
ing, not yet determined, out of range, and so forth. Choose the label that makes the most
sense in a given context.
The preceding query can be written more concisely using
IFNULL()
, which tests its first
argument and returns it if it's not
NULL
, or returns its second argument otherwise:
SELECT
subject
,
test
,
IFNULL
(
score
,
'Unknown'
)
AS
'score'
FROM
expt
;
In other words, these two tests are equivalent:
IF(
expr1
IS NOT NULL,
expr1
,
expr2
)
IFNULL(
expr1
,
expr2
)
From a readability standpoint,
IF()
often is easier to understand than
IFNULL()
. From
a computational perspective,
IFNULL()
is more efficient because
expr1
need not be
evaluated twice, as happens with
IF()
.
See Also
NULL
values also behave specially with respect to sorting and summary operations. See
Recipes
7.11
and
8.6
.
3.6. Writing Comparisons Involving NULL in Programs
Problem
You're writing a program that looks for rows containing a specific value, but it fails when
the value is
NULL
.
