PostgreSQL JOIN SELECT null if record on one of both table not exist

All we need is an easy explanation of the problem, so here it is.

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!

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 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

Method 2

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);

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Tags: ,

Leave a Reply