left join outer apply
--Script #3 - OUTER APPLY and LEFT OUTER JOIN
SELECT * FROM Department D
OUTER APPLY
(
SELECT * FROM Employee E
WHERE E.DepartmentID = D.DepartmentID
) A
GO
SELECT * FROM Department D
LEFT OUTER JOIN Employee E ON D.DepartmentID = E.DepartmentID
GO