SQL Server – Why WHERE 1 = 2 Returns a Row

sql serversql server 2014sql-server-2016t-sql

Any idea, why the first of the following queries returns a row with -1, NULL, although it has a WHERE 1=2?

Only when I put the query in another subquery it works correct (and returns an empty result set).

Tested on Microsoft SQL Server 2014 and 2016

DECLARE @i INT = 1

SELECT @i i, MAX(sub.id) mid
  FROM (SELECT TOP(@i) x.id
          FROM (VALUES(1), (2), (3), (4)) x(id)
         WHERE x.id > 2 + @i
         ORDER BY x.id) sub
WHERE 1 = 2

SELECT s1.i, s1.mid
  FROM (
        SELECT @i i, MAX(sub.id) mid
          FROM (SELECT TOP(@i) x.id
                  FROM (VALUES(1), (2), (3), (4)) x(id)
                 WHERE x.id > 2 + @i
                 ORDER BY x.id) sub
       ) s1
WHERE 1 = 2

Best Answer

An aggregate query without a GROUP BY is a scalar aggregate and (in the absence of any HAVING clause) returns exactly one row.

So if you do 

SELECT COUNT(*)
FROM EmptyTable

Or 

SELECT COUNT(*)
FROM SomeOtherTable
WHERE 1 = 0

Both would return the value 0 rather than an empty result set.

If you add a GROUP BY it then becomes a vector aggregate and returns one row per group (So would return nothing in the above examples) - See Vector and scalar aggregates

Your first query just consists of a scalar aggregate with @i added to the SELECT list so you get the single row. The second query has no aggregates of any kind and nothing matches the WHERE so you get no row.

Further Reading

Fun with Aggregates - Paul White

Related Question