OUTER APPLY (left join)

Gets all the records from the right table and put them against each record in the left table.

Note that it works like a left join, all results in the left table are included.

SELECT * FROM SchemaName.LeftTable AS LT
	OUTER APPLY (SELECT * FROM SchemaName.RightTable AS NestedRT) AS ApplyedTable

This is typically used to get aggregate info for related tables to a certain record

SELECT ApplyedTable.RtCount, * FROM SchemaName.LeftTable AS LT
	OUTER APPLY (SELECT COUNT(*) AS RtCount 
								 FROM RT
								 WHERE LT.SomeID = RT.SomeID) AS ApplyedTable

Multiple Outer Apply

SELECT Rt1.RtCount, Rt2.RtCount, * FROM SchemaName.LeftTable AS LT
	OUTER APPLY (SELECT COUNT(*) AS RtCount 
								 FROM RT
								 WHERE LT.SomeID = RT.SomeID) AS Tb1
	OUTER APPLY (SELECT COUNT(*) AS RtCount 
								 FROM RT
								 WHERE LT.SomeID = RT.SomeID) AS Tb2