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

Joining two tables with a joint result in Postgresql

There are two tables.

table1

select tt1.*
    from 
    (values(1,'key1',5),(2,'key1',6),(3,'key1',7)
    ) as tt1(id, field2,field3)

table2

select tt2.*
from 
(values(4,'key1',null),(2,'key1',null),(3,'key1',null)) as tt2(id, field2,field3)

How can I combine these two tables in a query to get the result like this?

select tt2.*
from 
(values(1,'key1',5),(2,'key1',6),(3,'key1',7),(4,'key1',null)) as tt2(id,field2,field3)
Tags:

2

Answers


  1. 0

    I think you need this SQL query:

    SELECT id, field2, field3
FROM table1
UNION
SELECT id, field2, field3
FROM table2
WHERE id NOT IN (SELECT id FROM table1);
    Login or Signup to reply.
  2. 0

    Do a full join (or full outer join) on the id column and use coalesce to merge the columns from the two tables:

    with
tt1 (id, field2, field3) as (
    select * from (values(1, 'key1', 5), (2, 'key1', 6), (3, 'key1', 7))
),
tt2 (id, field2, field3) as (
    select * from (values(4, 'key1', null::integer), (2, 'key1', null), (3, 'key1', null))
)
select
    coalesce(tt1.id, tt2.id) as id,
    coalesce(tt1.field2, tt2.field2) as field2,
    coalesce(tt1.field3, tt2.field3) as field3
from
    tt1
full join tt2 using (id);

    Output:

     id | field2 | field3
----+--------+--------
  1 | key1   |      5
  2 | key1   |      6
  3 | key1   |      7
  4 | key1   |
(4 rows)

    Note that if there are different non-null values in tt1 and tt2, with this coalesce call, tt1 values will be chosen.

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

Back To Top
Search