question

Matt Whitfield avatar image
Matt Whitfield asked

When does a sub-query become a correlated sub-query?

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?

t-sqlsub-querycorrelation
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

· Write an Answer
Rob Farley avatar image
Rob Farley answered

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).

10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.