Skip to content
Advertisement

Terse syntax to return an empty result set from SQL server

Is there a shorter way to write the following?

-- select empty_result
select t.col
from (select 1 as col) t
where 1 = 0 -- never match

The “original” question follows. This was was modified may times, explicitly in hopes of stopping Y responses as a result of showing a specific use-case; and [rightly] claimed to be a confusing mess.

The use-case is a TSQL query that returns an empty result set in some cases while a ‘real’ result set in another. In both cases the structure is expected to be the same.

 if @foo = 'bar'
   -- select real_result
 else
   -- select empty_result

The question here is then, specifically about creating an empty result set / derived table simply.

One way to do this is as follows. Is there a syntactically ‘simpler’ method?

-- select empty_result
select t.col
from (select 1 as col) t
where 1 = 0 -- never match

An alternative in this specific MINIMAL IF..ELSE.. CASE, it could be constructed as the following SQL. It is (XY) outside the question scope even though it would function here as the resulting schema is the same in the example above. While it may be a good option elsewhere, it requires a different TSQL flow-control structure. It will also not return the correct result sets if both cases result in a different schema – making it too specific in respect to the title scope.

-- XY alternative specific to MINIMAL CASE shown above
-- select real_result or empty_result with single query
select t.col
from real_data t
where @foo = 'bar'

Advertisement

Answer

Yes, there is a ‘simpler’ / shorter / more terse syntax to returning an empty result set in SQL Server that does not require first creating a derived table.

-- select empty_result
select top 0
  1 as col

This is SQL Server specific syntax. There might be other similar forms found in other database implementations.


While not specifically about a shorter empty result set syntax, Ken White provided an approach which avoids duplicating schema if it’s identical in both cases. The IF flow-control structure is preserved, as per the original question.

if @foo = 'bar'
  -- select real_result
  select t.col
  from real_table t
else
  -- select empty_result
  select t.col
  from real_table t
  where 1 = 0
User contributions licensed under: CC BY-SA
9 People found this is helpful
Advertisement