Correlated Sub-Query

From Zanecorpwiki

A correlated sub-query is a query that applies values from an outer/parent query to the where clause within the sub-query. The following correlated sub-query select the lowest price supplier of each widget from the table:

SELECT is1.supplier, is1.item, is1.price
  FROM itemsupplier is1
  WHERE price=
    (SELECT MIN(is2.price)
       FROM itemsupplier is2
       WHERE is2.item=is1.item);

Correlated sub-queries are--for most SQL users--the most obvious approach to a problem. Sometimes, they're perfectly fine, but they tend to scale poorly. The reason is that you must execute the sub-query for each possible value from the outer query. In this case, 1,000 items means 1+1,000 queries.

You almost always want to and can replace the the correlated sub-query with some kind of join.