How to make a UNION (or JOIN) query between two tables where 1st has higher priority without duplica-CodePudding

I am trying to make a performant UNION (or JOIN) query between two tables:

tableA
 ---- 
| id |
 ---- 
|  1 |
|  2 |
|  7 |
 ----

tableB
 ---- 
| id |
 ---- 
|  1 |
|  2 |
|  9 |
 ----

Where it should produce the following output:

outputTable
 ---- ----------- 
| id | fromTable |
 ---- ----------- 
|  1 | TableA    |
|  2 | TableA    |
|  7 | TableA    |
|  9 | TableB    |
 ---- -----------

I want to reproduce the behavior of select tableA and then union (or join) the rows that aren't on tableA or by another words union tableA and tableB with fromTable priority on tableA. Which is the best way to accomplish this?

CodePudding user response：

you can try something like that:

select id, 'tableA'
from tableA
union all
select id, 'tableB'
from tableB
where tableB.id not in (select id from tableA)

CodePudding user response：

You can use a full join. For example:

select
  coalesce(a.id, b.id) as id, 
  case when a.id is null then 'TableB' else 'TableA' end as from_table
from a
full join b on a.id = b.id

Result:

id  from_table
--- ----------
1   TableA
2   TableA
7   TableA
9   TableB

See fiddle.