Re: IN/NOT IN Parsing
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