postgresql内连接和外部加入，带有一些空值逻辑

Question

我有这2张表：

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            |
+---------------------------------+

http://sqlfiddle.com/#！15/b8209cc.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);

我该如何加入PostgreSQL，以便像往常一样进行完整的匹配（counch_id and game）加入，但是，当其中一张桌子中缺少游戏时，它仍然会加入，但将NULL放置为NULL ？所需的输出应该是这样的：

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               |
+---------------------------------------------------------+

请帮助。

Answer 1

您想要一个完整的外部加入：

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;