I have two tables SAP HANA.
I want to do a union of these two tables by selecting some common columns in the them.
Below are the things I want to satisfy while doing the union.
1) SELECT ALL product_keys from TABLE A
2) IF product_key if present in both TABLE A and TABLE B then pick product_key from only TABLE A
3) IF product_key if not present in TABLE A then pick product_key from TABLE B
I propose the below, where the first part pulls all product keys from table_a where a match is found in table_b by using an inner join. Then the union all pulls in all keys from table_b where a match is not found from table_a by using a left join and filtering for null values from table_a.
select a.product_keys
from table_a as a
inner join table_b as b
on a.product_keys = b.product_keys
union all
select b.product_keys
from table_b as b
left join table_a as a
on b.product_keys = a.product_keys
where a.product_keys is null