Why COUNT(*) is equal to 1 without FROM clause? [duplicate]

Question

for a quick check I used a query

select COUNT(*) LargeTable

and was surprized to see

LargeTable
-----------
1

seconds later I realized my mistake, made it

select COUNT(*) from LargeTable

and got expected result

(No column name)
-----------
1.000.000+

but now I don't understand why COUNT(*) returned 1

it happens if I do select COUNT(*) or declare @x int = COUNT(*); select @x

another case

declare @EmptyTable table ( Value int )
select COUNT(*) from @EmptyTable

returns

(No column name)
-----------
0

I did't find explanation in SQL standard (http://www.contrib.andrew.cmu.edu/~shadow/sql/sql1992.txt, online source is given here https://stackoverflow.com/a/8949764/1506454)

why COUNT(*) returns 1?

Answer 1

In SQL Server a SELECT without a FROM clause works as though it operates against a single row table.

This is not standard SQL. Other RDBMSs provide a utility DUAL table with a single row.

So this would be treated effectively the same as

SELECT COUNT(*) AS LargeTable
FROM   DUAL 

A related Connect Item discussing

SELECT 'test'
WHERE  EXISTS (SELECT *) 

is https://connect.microsoft.com/SQLServer/feedback/details/671475/select-test-where-exists-select