37

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?

gbn
  • 422,506
  • 82
  • 585
  • 676
Ali
  • 261,656
  • 265
  • 575
  • 769
  • @col yea :) ' , ' – Ali Mar 31 '10 at 10:34
  • 1
    @col by not trying and just asing it here, I guess he wants viewers to "Click Upvote" to his questions. – raj Mar 31 '10 at 10:37
  • 4
    @raj i'd have to actually create a test table and construct a query that would return 0 to test it, plus there could be differences between mysql versions that i don't know. – Ali Mar 31 '10 at 10:44

7 Answers7

54

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

Edit, a bit late: SUM would return NULL like MAX

Edit, May 2013: this applies to all main RDBMS. I guess as per the ANSI standard

gbn
  • 422,506
  • 82
  • 585
  • 676
  • What about functions like SUM(), would they return 0 or NULL? – Ali Mar 31 '10 at 10:36
  • 2
    @Click Upvote: sorry, I didn't answer. SUM gives NULL like MAX. Only COUNT returns 0 – gbn Jan 18 '11 at 06:04
  • What is the type of `COUNT(*)`? Also see [Numeric Types](https://dev.mysql.com/doc/refman/5.7/en/numeric-type-overview.html) in the MySQL manual. – jww Mar 02 '19 at 18:30
10

Yes, the return value of the "count" function itself is always a non-null integer, without exception.

Having said that, you may be more interested in the return value of your query, which is actually a result set. If that's the case, then you simply have to consider whether your query includes a "group by" clause.

A non-grouped query with aggregate functions like

select count(*), sum(*), max(*), min(*) from table

produces a result set with a single record containing the return value(s) of the aggregate function(s). In this case, your result set will always have exactly one record, with columns containing the return values of whatever aggregate functions you included. Within this lone record, the return value of count will always be a non-null integer, while the return values of the other aggregate functions such as min, max, and sum may be null. In the above example, your returned record may be "zero, null, null, null", but will never be "null,null,null,null", since count never returns null. So if you're calling only "count", then that one column in that one record returned will always contain the non-null return value of count; hence you can rest assured that the scalar return value of your count query will always be a non-null integer.

On the other hand, a grouped query with aggregate functions like

select count(*), sum(*), max(*), min(*) from table group by column

produces a result set with zero or more records where each record contains the return value of the aggregate function(s) for each group identified. In this case, if there are no groups identified, there are no groups for the aggregate functions to run on, the functions are never run, and the "query return value" will be an empty set with no records at all.

Test it out on a table with no records to see the results:

create table #t (c int);
select COUNT(c), MIN(c), MAX(c), SUM(c) from #t; --returns one record {0, null, null, null}
select COUNT(c), MIN(c), MAX(c), SUM(c) from #t group by c; --returns no records
drop table #t;

So whether you're calling count, min, max, or sum, it is important to understand not only the return values of individual aggregate functions, but also the "return value" of the query itself, and what that result set contains.

Triynko
  • 18,766
  • 21
  • 107
  • 173
5

Aggregate function Count() always returns value

Axarydax
  • 16,353
  • 21
  • 92
  • 151
  • 2
    ...to clarify: The "return value of the 'count' function" is ALWAYS a non-null integer, without exception. By mentioning "group by", you're referencing the containing query and changing the subject of "return value" from "count function" to "query's result set". A non-grouped count query produces a result set of a single record containing the return value of count. Alternatively, a grouped count query produces a result set where each record contains a count value. In that case, if there are no groups for count to run on, count is never run and the "query return value" is an empty set. – Triynko Dec 08 '11 at 20:41
2

Yes, it'll return 0 in such cases.

reko_t
  • 55,302
  • 10
  • 87
  • 77
2

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 *.

mauris
  • 42,982
  • 15
  • 99
  • 131
  • 2
    `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 Mar 31 '10 at 17:27
  • 3
    @Ty W - "Counting a column checks for distinct values". No this is not true unless you specify the `distinct` keyword. It counts values that are not `NULL` – Martin Smith Jan 18 '11 at 13:40
2

if no record is matched the count will return 0. (so yes, count always returns a result, unless you have some syntax error)

Omry Yadan
  • 31,280
  • 18
  • 64
  • 87
1

Yes it will return a numeric always

Midhat
  • 17,454
  • 22
  • 87
  • 114