Oracle sql union with no duplicate on a single column

oracle, sql

Solution

I think you are looking for a join:

select coalesce(a.item_name, b.item_name) as item_name,
       coalesce(a.price, b.price) as price,
       (case when a.price is not null then 'A' else 'B' end) as source
from a full outer join
     b
     on a.item_name = b.item_name

Problem

In Oracle, is it possible to perform a `union` where the duplicate condition is on a single column rather than the entire row? I have table `A`and `B` that have 2 columns: `item_name, price`. I'd like to create a view that for certain `item_names`, it looks in table `A` to see if the `item_name` is present, and if so use the `price` in `A`, if not go to `B` and use the `price` in `B`, then `union` the rest of `item_name` in `B` that have not yet been added to the view. For example, ``` Table A Table B ---------------- ---------------- item_name price item_name price ---------------- ---------------- shoe 10 shoe 8 socks 2 socks 4 shirt 5 t-shirt 3 gloves 1 glasses 15 pants 7 ``` For `shoe` and `socks` I'd like to use `table A`'s prices if available, and if not use `table B`. So in the end, my view should look like this: ``` View ----------------------- item_name price source ----------------------- shoe 10 A socks 2 A t-shirt 3 B glasses 15 B pants 7 B ``` I tried ``` select * from A a where item_name in ('shoe', 'socks') union select * from B b where b.item_name not in (select item_name from A where item_name in ('shoe', 'socks')) ``` Which I don't like because the query `select * from A where item_name in ('shoe', 'socks')` is duplicated. Is there a better/more efficient way of doing this?

Original source