Re: IN/NOT IN Parsing

William Robertson <williamr2019_at_googlemail.com> wrote:
> On Oct 24, 10:00=A0am, "Preston" <dontwant..._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.

The universe of typos that could screw up your data is extremely large.

> >
> > 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);
>
> I don't see a bug. Where does it say you can't refer to columns from
> the main query within the subquery? In fact if you couldn't, there
> would be no correlated subqueries.

But there are several ways to "refer" to a column. In the where part of a subquery, it is obviously useful. In the select part, I don't see the utility such. However, that is not necessarily a good reason for disallowing it. If SQL was like C or Perl, I'd say such use should generate a compiler warning, but not a compiler error.

Xho

-- 
-------------------- http://NewsReader.Com/ --------------------
The costs of publication of this article were defrayed in part by the
payment of page charges. This article must therefore be hereby marked
advertisement in accordance with 18 U.S.C. Section 1734 solely to indicate
this fact.