Re: IN/NOT IN Parsing

From: Jaap W. van Dijk <j.w.vandijk.removethis_at_hetnet.nl>
Date: Fri, 24 Oct 2008 21:06:44 GMT
Message-ID: <4902376d.1815250@news.hetnet.nl>


On 24 Oct 2008 10:00:37 GMT, "Preston" <dontwantany_at_nowhere.invalid> 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);
>
>--
>Preston

Oracle *does* check if the columns used in an in / not in clause are valid. And if it can relate them *unambiguously* to a table column of the tables present, the check will pass. You're query is equivalent to

select * from t2
where t2.id2 in (select T2.ID2 from t1)

the value T2.ID2 is a constant in the scope of the select between parentheses. As long as t2.id2 is not null the condition is TRUE.

Regards,
Jaap. Received on Fri Oct 24 2008 - 16:06:44 CDT

Original text of this message