Don't like ads? PRO users don't see any ads ;-)
Guest

Untitled

By: a guest on Jul 14th, 2012  |  syntax: None  |  size: 0.85 KB  |  hits: 9  |  expires: Never
download  |  raw  |  embed  |  report abuse  |  print
Text below is selected. Please press Ctrl+C to copy to your clipboard. (⌘+C on Mac)
  1. How to perform FULL OUTER JOIN in ORACLE using ' ' operator?
  2. select a.field1, b.field2
  3. from table_a a, table_b b
  4. where a.id = b.id(+)
  5. union all
  6. select a.field1, b.field2
  7. from table_a a, table b b
  8. where a.id(+) = b.id
  9.       and a.id is null
  10.        
  11. select a.field1, b.field2
  12. from table_a a full outer join table_b b
  13. on a.id = b.id
  14.        
  15. with
  16. a as
  17.    (select 'A' tbl, level id from dual connect by level < 1000),
  18. b as
  19.    (select 'B' tbl, level + 500 id from dual connect by level < 1000)
  20. select a.tbl, a.id, b.tbl, b.id from a, b where a.id = b.id(+)
  21. union all
  22. select a.tbl, a.id, b.tbl, b.id from a, b where a.id(+) = b.id and a.id is null
  23.        
  24. with
  25. a as
  26.    (select 'A' tbl, level id from dual connect by level < 1000),
  27. b as
  28.    (select 'B' tbl, level + 500 id from dual connect by level < 1000)
  29. select a.tbl, a.id, b.tbl, b.id from a full outer join b on a.id = b.id