How can I merge the columns from two tables into one output?

I have two tables with similar information. Let's call them items_a and items_b. They should be one, but they are coming from different sources, so they aren't. When I full-join the two table, some rows end up with data from either one or both tables. One of the columns in both tables is category_id. I would like to cross the combined table with the categories table using category_id. However, I have two category_id columns now (one from items_a and one from items_b). Is there a way to merge the two columns into one?

I hope this isn't too confusing of a question.

Specifying the columns on your query should do the trick:

select a.col1, b.col2, a.col3, b.col4, a.category_id 
from items_a a, items_b b 
where a.category_id = b.category_id

should do the trick with regards to picking the columns you want.

To get around the fact that some data is only in items_a and some data is only in items_b, you would be able to do:

select 
  coalesce(a.col1, b.col1) as col1, 
  coalesce(a.col2, b.col2) as col2,
  coalesce(a.col3, b.col3) as col3,
  a.category_id
from items_a a, items_b b
where a.category_id = b.category_id

The coalesce function will return the first non-null value, so for each row if col1 is non null, it'll use that, otherwise it'll get the value from col2, etc.

I guess that what you want to do is an UNION of both tables.

If both tables have the same columns then you can just do

SELECT category_id, col1, col2, col3
  FROM items_a
UNION 
SELECT category_id, col1, col2, col3 
  FROM items_b

Else, you might have to do something like

SELECT category_id, col1, col2, col3
  FROM items_a 
UNION 
SELECT category_id, col_1 as col1, col_2 as col2, col_3 as col3
  FROM items_b

The top answer assumes that neither result is null. In my requirement i had two tables that needed to be made 1 with no join constraint. I was just combining them.

If you want to get results regardless of 1 is null or not.

select a.col1, b.col2, a.col3, b.col4, a.category_id 
from items_a a
  full outer join items_b b 
  on 1=1
 

Should do the trick. When i was doing this practically i had to go even further and use table expressions for my two "sets" I wanted to join together and then full outer join the two sets.

SELECT col1,
  col2
FROM
  (SELECT rownum X,col_table1 FROM table1) T1
INNER JOIN
  (SELECT rownum Y, col_table2 FROM table2) T2
ON T1.X=T2.Y;

select a.categoryname, b.productname, a.categoryid from categories a LEFT OUTER JOIN products b on a.categoryid = b.categoryid ;

select COALESCE(A.CUR_ID, B.CUR_ID, 'OTHER') as CUR_ID 
from (select 'CHF'as CUR_ID) as A 
full outer join (select 'UAD' as CUR_ID) as B
on A.CUR_ID = B.CUR_ID

Output:

CUR_ID
CHF
UAD

When your are three tables or more, just add union and left outer join:

select a.col1, b.col2, a.col3, b.col4, a.category_id 
from 
(
    select category_id from a
    union
    select category_id from b
) as c
left outer join a on a.category_id = c.category_id
left outer join b on b.category_id = c.category_id

I had a similar problem with a small difference: some a.category_id are not in b and some b.category_id are not in a.
To solve this problem just adapt the excelent answer from beny23 to

select a.col1, b.col2, a.col3, b.col4, a.category_id
from items_a a
LEFT OUTER JOIN
items_b b
on a.category_id = b.category_id

Hope this helps someone.
Regards.