Select COUNT(*) in MSSQL without FROM

Question

Why does it output 1?

select count(*)

Why will this work?

SELECT 'penguins'
WHERE 1 = 2
HAVING 0 < 1;

Why doesn't it work? I mean, why doesn't it give out a 1 similarly to "penguins"?

select count(*)
WHERE 1 = 2
HAVING 0 < 1;

I don't understand how this works. Can someone explain?

Answer 1

It may help to look at several similar queries together.

SELECT 'a'              -- One row
SELECT 'b' WHERE 1 = 2  -- Zero rows

SELECT COUNT(*)              -- Result = 1
SELECT COUNT(*) WHERE 1 = 2  -- Result = 0

SELECT COUNT(*) HAVING 0 < 1              -- Result = 1 (passes having criteria)
SELECT COUNT(*) WHERE 1 = 2 HAVING 0 < 1  -- Result = 0 (passes having criteria)

SELECT COUNT(*) HAVING 0 > 1              -- No results (fails having criteria)
SELECT COUNT(*) WHERE 1 = 2 HAVING 0 > 1  -- No results (fails having criteria)

SELECT 'penguins' FROM (VALUES(1),(2)) A(I) -- Two rows containing fixed 'penguins' value
SELECT 'penguins' FROM (VALUES(1),(2)) A(I) HAVING 0 < 1 -- One aggregated row containing fixed 'penguins' value  (passes having criteria)
SELECT 'penguins' FROM (VALUES(1),(2)) A(I) HAVING 0 = 1 -- No results (fails having criteria)

SELECT 'penguins' WHERE 1 = 2 -- Zero rows
SELECT 'penguins' WHERE 1 = 2 HAVING 0 < 1 -- One aggregated row containing fixed value 'penguins'
SELECT 'penguins' WHERE 1 = 2 HAVING 0 = 1 -- No results (fails having criteria)

See this db<>fiddle.

A query with no FROM clause normally returns one row containing the selected values. Adding a WHERE clause may eliminate that one row.

If the select list contains COUNT(*), the select statement now becomes an aggregation. Lacking a GROUP BY clause, all rows are aggregated into one (even if there are zero rows feeding the group-by). This is properly known as a scalar aggregate. If there was one row before the aggregation, the count will be 1. If there were no rows feeding the aggregation, the count will be '0' but that count is still part of the result.

The HAVING clause then filters the aggregation results. If the condition is true, the aggregation result is retained in the final result. If the condition is false, the aggregation result is excluded.

As for the SELECT 'penguins' WHERE 1 = 2 HAVING 1 < 0 case, the presence of the HAVING clause also triggers an aggregation (even without GROUP BY), but turns it into a vector aggregate (which has slightly different semantics). The 'penguins' value then just becomes a fixed value within the aggregated results. Since the HAVING criteria passes, that aggregated row is included in the final result.

All is working as designed.