CROSS APPLY (inner join)

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

Note that it works like an inner join, results are only included when the apply returns a value.

SELECT * FROM SchemaName.LeftTable AS LT
	OUTER APPLY (
		SELECT * 
			FROM SchemaName.RightTable AS NestedRT
			WHERE NestedRT.FK_LeftTableId = LT.LeftTableId
	) AS ApplyedTable