CODEWITHSUNDEEP
postgresql
n1000
n1000

Reputation: 5314

Postgresql mulit-column join containing NULL values

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?

Upvotes: 1

Views: 60

Answers (1)

user330315
user330315

Reputation:

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!

Upvotes: 1

Related Questions