Correlated subqueries and ANSI

[Benny86]

Hello,

can anyone tell me where it's mentioned in the standard document (part 2) that correlated subqueries are supported and where I am able to use them? I found out that ANSI mentions Feature E061-13 "Correlated subqueries" but I didn't found where it's specified, neither in the rules for subqueries nor in the rules for query expressions.

Thanks for replies!

 

[Benny86]

Ok, I found following text passages, but I'm not sure if I interpreted them right..

- "If a <column reference> CR is contained in a <table expression> TE and the qualifying scope of CR contains
TE, then CR is an outer reference to the qualifying table of CR."

- "Let TRL be the ordering of <table reference list>. No element TRi in TRL shall contain an outer reference
to an element TRj, where i ? j."

I guess the first one answers my question that there is a possibility of a correlated subquery and the second says, that such an outer reference is not allowed in the from-clause, am I right? Unfortunately it's not so easy for me to understand that special wording completely, because english isn't my mother tongue. That's why I asked ^^

 

[JarlH]

The SQL standard specification is a pain to read!

ISO/IEC 9075-2:2008(E), page 1262 (Annex F):

45 E061-13 Correlated subqueries
— Subclause 8.1, “<predicate>”: When a <correlation
name> can be used in a <table subquery>
as a correlated reference to a column in the outer
query

 

[Benny86]

Thanks JarlH and I agree with you but unfortunately I have to deal with the Standard.
I found what you stated, too. But I thought that there has to be explained in more detail, what it's meant about "correlated subqueries". For my understanding what you mentioned means that a correlated subquery is just able together with an predicate (=,>,<, IN, EXISTS etc.). But is that correct (possiblity of correlated subqueries in SELECT)? How would you interpret the two mentioned text passages in the second post?

Thanks for reply