PostgreSQL INNER JOIN AND OUTER JOIN WITH SOME NULL VALUES LOGIC

Question

I have these 2 tables:

games_during_trial
+---------------------------------+
|account_id     |game             |
+---------------------------------+
|    1          | Minecraft       |
|    1          | Overwatch       |
|    2          | NULL            |
|    3          | God of War      |
|    4          | Minecraft       |
|    4          | Elden Ring      |
|    5          | Minecraft       |
+---------------------------------+

games_after_trial
+---------------------------------+
|account_id     |game             |
+---------------------------------+
|    1          | Overwatch       |
|    1          | Elden Ring      |
|    2          | Horizon         |
|    2          | Elden Ring      |
|    3          | Overwatch       |
|    3          | Minecraft       |
|    4          | Minecraft       |
|    4          | God of War      |
|    4          | Overwatch       |
|    5          | NULL            |
+---------------------------------+

Schema available here http://sqlfiddle.com/#!15/b8209c/1

CREATE TABLE games_during_trial (account_id int,game    varchar);

INSERT INTO games_during_trial (account_id,game)
VALUES ('1', 'Minecraft'), ('1', 'Overwatch'), ('2', NULL), ('3', 'God of War'), ('4', 'Minecraft'), ('4', 'Elden Ring'), ('5', 'Minecraft');

CREATE TABLE games_after_trial (account_id  int, game   varchar);

INSERT INTO games_after_trial (account_id,game)
VALUES ('1', 'Overwatch'), ('1', 'Elden Ring'), ('2', 'Horizon'), ('2', 'Elden Ring'), ('3', 'Overwatch'), ('3', 'Minecraft'), ('4', 'Minecraft'), ('4', 'God of War'), ('4', 'Overwatch'), ('5', NULL);

How can I join them in Postgresql so that when there is a full match (account_id and game) it joins as usual, however, when the game is missing from one of the tables, it still joins but puts NULL? Here is how the desired output should look like:


Desired JOIN
+---------------------------------------------------------+
|account_id     |games_during_trial  |games_after_trial   |
+---------------------------------------------------------+
|    1          | Minecraft          | NULL               |
|    1          | Overwatch          | Overwatch          |
|    1          | NULL               | Elden Ring         |
|    2          | NULL               | Horizon            |
|    2          | NULL               | Elden Ring         |
|    3          | God of War         | NULL               |
|    3          | NULL               | Overwatch          |
|    3          | NULL               | Minecraft          |
|    4          | Minecraft          | Minecraft          |
|    4          | Elden Ring         | NULL               |
|    4          | NULL               | God of War         |
|    4          | NULL               | Overwatch          |
|    5          | Minecraft          | NULL               |
+---------------------------------------------------------+

Please help.

Tim Biegeleisen · Accepted Answer · 2022-05-21T09:58:36.370

1

You want a full outer join here:

SELECT COALESCE(gd.account_id, ga.account_id) AS account_id,
       gd.game AS games_during_trial,
       ga.game AS games_after_trial
FROM games_during_trial gd
FULL OUTER JOIN games_after_trial ga
    ON gd.account_id = ga.account_id AND
       gd.game = ga.game
WHERE gd.game IS NOT NULL OR ga.game IS NOT NULL
ORDER BY 1;

edited May 21 '22 at 09:58

answered May 21 '22 at 09:36

Tim Biegeleisen

502,043
27
286
360

Hi Tim, thank you so much for help. What is the best way to remove rows when there are NULL both for games_during_trial and games_after_trial columns? Should this filter be in the JOIN clause or WHERE clause? – Andrew Peterman May 21 '22 at 09:57
1

@AndrewPeterman Check the updated query. Just assert in the `WHERE` clause that at least one game is not null. – Tim Biegeleisen May 21 '22 at 09:59
thank you so much for help. It is funny I was working on this query with my real production data within a big complex query, and nothing worked, I tried almost all JOINs. And then I reproduced it here on Stackoverflow with dummy data, and it worked. Thanks to you and your prompt answer I found a mistake in my big complex query that did not allow FULL OUTER JOIN to work as expected (some WHERE filters in other subqueries). – Andrew Peterman May 21 '22 at 10:07

PostgreSQL INNER JOIN AND OUTER JOIN WITH SOME NULL VALUES LOGIC

1 Answers1