I'm bit confused with the differences between co-related and nested queries. Please give me an example if anybody knows it. Thank you :)
asked Dec 21 '09 at 12:26 PM in Default
A correlated subquery is a special kind of subqery that allows reference to specific rows in the principal query.
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:
Now, we want to see the latest trade for each symbol and tradetype:
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:
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)