Subqueries vs joins(子查询与连接的比较)

我认为stackoverflow中有个回答还比较靠谱。

A：

I refactored a slow section of an application we inherited from another company to use an inner join instead of a subquery like

where id in (select id from ... )

The refactored query runs about 100x faster. (~50 seconds to ~0.3) I expected an improvement, but can anyone explain why it was so drastic? The columns used in the where clause were all indexed. Does SQL execute the query in the where clause once per row or something?

Q：

A "correlated subquery" (i.e., one in which the where condition depends on values obtained from the rows of the containing query)will execute once for each row. A non-correlated subquery (one in which the where condition is independent of the containing query)will execute once at the beginning. The SQL engine makes this distinction automatically.

But, yeah, explain-plan will give you the dirty details.