Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: SQL Select statement question ...

Re: SQL Select statement question ...

From: Avi Abrami <aabrami_at_intersystemsww.com>
Date: Sun, 10 Nov 2002 07:06:27 +0200
Message-ID: <3DCDE953.11FEB3E9@intersystemsww.com>


[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

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US