Came across something I haven't seen before today. . .

• Cadavre

  SSC-Forever

  Points: 41690

  More actions

  August 31, 2017 at 9:15 am

  #402451

  Not sure I'm expecting an answer from anyone, but I came across something I've not noticed before.  Selecting an aggregate from an empty result set with no group returns NULL whereas I'd expect nothing.  In actuallity, this was hidden in a fairly large process with an error complaining about the attempt to insert a NULL in to a non-NULL column, but I can reproduce it with this simple T-SQL: -
  DECLARE @TABLE AS TABLE
  (
  [ID] TINYINT,
  [GROUPER] CHAR(1)
  );

  -- Returns NULL
SELECT MAX([ID]) AS [MAX_ID]
FROM  @TABLE;

  Whereas adding the group by, makes it return "nothing": -
  DECLARE @TABLE AS TABLE
  (
  [ID] TINYINT,
  [GROUPER] CHAR(1)
  );

  -- Returns an empty result set
SELECT MAX([ID]) AS [MAX_ID]
FROM  @TABLE
GROUP BY [GROUPER];
  I'd expect both of the above to return "nothing" as @TABLE is empty.

  ---

  Forever trying to learn
  My blog - http://www.cadavre.co.uk/
  For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
  For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  ---

• drew.allen

  SSC Guru

  Points: 76985

  More actions

  August 31, 2017 at 12:37 pm

  #1957718

  Since a table represents a set, an empty table represents the empty set { }, so the value of any aggregate is either NULL or zero.

  GROUP BY defines several subsets over which the aggregate should be measured.  The empty set does not have any (proper) subsets, so there are no sets defined by the GROUP BY definition and no sets to calculate aggregates for, so there are no results.

  Drew

  J. Drew Allen
  Business Intelligence Analyst
  Philadelphia, PA

• Avi1

  SSCrazy

  Points: 2323

  More actions

  August 31, 2017 at 12:43 pm

  #1957721

  Actually this make sense, since aggregation of at table level should always return a row to represent overall picture of table, you can have count, max, avg etc. in case of no rows in table all records will simply by NULL
  But in case we group data on a column, since there is no data (so there is no group element as well), it will not show any record, because nothing to group. Just imagine if one row has to be displayed with value in all aggregated column as NULL, what will be value in the grouper column, NULL? then logically you will think there is one row in the table where value of grouper is null, which will further mislead.