correlated query /subquery VS join query

Question

can we always convert a usual subquery/correlated subquery to join type query?

Answer 1

Yes, but it means that what you JOIN to can appear in the result set. A subquery in either an EXISTS or IN clause would not have this risk.

Answer 2

yes and no...

• Yes, in that JOIN/EXISTS/IN usually give the same plan and are often the same

• No, in that if the EXISTS table has > 1 row for the main table you'll need DISTINCT to avoid dupes

I'd stick with an EXISTS type query if this is relevant and you don't need data from the EXISTS table