March 1, 2023 at 5:31 pm
Hey all,
Been a while since I posted here. Recently encountered an odd problem. In the below code, if I use the version with the inner query returning just the value of Amount, I get no results (as expected). However, if instead I use an aggregate (such as SUM) on Amount, I get one row returned, with the inner query returning NULL. Why does this happen?
CREATE TABLE #Parent
(
ID INT PRIMARY KEY IDENTITY
)
CREATE TABLE #Child
(
ID INT PRIMARY KEY IDENTITY,
ParentID INT,
Amount DECIMAL(18, 2),
DateDeleted DATE
)
INSERT INTO #Parent DEFAULT VALUES
INSERT INTO #Child (ParentID, Amount, DateDeleted) VALUES (1, 10, GETDATE())
SELECT *
FROM #Parent
CROSS APPLY
(
SELECT
Amount
--SUM(Amount) AS Amount
FROM #Child
WHERE#Child.ParentID = #Parent.ID
AND #Child.DateDeleted IS NULL
) c
DROP TABLE #Parent
DROP TABLE #Child
March 1, 2023 at 6:30 pm
If you just run a select on the child table, the sum returns a row with a null, but the select returns nothing. I assume the cross apply is picking up the row containing a null. Maybe this simply asks the same question without answering it, but the sum returns the null even if the child table is empty. It works for any aggregate function (min and max at least).
CREATE TABLE #Parent
(
ID INT PRIMARY KEY IDENTITY
)
CREATE TABLE #Child
(
ID INT PRIMARY KEY IDENTITY,
ParentID INT,
Amount DECIMAL(18, 2),
DateDeleted DATE
)
INSERT INTO #Parent DEFAULT VALUES
--INSERT INTO #Child (ParentID, Amount, DateDeleted) VALUES (1, 10, GETDATE())
SELECT SUM(Amount) AS Amount2
FROM #Child
WHERE ParentID =5
AND DateDeleted IS NULL
SELECT * FROM #Child
WHERE ParentID =1
AND DateDeleted IS NULL
DROP TABLE #Parent
DROP TABLE #Child
March 1, 2023 at 7:09 pm
Yeah - I probably could have simplified the question to just "why do aggregate functions return NULL instead of no rows" ... but now that I think about it, my guess is because it is implicitly grouping on nothing, and so it will always return a row with the aggregated results, even if there are no results. Just ... feels weird.
March 1, 2023 at 7:34 pm
Maybe it is for consistency among aggregates, they always return something and sometimes the answer is NULL.
Why should the MAX() behave differently because the COUNT() is also present?
SELECT COUNT(OBJECT_ID),
MAX(OBJECT_ID)
FROM SYS.Objects
WHERE 1=2
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply