Correlated/uncorrelated sub-queries and joins are blood brothers and often simultaneously usable to solve a given problem. A few non-obvious differences.
* subquery — clearer than joins
sub query may look unfriendly compared to joins, but actually the logic is easier to make out.
* uncorrelated — most readable
Uncorrelated is usually *simpler* than joins or correlated.
* uncorrelated — fast
b’cos one nested scan only. Correlated (not uncorrelated) can hurt performance because it could involve one scan per value “passed in”
Uncorrelated requires just one execution of the sub query.