Hi,
This might be trivial but I am a bit confused on how the OR condition works in a JOIN condtion. Consider the following example
with temp as
(select '1' as ben_id , '2' as org_id, 1000 as amt, 1 as id from dual
union all
select '1' as ben_id , '1' as org_id, 2000 as amt, 2 as id from dual
),
temp1 as
(select '1' as id,'BEN1' as id_name from dual
union all
select '2' as id,'ORG1' as id_name from dual
)
select a.ben_id,a.org_id,a.amt,a.id,b.id_name from temp a inner join temp1 b on (a.ben_id = b.id or a.org_id = b.id);
The query results in the following output :My question is two fold. First why are there two rows for the first row in temp table. Does not OR specify if either matches. So should not only the BEN information be joined. Second if there is to be indeed two rows for every match ( either BEN_ID or ORG_ID) why is there only one row of record for the case where both the keys are same. Should not we get two exact same rows?
| BEN_ID | ORG_ID | AMT | ID | ID_NAME |
|---|
1 | 2 | 1000 | 1 | BEN1 |
| 1 | 2 | 1000 | 1 | ORG1 |
| 1 | 1 | 2000 | 2 | BEN1 |