CODEWITHSUNDEEP
sqldatabase
throwawayeconboi
throwawayeconboi

Reputation: 9

What are "correlated" subqueries, and why is it that we should avoid them if possible?

I looked it up online and it seems to be that we should avoid them because they run slow or something?

Upvotes: 0

Views: 3838

Answers (2)

Schima
Schima

Reputation: 1

when the subquery is no longer self contained ,you need to have a connection between inner and outer query, that is why we use correlated queries which means it calculates for each line based on the data it receives from the outer query.

For example use pubs data set and try this :

select title , [type] , price ,(select avg(price) 
from dbo.titles as InnerQuery where InnerQuery.[type] = OuterQuery.[type]) as AVGPrice 
from dbo.titles as OuterQuery

as a result you will have the average price of each type of books.

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269753

A correlated subquery is a subquery where that connects to the outer query, typically via a correlation clause in the where. A generic example would be:

select a.*,
       (select count(*)
        from b
        where b.a_id = a.a_id
--------------^ correlation clause
       ) as b_count
from a;

Why should you avoid them? That's like asking "When did you stop beating your spouse?" It is the wrong question. There is no reason to avoid them.

As with all SQL constructs, sometimes correlated subqueries perform poorly. Surprisingly often, though, they are the fastest way to implement the desired logic.

Some misinformed people have generalized that they are always bad, probably because those people don't understand how SQL optimizers work.

Upvotes: 3

Related Questions