Basically, a correlated subquery is one that is referencing a column from the "outer" query.

The following is NOT a correlated subquery....because the inside subquery could run on its own.

select * from myTable where DiscountID in (select discountID from DiscountMaster where Active = 'N')

A correlated subquery is one that references a column from the outside...the inside query can't run on its own because it's referencing (or correlated with) a column on the outside. I think it's fair to say that every EXISTS is going to be correlated.

select * from MyTable   outside  
    where exists   
          ( select 1 from DiscountMaster inside where inside.DiscountID = Outside.DiscountID and inside.active = 'N')

SQL Server (again, assuming you're using SQL as the back end) will make every attempt to optimize the 2nd query.
Again, a long time ago, SQL Server was sometimes known for generating an execution plan for the second, by running the correlated subquery once for each row on the outside. Obviously, that would be a performance killer. It was a long time ago (pre sql 2000, but I believe even sql 2000 was known for it). But it gave correlated subqueries a bad rap that still exists today.

Bottom line, readability is ultimately pretty important (and sanity testing and checking the execution plan). :)







>Question (for anyone): when looking at a query, how to you know that the query will be correlated - i.e. that for each row of the outer query the inner query is run? Is it triggered by the inner subquery referencing a table in the outer query specifically for a field value in the outer query? Or does VFP just "figure this out"? Would help to know what is going on and whether properly coded - apart from trying to check the resulting data sets.
>
>e.g. for the inner query:
>
>

>.... NOT EXISTS ( SELECT 1 FROM PLaintiff_Files WHERE Plaintiff_Files.Plaintiff_ID == Plaintiff.Plaintiff_ID AND EMPTY(Date_Closed) )
>

>
>Thanks,
>Albert
>
>>Like I said, it takes some time, but offers much more than IN
>>
>>One thing...if you're using SQL Server as your back end, and you're using correlated subqueries on a LARGE table, you might want to check the actual execution plan. MANY moons ago (like 20 years!), SQL Server wasn't always smart about optimizing a correlated subquery and you "could" get a really horrible plan. By SQL 2005 I believe they cleaned up most or all of that.
>>
>>(Ironically, when they implemented LINQ to SQL by around 2007....one of the worst ideas of all time...they generated some really god-awful correlated subqueries, At that time, they pretty much HAD to make sure that correlated subqueries worked efficiently.
>>
>>There are still a few DBAs out there who are superstitiously frightened by correlated subqueries, because OLD versions of SQL would generate bad plans.