Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Does COUNT(*) always return a result?

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?

like image 557
Ali Avatar asked Mar 31 '10 10:03

Ali


People also ask

What does the COUNT (*) command return?

The COUNT(*) function returns a number of rows in a specified table or view that includes the number of duplicates and NULL values.

Will COUNT (*) ever return NULL?

If you specify the asterisk (*), then this function returns all rows, including duplicates and nulls. COUNT never returns null.

What does COUNT (*) do in SQL?

COUNT(*) returns the number of rows in a specified table, and it preserves duplicate rows. It counts each row separately. This includes rows that contain null values.

What does select COUNT (*) mean?

count(*) means it will count all records i.e each and every cell BUT. count(1) means it will add one pseudo column with value 1 and returns count of all records.


1 Answers

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

like image 79
gbn Avatar answered Sep 21 '22 18:09

gbn