CODEWITHSUNDEEP
sqlsql-serverselectcount
IsmailBaig
IsmailBaig

Reputation: 87

SELECT COUNT(*) ;

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)
)

Upvotes: 7

Views: 15697

Answers (8)

Martin Smith
Martin Smith

Reputation: 453028

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.

Upvotes: 6

Meta Mussel
Meta Mussel

Reputation: 590

Select *

without a from clause is "Select ALL from the Universe" since you have filtered out nothing. In your case, you are asking "How many universe?" This is exactly how I would teach it. I would write on the board on the first day, Select * and ask what it means. Answer: Give me the world. And from there I would teach how to filter the universe down to something meaningful.

I must admit, I never thought of Select Count(*), which would make it more interesting but still brings back a true answer. We have only one world.
Without consulting Steven Hawking, SQL will have to contend with only 1.

The results of the query is correct.

Upvotes: -1

Noufal Panolan
Noufal Panolan

Reputation: 1367

COUNT function returns the number of rows as result. If you don't specify any table, it returns 1 by default. ie., COUNT(*), COUNT(1), COUNT(2), ... will return 1 always.

Upvotes: 0

Jon Hanna
Jon Hanna

Reputation: 113242

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.

Upvotes: 8

Buhake Sindi
Buhake Sindi

Reputation: 89169

Since this is tagged SQL server, the MSDN states.

COUNT always returns an int data type value.

Also,

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

Thus, since you didn't provide a table to do a COUNT from, the default (assumption) is that it returns a 1.

Upvotes: 0

Brad
Brad

Reputation: 15879

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.

Upvotes: 1

user359040
user359040

Reputation:

It's because you have executed select count(*) without specifying a table.

The count function returns the number of rows in the specified dataset. If you don't specify a table to select from, a single select will only ever return a single row - therefore count(*) will return 1. (In some versions of SQL, such as Oracle, you have to specify a table or similar database object; Oracle includes a dummy table (called DUAL) which can be selected from when no specific table is required.)

Upvotes: 2

Gaurav Agrawal
Gaurav Agrawal

Reputation: 4431

without table name it always return 1 whether it any database....

Upvotes: 0

Related Questions