Re: Help Outer joins and union and duplicate rows..

These two queries do not produce the same output, unless one guarantees that table_a.col1 either matches a col1 in table_b or is a null value; that is: if you have a row in table a that do not match another one in table b upon col1, the results produced by the outer join and the union will differ.

Assuming this is not the case, a select distinct would make the outer join query work without duplicates.

'hope this can help you.

-- 
Francisco C P de Almeida, consultor
Tel: 55-21-5125064
piragibe_at_esquadro.com.br

CHEELYM9_at_spanky.badm.sc.edu escreveu no artigo
<33335C82.46E0_at_spanky.badm.sc.edu>...


> Hi oracle Group

> Can anybody help me to get ride of  duplicate rows.


> I am using union to get ride of my publicate rows , but I hit a very

> large table twice causing performance problems.

> I would like to outer join, but with this I get publicate rows.

> 

> exapmle

> 

> select a.col1  ,a.col2 ,a.col3 

> from table_a a, table_b b

> where a.col1 = b.col1            non-unique relation

> and a.col2 > 0

> 

> union

> 

> select c.col1  ,c.col2 ,c.col3 

> from table_a c

> where c.clo1 is null

> and c.col2 >0

> 

> I would to use

> ================

> select a.col1  ,a.col2 ,a.col3 

> from table_a a, table_b b

> where a.col1(+) = b.col1

> and b.col2 > 0

> 

> Can anybody give me hint.

> THANKS

> kboussif_at_sctcorp.com

>