Postgresql mulit-column join containing NULL values

Question

I would like to JOIN two tables in Postgresql 9.5 ON two columns:

CREATE TABLE table_a (col1 INT, col2 INT, col3 TEXT);
CREATE TABLE table_b (col1 INT, col2 INT, col3 TEXT);

INSERT INTO table_a (col1, col2, col3)
  VALUES (1, 2, 'foo'), (NULL, 3, 'foo');

INSERT INTO table_b (col1, col2, col3)
  VALUES (1, 2, 'bar'), (NULL, 3, 'bar');

When I do the join

SELECT a.col3, b.col3
FROM table_a a
  LEFT JOIN table_b b
    ON a.col1 = b.col1 AND
       a.col2 = b.col2;

the row with the null value is not joined.

a.col3 | b.col3
---------------
  foo  |  bar
  foo  | <null>

However, I would like to have both columns joined, i.e. including the ones where one of the values is null. So there should be foo bar appearing twice in this example. How could I make this work?

something like ON coalesce(a.col1,-1) = coalesce(b.col1,-1) AND a.col2 = b.col2 — user330315
– user330315, Commented Feb 18, 2016 at 14:16
@a_horse_with_no_name Totally works. Nice. Care for an accepted answer? — n1000
– n1000, Commented Feb 18, 2016 at 14:54

user330315 · Accepted Answer · 2016-02-18 14:56:30Z

1

If you have values that do not occur in that column, you could use coalesce() for this:

SELECT a.col3, b.col3
FROM table_a a
  LEFT JOIN table_b b
    ON ON coalesce(a.col1,-1) = coalesce(b.col1,-1) AND a.col2 = b.col2

Note that this will only work if you do not have any (real) rows with col1 = -1 otherwise you'll get a wrong result!

answered Feb 18, 2016 at 14:56

user330315

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Postgresql mulit-column join containing NULL values

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related