By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
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
| A | B | c |
|---|---|---|
| 1 | 1 | 0 |
| A | B | c |
|---|---|---|
| 1 | 1 | 0 |
| A | B | c |
|---|---|---|
| 1 | 1 | null |
| A | B | c |
|---|