Sample Header Ad - 728x90

CROSS APPLY produces outer join

19 votes
1 answer
3531 views
In answer to https://dba.stackexchange.com/q/239788 Erik Darling posted this code to work around for the lack of COUNT(DISTINCT) OVER ():
SELECT      *
FROM        #MyTable AS mt
CROSS APPLY (   SELECT COUNT(DISTINCT mt2.Col_B) AS dc
                FROM   #MyTable AS mt2
                WHERE  mt2.Col_A = mt.Col_A
                -- GROUP BY mt2.Col_A 
            ) AS ca;
The query uses CROSS APPLY (not OUTER APPLY) so why is there an **outer** join in the execution plan instead of an **inner** join? enter image description here Also why does uncommenting the group by clause result in an inner join? enter image description here I don't think the data is important but copying from that given by kevinwhat on the other question:
create table #MyTable (
Col_A varchar(5),
Col_B int
)

insert into #MyTable values ('A',1)
insert into #MyTable values ('A',1)
insert into #MyTable values ('A',2)
insert into #MyTable values ('A',2)
insert into #MyTable values ('A',2)
insert into #MyTable values ('A',3)

insert into #MyTable values ('B',4)
insert into #MyTable values ('B',4)
insert into #MyTable values ('B',5)
Asked by Paul White (95105 rep)
Jun 5, 2019, 02:08 PM
Last activity: Jun 8, 2019, 01:24 PM