What is a co-related sub-query and how it is different from nested queries ?

I'm bit confused with the differences between co-related and nested queries. Please give me an example if anybody knows it. Thank you :)

more ▼

asked Dec 21, 2009 at 12:26 PM in Default

avatar image

771 73 75 79

(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

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)

more ▼

answered Dec 22, 2009 at 02:33 AM

avatar image

1.5k 9 11 14

Thank you for your answer. I'll try this in Oracle.

Dec 22, 2009 at 06:48 AM OracleApprender
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: Dec 21, 2009 at 12:26 PM

Seen: 1660 times

Last Updated: Dec 21, 2009 at 12:26 PM

Copyright 2016 Redgate Software. Privacy Policy