Re: SQL: Full outer join?

From: Niall Litchfield <n-litchfield_at_audit-commission.gov.uk>
Date: Wed, 11 Jul 2001 11:50:28 +0100
Message-ID: <3b4c2f78$0$8509$ed9e5944@reading.news.pipex.net>

UNION doesn't necessarily imply full table scans. I would have thought however in my innocence that if you are simply using a where clause equivalent to

where a.pk = b.pk(+) OR a.pk(+)=b.pk then you will always visit all rows and a FTS is your most efficient access.

I may of course just be having a brain fit.

--
Niall Litchfield
Oracle DBA
Audit Commission UK
"JK Yao" <jkyf0131_at_hotmail.com> wrote in message
news:93f65984.0107102002.719e916f_at_posting.google.com...


> However, UNION is the slowest way to query, coz it execute full table

 scan.


> Please correct me if I am wrong.

> Thanks a lot.

>

> regards,

> JK

>

>

> oraskm_at_yahoo.com (Sanjay Mishra) wrote in message

 news:<eca56b4a.0107100914.778008da_at_posting.google.com>...


> > The UNION is the best thing you can do with Oracle8i to solve this

> > problem. Oracle9i has a new syntax for full outer joins, which is the

> > ANSI standard.

> >

> > select A.*, B.*

> > from A FULL OUTER JOIN B

> > ON A.pk = B.pk

> >

> > HTH,

> > Sanjay

> >

> > onlyforposting_at_yahoo.com (Vikas Agnihotri) wrote in message

 news:<77e87b58.0107021920.287b3c03_at_posting.google.com>...


> > > How can I write a SQL for the following 2-table join?

> > >

> > > Based on the PK, Table A has 0 or 1 matching values. Table B has 0 or

> > > 1 matching values.

> > >

> > > i.e. where A.pk = B.pk(+) or B.pk = A.pk (+)

> > >

> > > Is the above valid? Or do I need

> > >

> > > select... where a.pk=b.pk(+)

> > > union

> > > select .... where b.pk=a.pk(+)

> > >

> > > Any other efficient approaches?

> > >

> > > Thanks

Received on Wed Jul 11 2001 - 05:50:28 CDT