If I run a query such as:
SELECT COUNT(*) as num FROM table WHERE x = 'y'
Will it always return a result, even when the query doesn't match any record? Or do I need to validate and make sure a row is returned as the result?
views:
170answers:
6...unless you add GROUP BY?
gbn
2010-03-31 10:37:12
+1
A:
There will always be a row of result like this:
| COUNT(*) |
------------
| 0 |
if there are no matches.
By the way, I would prefer to count only the primary key instead of *.
thephpdeveloper
2010-03-31 10:33:09
`COUNT(*)` is not the same as `COUNT(column)` in general. Counting a column checks for distinct values, not all records. Granted, when you count your primary key you'll get all records because primary keys must be unique, but this will be no faster than `COUNT(*)` and might be slower (unless MySQL auto-optimizes back to `COUNT(*)` ).
Ty W
2010-03-31 17:27:03
+1
A:
if no record is matched the count will return 0. (so yes, count always returns a result, unless you have some syntax error)
Omry
2010-03-31 10:33:32
+7
A:
Yes, because it's an aggregate and returns zero. Unless you add GROUP BY in which case no result because there is no group...
MAX/SUM etc would return NULL unless you add GROUP BY then no rows. Only COUNT returns a number for no results
gbn
2010-03-31 10:33:59