Re: No error from subselect with invalid column name

This is an example of 'outer capture'.

The query is totally valid, it just isn't the query you think it is. Remember that a correlated subquery allows you to pass in values from the outer table to the inner query.

In the absence of fully qualified column names, Oracle interprets your query as:

select tab1.col1 from tab1 where tab1.col1 in (select tab1.col1 from tab2);

not, as you seem to be expecting:

select tab1.col1 from tab1 where tab1.col1 in (select tab2.col1 from tab2);

So long as none of the rows in tab1 have a null col1, the result will be all the col1 values from tab1. Rows with a null value for col1 will be excluded.

--

Jonathan Lewis
Yet another Oracle-related web site: www.jlcomp.demon.co.uk

mcdonnem_at_my-dejanews.com wrote in message <7hua7u$tif$1_at_nnrp1.deja.com>...
>I have been able to reproduce this under 7.3.2.3, 7.3.3.4, 7.3.4.2 and
>8.0.5 (32 Bit running under HP-UX 11). Has anyone ever done this or
>seen this or can explain this.
>
>create table tab1 (col1 number);
>create table tab2 (col2 number);
>select col1 from tab1 where col1 in (select col1 from tab2);
>
>The result will be no rows returned. I would have expected a 904 error
>indicating invalid column name since col1 does not exist in tab2.
>
>
Received on Wed May 19 1999 - 08:18:33 CDT