Professional SQL Server™ 2005 Programming

6.3. Correlated Subqueries

Two words for you on this section: Pay attention! This is another one of those little areas that, if you truly "get it," can really set you apart from the crowd. By "get it" I don't just mean that you understand how it works but also that you understand how important it can be.

Correlated subqueries are one of those things that make the impossible possible. What's more, they often turn several lines of code into one, and often create a corresponding increase in performance. The problem with them is that they require a substantially different style of thought than you're probably used to. Correlated subqueries are probably the single easiest concept in SQL to learn, understand, and then promptly forget because it simply goes against the grain of how you think. If you're one of the few who choose to remember it as an option, then you will be one of the few who figure out that hard to figure out problem. You'll also be someone with a far more complete toolset when it comes to squeezing every ounce of performance out of your queries.

6.3.1. How Correlated Subqueries Work

What makes correlated subqueries different from the nestedsubqueries we've been looking at is that the information travels in two directions rather than one. In a nested subquery, the inner query is onlyprocessed once, and that information is passed out for the outer query, whichwill also execute just once — essentially providing the same value or list thatyou would have provided if you ...