Menu
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?
757
The CROSS APPLY operator is semantically similar to INNER JOIN. It retrieves all the records from the table where there are corresponding matching rows in the output returned by the table valued function.
The most common practical use of the CROSS APPLY is probably when you want to make a JOIN between two (or more) tables but you want that each row of Table A math one and only one row of Table B. In the following example, in more detail, each user (Table A) will match with its longest trip (Table B).
Thus, the CROSS APPLY is similar to an INNER JOIN, or, more precisely, like a CROSS JOIN with a correlated sub-query with an implicit join condition of 1=1. The OUTER APPLY operator returns all the rows from the left table expression regardless of its match with the right table expression.
There are five aggregate functions, which are: MIN, MAX, COUNT, SUM, and AVG.
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.
139
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
