sql union and join combine-CodePudding

I checked multiple questions but could not point to similar topic as mine. Please point to the link if this looks repetitive.

Basically I have 2 tables. I need to join the 2 tables and then use lets say store_nr from table_b if it is not null. if it is null then use id_nr form table_a. I can achieve this using union and join but it is not efficient as table is quite big. Looking for any faster solution.

SELECT a.month,
       a.id_nr
from   table_a a
where  a.id_nr not in (select distinct to_char(b.group_id) from table_b)
union
SELECT a.month,
       CASE
       WHEN b.store_nr is not null
       then b.store_nr
       ELSE to_number(a.id_nr)
       END id_nr
FROM   table_a a
       join table_b b
       on a.id_nr = to_char(b.group_id)

CodePudding user response：

I think you're really after a left outer join of your table_b to your table_a, plus a COALESCE to handle which column to display, something like:

select a.month,
       coalesce(b.store_nr, a.id_nr) id_nr
from   table_a a
       left outer join table_b b on a.id_nr = to_char(b.group_id);