In answer to https://dba.stackexchange.com/q/239788 Erik Darling posted this code to work around for the lack of
Also why does uncommenting the group by clause result in an inner join?
I don't think the data is important but copying from that given by kevinwhat on the other question:
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?


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
Last activity: Jun 8, 2019, 01:24 PM