SELECT COUNT(*) ;

Question

I have a database, database1, with two tables (Table 1, Table2) in it.

There are 3 rows in Table1 and 2 rows in Table2. Now if I execute the following SQL query SELECT COUNT(*); on database1, then the output is "1".

Does anyone has the idea, what this "1" signifies?

The definition of the two tables is as below.

CREATE TABLE Table1
(
ID INT PRIMARY KEY,
NAME NVARCHAR(20)
)

CREATE TABLE Table2
(
ID INT PRIMARY KEY,
NAME NVARCHAR(20)
)

Solution 1

Along similar lines the following also returns a result.

SELECT 'test'
WHERE  EXISTS (SELECT *)  

The explanation for that behavior (from this Connect item) also applies to your question.

In ANSI SQL, a SELECT statement without FROM clause is not permitted - you need to specify a table source. So the statement "SELECT 'test' WHERE EXISTS(SELECT *)" should give syntax error. This is the correct behavior.

With respect to the SQL Server implementation, the FROM clause is optional and it has always worked this way. So you can do "SELECT 1" or "SELECT @v" and so on without requiring a table. In other database systems, there is a dummy table called "DUAL" with one row that is used to do such SELECT statements like "SELECT 1 FROM dual;" or "SELECT @v FROM dual;". Now, coming to the EXISTS clause - the project list doesn't matter in terms of the syntax or result of the query and SELECT * is valid in a sub-query. Couple this with the fact that we allow SELECT without FROM, you get the behavior that you see. We could fix it but there is not much value in doing it and it might break existing application code.

Solution 2

Normally all selects are of the form SELECT [columns, scalar computations on columns, grouped computations on columns, or scalar computations] FROM [table or joins of tables, etc]

Because this allows plain scalar computations we can do something like SELECT 1 + 1 FROM SomeTable and it will return a recordset with the value 2 for every row in the table SomeTable.

Now, if we didn't care about any table, but just wanted to do our scalar computed we might want to do something like SELECT 1 + 1. This isn't allowed by the standard, but it is useful and most databases allow it (Oracle doesn't unless it's changed recently, at least it used to not).

Hence such bare SELECTs are treated as if they had a from clause which specified a table with one row and no column (impossible of course, but it does the trick). Hence SELECT 1 + 1 becomes SELECT 1 + 1 FROM ImaginaryTableWithOneRow which returns a single row with a single column with the value 2.

Mostly we don't think about this, we just get used to the fact that bare SELECTs give results and don't even think about the fact that there must be some one-row thing selected to return one row.

In doing SELECT COUNT(*) you did the equivalent of SELECT COUNT(*) FROM ImaginaryTableWithOneRow which of course returns 1.

Solution 3

you wouldn't normally execute a select count(*) without specifying a table to query against. Your database server is probably giving you a count of "1" based on default system table it is querying.

Try using

select count(*) from Table1

Without a table name it makes no sense.

Comments

[Luke Girvin]

Is there any reason why it should return one rather than zero?

[Jon Hanna]

@LukeGirvin yes, bare selects act as if run against a table with one row. Take a look at my answer.