db2 sql : join in both directions

bobdylan75
bobdylan75 used Ask the Experts™
on
Hi,
need to have a TYPE OF JOIN that connects to both records in the table on the left that those in the right table,
even if they do not match each other..
in example:
in the left table I have 3 records,
1
3
5

in right table I have 3 records,
1
4
6

the result join table has to have 5 elements:
1
3
4
5
6

(I dont need a UNION sql because my real table has more fields different in the two tables)
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Hi Bob,

If you simply want all of the keys in the two files:

  SELECT key FROM table1
  UNION
  SELECT key FROM table2;

Then join the data to that as a subquery.


SELECT *
FROM
(
  SELECT key FROM table1
  UNION
  SELECT key FROM table2
) Keys
LEFT JOIN table1
  ON KEYS.key = table1.key
LEFT JOIN table2
  ON KEYS.key = table2.key

That's not very efficient, but it should work for you.


Kent




Author

Commented:
Kdo,
you say there isn't a special JOIN to do this?

That depends on the DB2 version.  The FULL OUTER JOIN is what you really want.

SELECT *
FROM table1
FULL OUTER JOIN table2
  ON table1.key = table2.key


Kent

Author

Commented:
ok
thanks!
Hi Bob,

I need to brush up on my AS/400 versions.  They've made huge improvements the last few releases...


Kent

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial