Question 115055 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – How to make a UNION (or JOIN) query between two tables where 1st has higher priority without duplicates?

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. What’s the best way to accomplish this (performance wise)?

Tags:

3

Answers


  1. 0

    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)
    Login or Signup to reply.
  2. 0

    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.

    Login or Signup to reply.
  3. 0

    You can get the ids of tableB that don’t exist in tableA with EXCEPT:

    SELECT id FROM tableB
EXCEPT
SELECT id FROM tableA

    and your final query would be:

    SELECT id, 'tableA' fromTable FROM tableA
UNION ALL
SELECT id, 'tableB'
FROM (
  SELECT id FROM tableB
  EXCEPT
  SELECT id FROM tableA
) t
ORDER BY id;

    or:

    SELECT id, 'tableB' fromTable FROM tableB
EXCEPT
SELECT id, 'tableB' FROM tableA
UNION 
SELECT id, 'tableA' FROM tableA
ORDER BY id;

    See the demo.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search