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 :
Search WWH ::




Custom Search