PostgreSQLINNERJOIN和OUTERJOINwithsomeNULLVALUESLOGIC

我有這兩張桌子：

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/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 中加入它們，以便當有完整匹配（account_id 和游戲）時它像往常一樣加入，但是，當其中一張表缺少游戲時，它仍然加入但將 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               |
 --------------------------------------------------------- 

請幫忙。

uj5u.com熱心網友回復：

你想要一個完整的外部連接：

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;

標籤：sql PostgreSQL 加入 左连接 外连接

上一篇：Sequelize：如何找到喜歡數量等于2的所有行

下一篇：如何在SQL中按列求和？