Seeder question: I know how to use sub-queries, but I have seen the term 'correlated sub-query' used, and I don't know what they are for. What are they, and should I be using them?
asked Oct 26 '09 at 06:37 AM in Default
Matt Whitfield ♦♦
A correlated subquery refers to fields in the outer query, and therefore changes (potentially) for each row in the outer query.
A subquery that isn't correlated is self-contained and only needs to be run once.
Edited to provide an example:
The subquery here is non-correlated. It returns the same number every time.
The subquery here is correlated, and will return the number of products in the relevant subcategory.
One common mistake people often make with correlated subqueries is to provide that hook into the outer query (making it non-correlated). If you're do that with a WHERE EXISTS clause, you turn the WHERE EXISTS into an all-or-nothing predicate (rather than something which is evaluated separately, and thereby filtering only some rows).