Correlated subquery scenario 1
SELECT c.LastName, c.FirstName,
(SELECT CompanyName FROM Company WHERE ID = c.CompanyID) AS CompanyName
FROM Customer c
Rewrite to
SELECT c.LastName, c.FirstName, cm.CompanyName
FROM Customer c LEFT OUTER JOIN Company cm
ON c.CompanyID = cm.CompanyID
Correlated subquery scenario 2
select *
from T1
where T1.a > (select max(T2.a) from T2 where T2.b = T1.b)
Rewrite to
select T1.*
from T1, (select T2.b, max(T2.a) max_a from T2 group by T2.b) S
where T1.b = S.b and T1.a > S.max_a
