Getting warning: Null value is eliminated by an aggregate or other SET operation

by

Mostly you should do nothing about it.

  • It is possible to disable the warning by setting ansi_warnings off but this has other effects, e.g. on how division by zero is handled and can cause failures when your queries use features like indexed views, computed columns or XML methods.
  • In some limited cases you can rewrite the aggregate to avoid it. e.g. COUNT(nullable_column) can be rewritten as SUM(CASE WHEN nullable_column IS NULL THEN 0 ELSE 1 END) but this isn’t always possible to do straightforwardly without changing the semantics.

It’s just an informational message required in the SQL standard. Apart from adding unwanted noise to the messages stream it has no ill effects (other than meaning that SQL Server can’t just bypass reading NULL rows, which can have an overhead but disabling the warning doesn’t give better execution plans in this respect)

The reason for returning this message is that throughout most operations in SQL nulls propagate.

SELECT NULL + 3 + 7 returns NULL (regarding NULL as an unknown quantity this makes sense as ? + 3 + 7 is also unknown)

but

SELECT SUM(N)
FROM   (VALUES (NULL),
               (3),
               (7)) V(N)

Returns 10 and the warning that nulls were ignored.

However these are exactly the semantics you want for typical aggregation queries. Otherwise the presence of a single NULL would mean aggregations on that column over all rows would always end up yielding NULL which is not very useful.

Which is the heaviest cake below? (Image Source, Creative Commons image altered (cropped and annotated) by me)

enter image description here

After the third cake was weighed the scales broke and so no information is available about the fourth but it was still possible to measure the circumference.

+--------+--------+---------------+
| CakeId | Weight | Circumference |
+--------+--------+---------------+
|      1 | 50     | 12.0          |
|      2 | 80     | 14.2          |
|      3 | 70     | 13.7          |
|      4 | NULL   | 13.4          |
+--------+--------+---------------+

The query

SELECT MAX(Weight)        AS MaxWeight,
       AVG(Circumference) AS AvgCircumference
FROM   Cakes

Returns

+-----------+------------------+
| MaxWeight | AvgCircumference |
+-----------+------------------+
|        80 |          13.325  |
+-----------+------------------+

even though technically it is not possible to say with certainty that 80 was the weight of the heaviest cake (as the unknown number may be larger) the results above are generally more useful than simply returning unknown.

+-----------+------------------+
| MaxWeight | AvgCircumference |
+-----------+------------------+
|         ? |          13.325  |
+-----------+------------------+

So likely you want NULLs to be ignored, and the warning just alerts you to the fact that this is happening.

More Related Contents:

  1. How to display data in JOIN, and return null value? [closed]
  2. How to create a SQL Server function to “join” multiple rows from a subquery into a single delimited field? [duplicate]
  3. Query to get only numbers from a string
  4. How can I query a value in SQL Server XML column
  5. SQL Server indexes – ascending or descending, what difference does it make?
  6. Identity column value suddenly jumps to 1001 in sql server [duplicate]
  7. Dynamic Pivot Columns in SQL Server
  8. Write a number with two decimal places SQL Server
  9. Selecting N rows in SQL Server
  10. A SQL Query to select a string between two known strings
  11. How to insert multiple records and get the identity value?
  12. How can I INSERT data into two tables simultaneously in SQL Server?
  13. SQL “select where not in subquery” returns no results
  14. How can a LEFT OUTER JOIN return more records than exist in the left table?
  15. Group DateTime into 5,15,30 and 60 minute intervals
  16. Column name or number of supplied values does not match table definition
  17. SQL Group BY, Top N Items for each Group
  18. SQL Query – Concatenating Results into One String [duplicate]
  19. Return rows in the exact order they were inserted
  20. The ALTER TABLE statement conflicted with the FOREIGN KEY constraint
  21. Select SQL Server database size
  22. Check if table exists and if it doesn’t exist, create it in SQL Server 2008
  23. Cannot Resolve Collation Conflict
  24. How to set a default row for a query that returns no rows?
  25. MAX vs Top 1 – which is better?
  26. How to detect if a string contains special characters?
  27. How to get Previous Value for Null Values
  28. CONCAT_WS() for SQL Server
  29. Display next event date
  30. SQL add filter only if a variable is not null

Leave a Comment