2

The question has been answered here: Oracle: I need a "partial" outer join. Look at the image

Look at the table. I've done a join the column CodA is from table A, the CodB is from table B. If I have the following situation my comparison goes wrong.

I would like to see a result like in the 3rd image. Should I do it using a Full outer JOIN?

SELECT master, detail, codA, codB, DECODE(A, B, 'Equal', 'Not equal')
FROM A, B
WHERE a.master = b.master
AND a.detail = b.detail

I also need to make a full outer Join JUST for the a.CodA = b.CodB statement, but not for the a.master = b.master statement. It must use normal JOIN there.

enter image description hereenter image description here

enter image description here

Community
  • 1
  • 1
Revious
  • 7,816
  • 31
  • 98
  • 147
  • What would you want to happen if B had a row with master and detail the same and codB = 5? – Craig Feb 20 '12 at 16:09
  • In the query result (third image), how can 2 be in `CodB` if it does not exist in the table (second image)? Also, what is the fourth image? – Branko Dimitrijevic Feb 20 '12 at 20:03
  • I would like to see master, detail, null, codB = 5. I try to explain in another way. The full outer join just takes all record matching and not. I want to take JUST matching records for the a.master = b.master statement and ALL the records if they differ JUST for the codA/B column. I want to compare the difference like in that excel in the image. – Revious Feb 20 '12 at 20:10
  • Just consider the records having 1-C3LFJX as master value, table A and B are extrapoleted just from that code. – Revious Feb 20 '12 at 20:12

1 Answers1

1

A full outer join would also display the elements that exist in B and not in A

For instance, if you had a codB=5, in your last image you will also have a record with CodA=NULL and CodB=5. Another option is to left join tables A and B. The same example I've just provided would result in the same image you provided with a left join.

In conclusion if you want to ALSO display in your result elements from B that do not match with elements from A, go for a full outer join. If you want to display all elements from A and their matches (or not) with elements from B go for A left join B.

PS: Currently you are doing an inner join, that means you are matching elments in A that have a correspondant element in B

Mosty Mostacho
  • 42,742
  • 16
  • 96
  • 123
  • Yes, but I currently don't use the WHERE clause on CodA = CodB. So it acts like a small cartesian join. – Revious Feb 20 '12 at 13:44