Why is that? I've always been taught, when I've worked with Oracle and/or SQL Server gurus to use correlated sub-queries.

Here's a quote I found looking up the subject on Google:

"It may indeed be convenient to imagine a correlated subquery as being "evaluated" or "executed" once for each row of the outer query; do not, however, be misled into thinking that the database optimizer actually executes it in that fashion, because this will lead you to believe that it's not very efficient, when in fact most optimizers will process a correlated subquery very efficiently, as a join."

Hey, Perry,

To clarify what I said earlier, when the result set is going to be relatively small (and I'll also add that when the overall query isn't that complex), correlated subqueries are OK.

However, in more complicated queries involving many tables, the complexity of singling out each possible rewrite goes up. Since optimizing is part of response time, the optimizer may wind up taking more time on complicated queries where the # of possibilities increases exponentially.

Another point, if only tangential to this topic: many developers who are new to SQL will often copy/paste existing code and implement it for their situations, not realizing that the original code might apply to a very different scenario. Also, some SQL developers view correlated subqueries as difficult to read (though I don't necessarily agree). I don't want to sound like a purist, but I will say they should be used very judiciously.

Kevin