Correlated Subquiries

[fl...@getridofthistotalise.co.uk]

If I have two separate sub queries within an outer quiry can I
reference the same tables in these two subquiries and assume that they
will have no correlated effect on each other (ie they will be treated
totaly separately)?

Thanks in advance.

Nigel.

[BP Margolin]

Nigel,

Perhaps you can post a simplified version of the query so that we can refer
to specifics rather than abstracts. I'm sure that you know exactly what you
are referring to, but people can, and have, misused terminology, so that it
is very possible that an answer will be given that is technically correct,
but not appropriate to your situation.

However, taking a wild guess at what your query looks like, the answer, if
I'm correcting understanding your question, is Yes.

-------------------------------------------
BP Margolin
Please reply only to the newsgroups.
When posting, inclusion of SQL (CREATE TABLE ..., INSERT ..., etc.) which
can be cut and pasted into Query Analyzer is appreciated.

<fl...@getridofthisTotalise.co.uk> wrote in message
news:3d8c18e6...@news-east.newscene.com...

[--CELKO--]

>> If I have two separate sub queries within an outer query can I

reference the same tables in these two subquiries and assume that they
will have no correlated effect on each other (ie they will be treated

totally separately)? <<

If I understand what you mean -- it would be nice to have an example
-- the answer is no. SQL is pretty much like other block structured
languages. You start at the innermost query, and resolve table name
references by working outward. If the two subqueries reference an
outer table, they will both be looking at one copy of it:

SELECT x,
(SELECT MAX(y) FROM T1 WHERE T1.k = T0.k) AS y,
(SELECT MIN(z) FROM T2 WHERE T2.k = T0.k) AS z
FROM T0;

Each scalar subquery will use the same value of T0.k inside. Same
rules apply in the WHERE and FROM clauses.

Tables with the same column names have to be at the same level in the
nesting to cause an ambigous reference.

Every time a correlation name is used on a table, it acts as if a copy
of the table was made under that new name.

It is always a good idea to qualify a column name with a table
correlation name. I like to use a letter followed by a sequential
number so i know how many copies of the same table are in use.