Answer by KillerDBA ·
A correlated subquery is a special kind of subqery that allows reference to specific rows in the principal query.
select title_id from titles where exists (select * from sales where sales.title_id = titles.title_id)
That query correlates sales to a title_id. You don't get a row if there's no sales. The " = titles.title_id" is what handles the correlation.
Let's say you had some trade data:
CREATE TABLE dbo.TradeData (Symbol varchar2 (20) NOT NULL , dateTraded datetime NOT NULL , TradeType varchar2 (50) NOT NULL , Signal int NOT NULL , priceTrigger decimal(18, 5) NOT NULL)
Now, we want to see the latest trade for each symbol and tradetype:
select sd.* from TradeData sd where sd.datetraded = (select max(dateTraded) from TradeData sd1 where sd.symbol = sd1.symbol and sd.TradeType = sd1.TradeType) order by sd.symbol, sd.strategy
The subquery correlates back by rows specific to that symbol, to allow you to find a row with a specific property for eacy symbol retrieved. The max(dateTraded) is computed for each symbol and TradeType and only the dateTraded with the max date is allowed into the final result set. I usually do that kind of query this way:
select sd.* from tradedata sd inner join (select max(datetraded) as datetraded, symbol, tradetype from tradedata group by symbol, tradetype) msd on sd.symbol = msd.symbol and sd.tradetype = msd.tradetype and sd.datetraded = msd.datetraded)
This isn't necessarily better but it shows a different way of doing it. In fact, it just happens to be the first way I figured out to solve such a problem, so it's my habit now. The inline-view, msd, yields a list that can be joined back for the latest datetraded for each type and symbol.
(These are from SQL Server examples... might need editing for Oracle but the idea works the same way)