CODEWITHSUNDEEP
sqlsql-servert-sqlsql-server-2012
cohena
cohena

Reputation: 83

Cross Apply With Aggregates

Does a CROSS APPLY with an aggregate function return a row even there is no result in the inner expression?

I'm using Sql Server 2012.

Most of the times when I have a query that requires a derived table I usually use CROSS APPLY. I think it's better performance wise, also, I have access to the outside table so I can perform TOP n for each row that gets joined etc.

Today I came across a very interesting behavior of cross apply which in my eyes it seems like a bug.

I was trying to do an aggregation on some table but I needed the details as well. so I wrote the query without any aggregation then, I tried a cross apply to sum up where the applied tables Id is equal the id of the outer table.

As far I know, if the inner table is not returning anything then the outer table won't either (CROSS APPLY vs OUTER APPLY), and that's the case when I'm not using aggregates but when I use the COUNT function I get back results even if the inner table doesn't return anything. I tried it with simple temp tables (see code) and surprisingly I got the same result.

If I do a GROUP BY it works fine.

CREATE TABLE #SampleParent (Id INT PRIMARY KEY IDENTITY, ParentName VARCHAR(25))

CREATE TABLE #SampleChildren (Id INT PRIMARY KEY IDENTITY, ParentId INT, ChildName VARCHAR(25))

INSERT INTO #SampleParent
(   ParentName )
VALUES ('Bob')

SELECT * 
FROM #SampleParent AS sp
CROSS APPLY (SELECT sc.ChildName FROM #SampleChildren AS sc WHERE sc.ParentId = sp.Id) c
WHERE sp.Id = 1

SELECT * 
FROM #SampleParent AS sp
CROSS APPLY (SELECT COUNT(sc.ChildName) c FROM #SampleChildren AS sc WHERE sc.ParentId = sp.Id) c
WHERE sp.Id = 1

--GROUP BY
SELECT * 
FROM #SampleParent AS sp
CROSS APPLY (SELECT COUNT(sc.ChildName) c FROM #SampleChildren AS sc WHERE sc.ParentId = sp.Id GROUP BY sc.ParentId) c
WHERE sp.Id = 1

So, the question is:

Does a CROSS APPLY with an aggregate function return a row even there is no result in the inner expression?

Upvotes: 8

Views: 4095

Answers (1)

Martin Smith
Martin Smith

Reputation: 453028

CROSS APPLY will eliminate outer rows if the inner expression does not return a row.

An aggregate without a GROUP BY is a scalar aggregate (as opposed to vector aggregate) and (in the absence of a HAVING clause) always returns one row even if run against an empty table.

e.g. SELECT COUNT(*) FROM EmptyTable returns a single row with result 0 - not no rows.

So this explains the behaviour you are asking about.

Upvotes: 6

Related Questions