Re: Ramming two SQL queries together.....

From: joel garry <>
Date: Fri, 28 May 2010 16:58:17 -0700 (PDT)
Message-ID: <>

On May 28, 11:38 am, Charles Hooper <> wrote:
> On May 28, 1:23 pm, joel garry <> wrote:
> > <> wrote:
> > > Thankyou both gentlemen. I appreciate the feedback you have given me.
> > > I am not an Oracle expert so I was not sure what the best approach
> > > was. I will set both up and see which runs faster (I am guessing the
> > > inline due to the way correlated subqueries run).
> > > I thank you both very much.
> > > Cheers
> > > The Frog
> > I'm wondering about how you think correlated subqueries run.  See
> > If you haven't already, get Jonathan's book about the optimizer.  See
> > this example:
> > jg
> Joel,
> I wonder if it is not just a terminology problem here.  Essentially,
> what do you call it when a SELECT statement appears in a column
> position of another query.  I think that Walt and The Frog were on the
> same page regarding what they are talking about.
> I think that the Oracle definition of a correlated subquery is a
> SELECT statement that appears in the WHERE clause of another SQL
> statement, and one or more columns from the parent SQL statement is
> directly related to one or more columns (or generated values) from the

That's right.
Sql Language Reference manual:
"Using Subqueries

A subquery answers multiple-part questions. For example, to determine who works in Taylor's department, you can first use a subquery to determine the department in which Taylor works. You can then answer the original question with the parent SELECT statement. A subquery in the FROM clause of a SELECT statement is also called an inline view. you can nest any number of subqueries in an inline view. A subquery in the WHERE clause of a SELECT statement is also called a nested subquery...Oracle performs a correlated subquery when a nested subquery references a column from a table referred to a parent statement any number of levels above the subquery."

> SELECT statement in the WHERE clause.
> "The following examples show the general syntax of a correlated
> subquery:
> SELECT select_list
>     FROM table1 t_alias1
>     WHERE expr operator
>         (SELECT column_list
>             FROM table2 t_alias2
>             WHERE t_alias1.column
>                operator t_alias2.column);"
> I think that there is a proper name for the case where a subquery
> appears in a column position of another query, but I can't think of
> that name at the moment.

"Scalar subquery," perhaps? I think the O docs are weak on this, though people seem to have picked up on it when it was introduced in 9i. It's an ANSI definition.

Tom has interesting observations:

> Here is possibly interesting test case:

> ---
> I think that The Frog is on the right track to test the performance of
> both approaches.

Thanks once again for demonstrating to us all how to think.


-- is bogus.
Received on Fri May 28 2010 - 18:58:17 CDT

Original text of this message