Database Reference
In-Depth Information
mysql>
SELECT COUNT(DISTINCT srcuser) FROM mail;
+-------------------------+
| COUNT(DISTINCT srcuser) |
+-------------------------+
| 4 |
+-------------------------+
DISTINCT
works with multiple-column output, too. The following query shows which
dates are represented in the
mail
table:
mysql>
SELECT DISTINCT YEAR(t), MONTH(t), DAYOFMONTH(t) FROM mail;
+---------+----------+---------------+
| YEAR(t) | MONTH(t) | DAYOFMONTH(t) |
+---------+----------+---------------+
| 2014 | 5 | 11 |
| 2014 | 5 | 12 |
| 2014 | 5 | 14 |
| 2014 | 5 | 15 |
| 2014 | 5 | 16 |
| 2014 | 5 | 19 |
+---------+----------+---------------+
See Also
Chapter 8
revisits
DISTINCT
and
COUNT(DISTINCT)
.
Chapter 16
discusses duplicate re‐
moval in more detail.
3.5. Working with NULL Values
Problem
You're trying to to compare column values to
NULL
, but it isn't working.
Solution
Use the proper comparison operators:
IS
NULL
,
IS
NOT
NULL
, or
<=>
.
Discussion
Conditions that involve
NULL
are special because
NULL
means “unknown value.” Con‐
sequently, comparisons such as
value
=
NULL
or
value
<>
NULL
always produce a result
of
NULL
(not true or false) because it's impossible to tell whether they are true or false.
Even
NULL
=
NULL
produces
NULL
because you can't determine whether one unknown
value is the same as another.
To look for values that are or are not
NULL
, use the
IS
NULL
or
IS
NOT
NULL
operator.
Suppose that a table named
expt
contains experimental results for subjects who are to
be given four tests each and that represents tests not yet administered using
NULL
: