sql - How to get records missed after join on two tables -

table aa  id  name    email         traid stusyid ----------------------------------------- 1   ketan   ketan@test.com      1 2   pankaj  pankaj@test.com pl1 5 3   adil    adil@test.com   as1 1 4   lalit   lalit@test.com      7 5   adil    adil@test.com   as1 2 6   adil    adil@test.com   as1 3  table ab  id  name    email         traid stusyid ----------------------------------------- 1   pankaj  pankaj@test.com pl1 5 2   lalit   lalit@test.com      7 3   milind  milind@test.com     8 4   akhi    akhi@test.com       8 5   milind  milind@test.com     8 6   pa      pankaj@test.com pl1 8 7   adil1   adil@test.com   as1 1 8   adil2   adil@test.com   as1 2 

i want detail table below:

((select * aa join ab b on a.email = b.email , a.traid null)  union  (select * aa join ab b on a.traid = b.tarid , a.traid not null) 

after getting above results want remaining results table ab not availabe in above result set , want merge/union entire result set (i want fetch entire result exectuting 1 sql statement.)

i using oracle 11 g db.

---

edited add results:

after suggestion richard, have updated query below:

select * aa full outer join ab b  on (a.email = b.email , a.stusyid = b.studyid)  or (a.traid = b.tarid , a.stusyid = b.studyid) 

and getting correct results below:

    id  name    email         traid stusyid id_1  name_1 email_1 traid_1 stusyid_1     ---------------------------------------------------------------------------   1 ketan   ketan@test.com      1                    2   pankaj  pankaj@test.com pl1 5   1   pankaj  pankaj@test.com pl1 5 3   adil    adil@test.com   as1 1   7   adil1   adil@test.com   as1 1 4   lalit   lalit@test.com      7   2   lalit   lalit@test.com      7 5   adil    adil@test.com   as1 2   8   adil2   adil@test.com   as1 2 6   adil    adil@test.com   as1 3                                                        3   milind  milind@test.com     8                                     4   akhi    akhi@test.com       8                                     5   milind  milind@test.com     8                                     6   pa      pankaj@test.com pl1 8 

this want in result (all matching rows in 2 tables on basis on either tarid or email(if tarid null)). here getting low performance of query if 'full outer join'. query cost coming 34000. please suggest way above results query performance.

note: actual realtime query have 7-8 other tables joins required data.

sounds want full outer join:

select whatever a   full outer join b b      on (a.emailid = b.emailid)       or (a.registrationid = b.registrationid) 

which return nulls there no matching row in , when there no matching row in b.