0

I hope it's clear. How can I achieve the result showed in the following image?

enter image description here

Revious
  • 7,816
  • 31
  • 98
  • 147
  • 1
    What you've shown as a "natural join" isn't by my understanding of the term, unless `ColA` and `ColB` actually have the same name and therefore are join columns. – Dave Costa Feb 23 '12 at 21:36
  • Yes, sorry, take "ColA" and "ColB" as an alias. – Revious Feb 24 '12 at 08:36

2 Answers2

3

Just do an outer join but also use coalesce

SELECT
   COALESCE(a.master,b.master) master,
   a.ColA,
   b.ColB
FROM
  a FULL OUTER JOIN B
  ON a.master = b.master
     AND (a.ColA = b.ColB 
          or a.ColA is Null
          or b.ColB is Null)
ORDER BY 
    COALESCE(a.master,b.master),
    COALESCE(a.cola,b.colB)

See it working here

Community
  • 1
  • 1
Conrad Frix
  • 51,984
  • 12
  • 96
  • 155
  • I think he also wants an equality on `ColA` and `ColB` to be part of the join conditions. – Dave Costa Feb 23 '12 at 21:37
  • 1
    @Dave Costa I totally missed that. Thanks – Conrad Frix Feb 23 '12 at 21:45
  • Thank you. I also discovered COALESCE operator. http://stackoverflow.com/questions/950084/oracle-differences-between-nvl-and-coalesce – Revious Feb 24 '12 at 09:08
  • There is another thing I don't understand. If I want to filter on a single value I have to put all in in a view and filter after that. I can't simply filter as follows you can see here: http://data.stackexchange.com/stackoverflow/query/62426/filtering-on-coalescent-outer-join – Revious Feb 24 '12 at 11:01
  • 1
    @Gik25 -- you've put the filter condition in the join conditions portion of the query. Since it's an outer join, the join conditions don't act as filters. You should use a separate WHERE clause for your filter conditions. – Dave Costa Feb 24 '12 at 13:00
1
SELECT 
  masters.master AS master,
  TableA.ColA AS ColA,
  TableB.ColB AS ColB,
FROM
    (
      SELECT master FROM TableA
      UNION
      SELECT master FROM TableB
    ) AS masters
    LEFT JOIN TableA ON masters.master=TableA.master
    LEFT JOIN TableB ON masters.master=TableB.master
ORDER BY
    master, ColA, ColB
Eugen Rieck
  • 64,175
  • 10
  • 70
  • 92
  • 1
    this is a good answer for MySQL (since it doesn't support full outer join) but this isn't required for Oracle. – Conrad Frix Feb 23 '12 at 21:35
  • 1
    IMHO this is a portable answer, that should (after optimization) result in a query plan very close to that of a full outer join – Eugen Rieck Feb 23 '12 at 21:40
  • 1
    The problem of course is that if join conditions get more complicated this gets nasty. This is actually the case here as (like I did) you missed the requirement that Dave Costa pointed out. Compare the output from [your query](http://data.stackexchange.com/stackoverflow/query/62381/sample-for-post-9421369) and the OP's desired results. – Conrad Frix Feb 23 '12 at 22:00