Re: join query question..oops

better issue a small correction ...

select

    a_alias.*
from

    tablea a_alias,
(select empnum

    from table a
 minus

    select empnum
    from table b) indexer
where
indexer.empnum = a_alias.empnum

    I normall want to do something with the return , however - so retrieve specific named or ordered columns.

sorry for the omission.

john sprague wrote:

> select
> *
> from
> tablea,
> (select empnum
> from table a
> minus
> select empnum
> from table b) indexer
> where
> indexer.empnum = tablea.empnum
>
> The 'where not exists ' statement earlier can have detrimental connotations. Try to
> avoid negation and base the seed on what is hopefully an indexed column ( empnum)
> in both source tables.
>
> If I am wrong - I hope someone out there will bat me. These positive seeds have
> generally worked better for me than negations. Oracle does minus evaluations nicely
> on index columns.
>
> jds
>
> Brian Tkatch wrote:
>
> > On Thu, 21 Jun 2001 15:37:19 GMT, vk02720 <nospam_at_newsranger.com>
> > wrote:
> >
> > >table A and table B both have a common field empnum.
> > >What is the most optimal query to get the data from table A which is not in the
> > >table B using empnum ?
> > >
> > >TIA
> >
> > I would assume
> >
> > SELECT * FROM Table_A WHERE NOT EXISTS
> > (SELECT * FROM Table_B WHERE Empnum = Table_A.Empnum);
Received on Thu Jun 21 2001 - 22:15:55 CDT