Re: ???? SQL question ????

Jørn Hansen wrote:

> SELECT *
> FROM t1
> WHERE (id1, id2, id3) NOT IN (SELECT DISTINCT Table1_id1, Table1_id2,
> Table1_id3 FROM t2);
>

This is not better because query optimizer will select all records in subquery, performa distinct operation on it and then in the resulting records it will search for (id1,id2,id3) using sequential search.

But in the query below it will execute subquery using fast indexed search provided
there is a index on t2(Table_id1, Table1_id2, Table1_id3) and not exist operation
on resulting set of records will be very fast.

> or better:
>
> SELECT *
> FROM t1
> WHERE NOT EXISTS
> (SELECT NULL FROM t2 WHERE t1.id1 = t2.Table1_id1 AND t1.id2 =
> t2.Table1_id2
> AND t1.id3 = t2.Table1_id3);
>
> /Jørn
>
> ilepper wrote:
>
> > Ok here's a nice SQL problem, who can solve this one?
> >
> > I have got 2 tables
> >
> > Table 1 :
> > prim. key : Id1, Id2, Id3
> >
> > Table 2 :
> > prim. key : Id
> > for. key : Table1_id1, Table1_id2, Table1_id3
> >
> > I want to do the following : I would like all records from table 1
 where
> > there's no record for it in table 2.
> >
> > Usualy it would look something like this :
> > select * from table 1 where id not in (select id from table 2)
> > but now table one has more attributes forming the prim. key.
> >
> > How can it be done, anyone?
> >
> > Thanks anyway for trying,
> > by the way, I am using Orcale version 7, and Delphi 3 to excute the
 SQL
> > query.
> >
> > Ido de Lepper
> > ilepper_at_fac.fbk.eur.nl
Received on Thu Jan 29 1998 - 00:00:00 CST