cross apply behaves like outer apply

Question

cross apply behaves like outer apply

atsql

Right there with Babe

Points: 740
More actions
August 26, 2014 at 5:31 pm

#145216

In my example, cross apply generates 5 rows when sum is used and 3 rows when no aggregate statement is used. Why cross apply generates 5 rows when SUM() used? I would expect such behavior when outer apply used.
IF OBJECT_ID('TempDB..#payment','U') IS NOT NULL
DROP TABLE #payment
IF OBJECT_ID('TempDB..#certificate','U') IS NOT NULL
DROP TABLE #certificate
CREATE TABLE #payment
(
paynum INT PRIMARY KEY,
payamount decimal
)
CREATE TABLE #certificate
(
gcnumber INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,
paynum INT,
gcamount decimal
)
insert #payment (paynum, payamount) values(1, 30)
insert #payment (paynum, payamount) values(2, 35)
insert #payment (paynum, payamount) values(3, 37)
insert #payment (paynum, payamount) values(4, 49)
insert #payment (paynum, payamount) values(5, 68)
insert #certificate (paynum, gcamount) values(1, 5)
insert #certificate (paynum, gcamount) values(2, 7)
insert #certificate (paynum, gcamount) values(2, 3)
select p.paynum, c.gcamount from #payment p
cross APPLY(
SELECT SUM(gcamount) gcamount
FROM #certificate c
WHERE c.paynum = p.paynum
)c
select p.paynum, c.gcamount from #payment p
cross APPLY(
SELECT gcamount
FROM #certificate c
WHERE c.paynum = p.paynum
)c

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Luis Cazares SSC Guru Points: 183706 More actions · Answer 1

It's a little confuse, but the problem is that you're returning the value of a function and not a row or group of rows. Would that make sense to you?

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

brad.mason5 Hall of Fame Points: 3299 More actions · Answer 2

Yes actually that is behavior I have seen as well when an aggregate is used in the query.

In order to eliminate the NULL values you will need to group the data. If a GROUP BY is not used or check for NULL is not in where condition and will get unexpected results.

select p.paynum, c.gcamount from #payment p

cross APPLY(

SELECT SUM(gcamount) gcamount

FROM #certificate c

WHERE c.paynum = p.paynum

GROUP BY c.paynum

)c

atsql Right there with Babe Points: 740 More actions · Answer 3

Thank you Brad for your response,

CROSS APPLY should behave like INNER JOIN. And yet with aggregates it behaves like OUTER APPLY or LEFT JOIN. I am wondering if there is Microsoft explanation for such behavior.