Re: IN/NOT IN Parsing

Preston wrote:

> I'd never noticed until today that Oracle doesn't check if columns
> used in an in/not in clause are valid. Is this common knowledge that
> I've somehow missed out on?
>
> Strikes me as being very dangerous as you could typo a column name &
> never know about it - at least not until your data was already screwed
> up.
>
>
> create table t1 (id1 number);
>
> create table t2 (id1 number, id2 number);
>
> insert into t1 values(1);
>
> insert into t2 values(1,1);
> insert into t2 values(1,2);
>
> commit;
>
> select * from t2
> where id2 in (select id2 from t1);

Sorry, obviously should've said doesn't /always/ check.

It appears that if the column name in the sub-query is the same as a column name in the table being selected from, it doesn't check. So:

select * from t2
where id2 in (select id3 from t1);

gives the error 'Invalid Identifier' as you'd expect.

-- 
Preston