Re: IN/NOT IN Parsing

From: Preston <dontwantany_at_nowhere.invalid>
Date: 24 Oct 2008 10:15:29 GMT
Message-ID: <>

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.

Received on Fri Oct 24 2008 - 05:15:29 CDT

Original text of this message