Left Join vs Outer Apply - Not grouping

• Danii_Pink

  Right there with Babe

  Points: 778

  More actions

  July 31, 2017 at 7:00 pm

  #324728

  Hi
  Can somone help me explain this.
  Why does the left join not group the rnGrp column like the outer apply?
  Thanks,
  Danii

  
CREATE TABLE #temp
(
Identifier varchar(20)NOT NULL
,START DATETIME NOT NULL
,ENDS DATETIME NOT NULL
,TYPE varchar(20) NOT NULL
,STAT varchar(20) NOT NULL
)
;

  INSERT INTO #temp
VALUES('64074558792','20160731','20160805','Act','No');
INSERT INTO #temp
VALUES('64074558792','20160801','20160805','Act','PART');
INSERT INTO #temp
VALUES('64074558792','20160809','20160809','Act','PART');
INSERT INTO #temp
VALUES('64074558792','20160810','20160810','Act','No');
INSERT INTO #temp
VALUES('64074558792','20160810','20160810','Act','No');
INSERT INTO #temp
VALUES('64074558792','20160811','20160811','Act','Yes');
INSERT INTO #temp
VALUES('64074558792','20160812','20160814','Act','No');

  -- WITH OUTER APPLY
;WITH src AS (
SELECT Identifier , START, ENDS, TYPE, STAT,
ROW_NUMBER() OVER(PARTITION BY Identifier ORDER BY START , ENDS) AS rn
FROM #temp
)
,grouped as (
Select s.* ,d.rnGrp from src s
outer apply (select top 1 rn rnGrp from src s2
where s.Identifier = s2.Identifier and s2.STAT='Yes'and s2.rn>=s.rn ) d(rnGrp))

  Select *
from grouped g2

  -- WITH LEFT JOIN
;WITH src AS (
SELECT Identifier , START, ENDS, TYPE, STAT,    
ROW_NUMBER() OVER(PARTITION BY Identifier ORDER BY START , ENDS) AS rn
FROM #temp
)
,grouped as (
Select s.* ,x.rnGrp from src s
left outer join (select top 1 rn,rn rnGrp,Identifier,STAT from src s2) x
on s.Identifier=x.Identifier and x.STAT='Yes' and x.rn>=s.rn )

  Select *
from grouped g2


• Jacob Wilkins

  One Orange Chip

  Points: 27976

  More actions

  August 1, 2017 at 8:52 am

  #1953595

  First things first: you're doing it yet again, namely posting the same question repeatedly under new topics. If you haven't received a reply in a few days, then bumping the original topic is fine, but creating a new topic altogether just splits replies, as we've discussed on some of your other posts.

  Second, as Kingston already pointed out in your other topic on https://www.sqlservercentral.com/Forums/1888105/Outer-Apply-Alternative, you can't make the LEFT JOIN and OUTER APPLY equivalent here.

  For JOINs, the inputs are independent of each other, in the sense that each input is some set of rows, and neither set of rows can be defined by a reference to the other or be made to vary based on the values from the other input.

  This is not the case with APPLY. With APPLY, the right input can reference the left input and be made to vary based on the values from the left input.

  The difference in this particular case is that with the join, you just pull back some essentially random 1 row from src with the subquery (because you do TOP without an ORDER BY, so what row you get back is undefined), and then check for rows from src that meet some criteria relative to that 1 row.

  The SAME 1 row from the subquery is used for every row on the left side, though. You have your set of rows from src (the left input), and you have some single row from the subquery (the right input), and that's that. 

  For the APPLY, for each row from the left input, some (again, essentially random) row is returned from src, but this time you are able to get a (potentially) different row from the subquery for each row from the left input, because you can make the right input reference the left input and depend on/vary with values from the left input's rows.

  Check out http://sqlmag.com/sql-server/logical-query-processing-clause-and-apply for a nice explanation of this crucial distinction between JOIN and APPLY.

  Cheers!