0

I am having 2 tables with data as below:

tableA
========
1
2
3
4
5

tableB
=======
1
3
5
6
8
9

With this query:

select
    coalesce(tA.id, null) as id_A,
    coalesce(tB.id, null) as id_B
from
    tableA tA
inner join tableB tB on
    tA.id = tB.id

And it currently outputs:

current result
==============
1|1
2|null
3|3
4|null
5|5

My expected result is:

expected result
===============
1|1
2|null
3|3
4|null
5|5
null|6
null|8
null|9

I wanted the 6, 8, and 9 values from tableB appearing despite tableA not having it. I have tried LEFT JOIN, RIGHT JOIN, FULL OUTER JOIN but it cannot give the results I expected. I suspect there is something on the FROM clause where I just choose tableA.

How I can get the expected result?

Thanks!

2 Answers 2

1

This is basic PostgreSQL FULL OUTER JOIN clause

SELECT  tA.id as id_A,
        tB.id as id_B
FROM tableA tA
FULL OUTER JOIN tableB tB ON tA.id = tB.id;

https://dbfiddle.uk/?rdbms=postgres_13&fiddle=508afb6b251572d45c01e6ec174da2ba

Learn more on: https://www.postgresql.org/docs/current/queries-table-expressions.html

0

FULL OUTER JOIN works just fine:

SELECT a.id AS a_id, b.id AS b_id
FROM tablea a
FULL OUTER JOIN tableb b ON (a.id = b.id);

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.