Different faces of COUNT

Question

I would like to know the difference between the following 4 simple queries in terms of result and functionality:

1. SELECT COUNT(*) FROM employees;

2. SELECT COUNT(0) FROM employees;

3. SELECT COUNT(1) FROM employees;

4. SELECT COUNT(2) FROM employees;

Answer 1

No difference in terms of result, they all return the number of rows in employees.

COUNT(expression) simply means "for each row in this table, if expression evaluates to a non-null value, count this row".

But, * means count anything, while n is a constant numeric value and is therefore never null. Hence, both don't take into account the actual row data and thus return the total number of rows in a table.

Answer 2

The four examples all evaluate to the same number - there is no difference.

What might give a different answer would be:

SELECT COUNT(middle_initial) FROM employees;

If there are any entries with a NULL in the middle_initial column, then the count returned will be different from COUNT(*) because it will be just the number of non-null values in the column.

Answer 3

SELECT COUNT(x) FROM employees will give you the number of rows where x is not null.

Answer 4

Count(*) :

Specifies that all rows should be counted to return the total number of rows in a table. COUNT(*) takes no parameters and cannot be used with DISTINCT. COUNT(*) does not require an expression parameter because, by definition, it does not use information about any particular column. COUNT(*) returns the number of rows in a specified table without getting rid of duplicates. It counts each row separately. This includes rows that contain null values.

Hence, COUNT(*) returns the number of items in a group. This includes NULL values and duplicates.

To sum. Count(*) will return all rows in your query which match your where clause.

So if you go

SELECT COUNT(*) FROM EMPLOYEES

the row count will be returned the same as if you went

SELECT * FROM employees

All rows will be returned from the table.

Count 1,2,3 and 4

COUNT(*) counts the number of rows produced by the query, whereas COUNT(1) counts the number of 1 values. Note that when you include a literal such as a number or a string in a query, this literal is "appended" or attached to every row that is produced by the FROM clause. This also applies to literals in aggregate functions, such as COUNT(1). The same can be said for Count(2) , Count(3) and Count(4). It will evaluate the expression based on the number of Count(variable) values and return non-null results.

So if you go

SELECT COUNT(1) from emplyees

it will return the same row count as if you went

SELECT first_name from employees

(Where first_name is column no. 1 in the table)

However the advantage here is you can go

SELECT COUNT(Distinct 1) from employees

and then it would return the count of unique records for that column in the table.