Question on outer join.?

Question

question

dbkid asked Dec 17, '09

Question on outer join.?

The query is like this.

SELECT * FROM

( select 'A1' as a , 'B1' as b from dual union all

select 'A1' as a , 'B2' as b from dual union all

select 'A2' as a , 'B1' as b from dual

) FIRST,

( select 'A1' as a , 'B1' as b from dual union all

select 'A1' as a , 'B2' as b from dual union all

select 'A3' as a , 'B1' as b from dual

) SECOND

WHERE FIRST.A = SECOND.A(+) AND FIRST.B = SECOND.B

The result I am expecting is 3 rows from the table "First". But its returning only 2 rows. Is there any reason?

sql oracle

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Answer 1 · 2009-12-17T14:54:00Z

Tariq Rahiman answered Dec 17, '09

This query will return only 2 rows since there are only 2 matching rows. If you notice in the last select 'A3' as a , 'B1' as b from dual , if it had been A2, the query would return 3 rows.

SELECT * FROM

(select 'A1' as a , 'B1' as b from dual union all select 'A1' as a , 'B2' as b from dual union all select 'A2' as a , 'B1' as b from dual)FIRST

right outer join

( select 'A1' as a , 'B1' as b from dual union all
select 'A1' as a , 'B2' as b from dual union all
select 'A3' as a , 'B1' as b from dual
) SECOND

ON FIRST.A = SECOND.A and FIRST.B = SECOND.B

The above query gave me 3 rows and is according to the definition of right outer join

2

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

dbkid commented · Dec 17, 2009 at 02:59 PM

But I am applying a Right outer join by keeping the condition as FIRST.A = SECOND.A(+). So it should return the thrid row also right? Please correct me if I am wrong.

0 ·

dbkid commented · Dec 17, 2009 at 03:31 PM

Yes this is giving the right answer. But if you see the query which I specified above, I am using (+) symbol for outerjoin. I applied condition as FIRST.A = SECOND.A(+) AND FIRST.B = SECOND.B. So here I didn't put (+) symbol over B. So Just got confused how oracle processes it ? .

0 ·

question