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?
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.
SELECT *, (SELECT COUNT(*) FROM Production.Product AS p) AS NumProducts FROM Production.ProductSubcategory AS s;
The subquery here is correlated, and will return the number of products in the relevant subcategory.
SELECT *, (SELECT COUNT(*) FROM Production.Product AS p WHERE p.ProductSubcategoryID = s.ProductSubcategoryID) AS NumProducts FROM Production.ProductSubcategory AS s;
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).
No one has followed this question yet.