L.L L.L - 6 months ago 5
SQL Question

How to combine tables of different columns into 1 result set and make all attributes of same pk in 1 row with 1 SQL statement?

I got a problems in combining tables

I have 2 table:
t1:

id | name
----- ---------
1 | 'foo'
2 | 'bar'


t2:

id | type
------ ---------
1 | 'type1'
3 | 'type2'


I want to combine those tables into 1 result set and make all attributes of same primary key in 1 row. And with single SQL statement in Oracle. The primary key column with the same name (id in the sample) can't appear twice

The result should be:

id | name | type
----- --------- ---------
1 | 'foo' | 'type1'
2 | 'bar' | null
3 | null | 'type2'


Thanks in advance for any ideas and responses.

Update:
I tried Ani Menon's out join statement, but not 100% the expected result. The outer join gives null value if id exists in t1 but not in t2.

SELECT t1.id,t1.name,t2.type
FROM t1
FULL OUTER JOIN t2 ON t1.id=t2.id;


Returns

id | name | type
----- --------- ---------
1 | 'foo' | 'type1'
2 | 'bar' | null
null | null | 'type2'

Answer

Similar to Ani's answer, but won't give you the null id:

select coalesce(table1.id, table2.id) as id, table1.name, table2.type
  from table1 full outer join table2 on table1.id = table2.id;