Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> Re: SQL Select statement question ...
[Multiple top-posted replies re-arranged]
Robbert Van der Hoorn wrote:
>
> "Martin Doherty" <martin.doherty_at_elcaro.moc> schreef in bericht
> news:SeJy9.4$x86.354_at_news.oracle.com...
>
> > Yasuki Izaki wrote:
> >
> > >I'm looking for a sample SQL statement to select KEY field from tableA
> > >where KEY field does not exist in tableB?
> > >Lets' say, there are 2 tables (tableA and tableB). Both tables has KEY
> > >column defined. I need to know the records which KEY does exist in the
> > >tableA but does not exist in the tableB. What's the way to do this?
> > >Please e-mail me your suggestions to yizaki_at_yacan.com
> > >Thank you in advance for your hellp.
> > >
> >
> > One way is with the NOT EXISTS predicate:
> >
> > select *
> > from tableA
> > where not exists (select 'x' from tableB where tableB.key = tableA.key)
> >
>
> This is another way:
>
> select distinct a_key
> from
> (select a.key a_key, b.key b_key
> from tableA a, tableB b
> where a.key = b.key(+)
> )
> where b_key is null
On my Oracle 8.1.7.4 database, running on my SUN Ultra-250 under Solaris 7, I think this is a better way...
select KEY from tableA
minus
select KEY from tableB
HTH,
Avi.
Received on Sat Nov 09 2002 - 23:06:27 CST