PostgreSQL INNER JOIN AND OUTER JOIN WITH SOME NULL VALUES LOGIC-CodePudding

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.

CodePudding user response：

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;