clear markdown feedback
clear markdown feedback
By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0. 3798660 fiddles created (41906 in the last week).

DECLARE @A table (A integer NULL, B integer NULL); DECLARE @B table (A integer NULL, B integer NULL); INSERT @A (A, B) VALUES (1, 1); INSERT @B (A, B) VALUES (2, 2); -- Original APPLY SELECT * FROM @A AS A CROSS APPLY (SELECT c = COUNT_BIG(*) FROM @B AS B WHERE B.A = A.A) AS CA; -- Correctly rewritten as a JOIN SELECT A.*, c = COALESCE(J1.c, 0) FROM @A AS A LEFT JOIN ( SELECT B.A, c = COUNT_BIG(*) FROM @B AS B GROUP BY B.A ) AS J1 ON J1.A = A.A; -- Incorrect result wthout the COALESCE SELECT A.*, J1.c FROM @A AS A LEFT JOIN ( SELECT B.A, c = COUNT_BIG(*) FROM @B AS B GROUP BY B.A ) AS J1 ON J1.A = A.A; -- Incorrect result with INNER JOIN SELECT A.*, J1.c FROM @A AS A INNER JOIN ( SELECT B.A, c = COUNT_BIG(*) FROM @B AS B GROUP BY B.A ) AS J1 ON J1.A = A.A;
A B c
1 1 0
A B c
1 1 0
A B c
1 1
A B c
 hidden batch(es)