OUTER APPLY Operator

[Costas]

SELECT T.TeamId,
    T.Name TeamName,
    ISNULL(x.MatchCount, 0) x.MatchCount
FROM Teams T
	LEFT JOIN
	(
		SELECT COUNT(*) MatchCount
		FROM Matches
		WHERE Matches.TeamId = T.TeamId
		GROUP BY Matches.TeamId
	) x  ON x.TeamId = T.TeamId

this will result

OUTER APPLY
allows us to use parent table fields (join table-evaluated functions with SQL Tables). more

SELECT T.TeamId,
    T.Name TeamName,
    ISNULL(x.MatchCount, 0) x.MatchCount
FROM Teams T
	OUTER APPLY
	(
		SELECT COUNT(*) MatchCount
		FROM Matches
		WHERE Matches.TeamId = T.TeamId
		GROUP BY Matches.TeamId
	) x

using values from joins

SELECT T.TeamId,
    T.Name TeamName,
    ISNULL(x.MatchCount, 0) x.MatchCount, 
    MyCounter.testCount
FROM Teams T
	OUTER APPLY
	(
		SELECT COUNT(*) MatchCount
		FROM Matches
		WHERE Matches.TeamId = T.TeamId
		GROUP BY Matches.TeamId
	) x
	OUTER APPLY
	(
		SELECT COUNT(*) ChampionCount
		FROM Champions
		WHERE Champions.TeamId = T.TeamId
		GROUP BY Champions.TeamId
	) y
	OUTER APPLY (
		SELECT 
		   CASE
			WHEN (ISNULL(ISNULL(x.MatchCount, 0) - ISNULL(y.ChampionCount, 0), 0)) < 0 THEN 0
			ELSE ISNULL(ISNULL(x.MatchCount, 0) - ISNULL(y.ChampionCount, 0), 0)
		   END AS testCount
	) MyCounter

CROSS APPLY is equivalent to an INNER JOIN
OUTER APPLY is equivalent to a LEFT OUTER JOIN