sql - 如何在 SQL 中连接这些表

Question

我有以下表格:

   match

 id   |   rival_team
----------------------
|  1  |   chelsea fc
|  2  |   real madrid


   player

 ID   |   name    |   last_name  |
---------------------------------
|  1  |    John   |      Doe     |
|  2  |   Peter   |   Williams   |


   called_up_players  

 match_id   |   player_id  |  substitution_id  |    
---------------------------|-------------------|
|     1     |      1       |         1         |
|     1     |      2       |        NULL       |


   substitution

|     id    |   match_id   | substitute_player_id | replaced_player_id |
---------------------------|----------------------|--------------------|
|     1     |      1       |           1          |          2         |

我有如下SQL语句

SELECT called_up_players.match_id, match.rival_team, player.name, 
           player.last_name, substitution.id
FROM called_up_players, substitution, player, match
WHERE called_up_players.substitution_id = substitution.substitute_player_id
         AND player.id = called_up_players.substitution_id;

和以下输出:

match_id| rival_team |   name   | last_name | substitution_id
--------+------------+----------+-----------+-----------
      1 | chelsea fc |   John   |    Doe    |         1
      1 | real madrid|   John   |    Doe    |         1
(2 rows)

但是，我希望输出是这样的

match_id| rival_team |   name   | last_name | substitution_id
--------+------------+----------+-----=-----+----------------
      1 | chelsea fc |   John   |    Doe    |         1
      2 | real madrid|   John   |    Doe    |        NULL

显示 John Doe 被征召入名单的所有比赛。无论球员是否参与了替换，我都希望有 name 和 substitution_id 的列

我想我可以使用 JOINS 实现这一点，但我不知道如何连接表所以我可以得到类似于上面的输出。我尝试了很多语句但都出错了。

Answer 1

您在没有连接条件的情况下连接匹配，因此这成为交叉连接而不是内部连接。

这是为什么不同的连接语法被认为是最佳实践的一个例子:

FROM called_up_players  
INNER JOIN substitution ON called_up_players.substitution_id = substitution.substitute_player_id  
 INNER JOIN player ON player.id = called_up_players.substitution_id  
 INNER JOIN match ON ???