Re: Apparently anomalous behaviour with a subquery - has anyone seen this?

On Wed, 10 Jul 2002, richard.foote_at_bigpond.com wrote:

> Take a look at my previous post (just above).

Actually, to my left, or right, or ... :-)

> Are you sure the * is under the right column (I would have thought it
> should have appeared under the var column as it doesn't exist in
> either table).

Must be Outlook Express displaying it differently than Gnus. I see it under "var"

> With subqueries, if a column is defined in the subquery then great
> Oracle references that, else if it's defined in the outer query then
> great, Oracle references that, else error.

Hm... Let me test.

    SQL>drop table t1;

    Table dropped.

    SQL>create table t1 (id number, var varchar(1));

    Table created.

    SQL>select count(*) from t1 where id in (select var from t2);

      COUNT(*)

             0

Well, I'll be damned. I see what you are saying.

> Your var column is not defined anywhere hence the error. The posters
> example was define *only* in the outer query, hence a correlated
> subquery.

So, you are saying I can do a correlated subquery with that syntax? That's actually sort of cool.

-- 
Galen deForest Boyer
Sweet dreams and flying machines in pieces on the ground.