October 7, 2014 at 10:09 am
Following is the query that I'm running:
create table a (id int, name varchar(10));
create table b(id int, sal int);
insert into a values(1,'John'),(1,'ken'),(2,'paul');
insert into b values(1,400),(1,500);
select *
from a
cross apply( select max(sal) as sal from b where b.id = a.id)b;
Below is the result for the same:
idname sal
1John500
1ken500
2paulNULL
Now I'm not sure why the record with ID 2 is coming using CROSS APPLY, shouldn't it be avoided in case of CROSS APPLY and only displayed when using OUTER APPLY.
One thing that I noticed was that if you remove the Aggregate function MAX then the record with ID 2 is not shown in the output.
Can someone help me figure out this behaviour, I'm running this query on SQL Server 2012.
Thanks
October 7, 2014 at 10:20 am
I can't really explain what's happening here, but the lack of group by returns a "valid" NULL value. If you add the GROUP BY it will work as expected.
SELECT *
FROM a
CROSS APPLY( SELECT MAX(sal) as sal
FROM b
WHERE b.id = a.id
GROUP BY b.id)b;
October 7, 2014 at 10:28 am
Quick thought, the filter is only applied in the scope of the sub-query, effectively a cross apply without a filter which will return all rows. Consider this query instead
😎
USE tempdb;
GO
SET NOCOUNT ON;
GO
create table a (id int, name varchar(10));
create table b(id int, sal int);
insert into a values(1,'John'),(1,'ken'),(2,'paul');
insert into b values(1,400),(1,500);
select *
from a
cross apply( select max(sal) as sal, id from b group by id) b
where b.id = a.id
;
Results
id name sal id
----------- ---------- ----------- -----
1 John 500 1
1 ken 500 1
October 7, 2014 at 10:39 am
That is very easy to explain, try:
select max(sal) as sal from b where id = 99999
over
select sal as sal from b where id = 99999
See the difference?
The first query returns the row and second one doesn't.
So, behaviour of CROSS APPLY is absolutely correpsonds to the above.
You can make CROSS APPLY working for you without use of agregate function...
you can do simple as this:
select *
from a
cross apply( select TOP 1 b.sal as sal from b where b.id = a.id order by b.sal desc)b;
October 7, 2014 at 11:18 am
Quick question, why use cross apply when this can be achieved more efficiently with an inner join?
😎
SELECT
AX.id
,AX.name
,MAX(BX.sal) AS sal
FROM a AX
INNER JOIN b BX
ON AX.id = BX.id
GROUP BY AX.id, AX.name;
October 7, 2014 at 10:13 pm
Thanks Eugene, I think that explains it perfectly.
@Eirikur yes a join would have been more efficient but I was trying to understand this particular behavior and not focus on the approach taken.
Thanks anyways for all your time and effort.. 🙂
October 8, 2014 at 3:00 am
pkjsh13 (10/7/2014)
Thanks Eugene, I think that explains it perfectly.@Eirikur yes a join would have been more efficient but I was trying to understand this particular behavior and not focus on the approach taken.
Thanks anyways for all your time and effort.. 🙂
I wouldn't be so sure about JOIN being more efficient than CROSS APPLY. Try to test on big set of data, you might surprise yourself...
1. Most likely the query plan would be exactly the same and no difference in performance.
2. If the above not true, I would expect CROSS APPLY to outperform JOIN.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply