(un)correlated subquery^join

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.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s