JOIN ON子句中OR的工作原理及球队表关联结果异常咨询

我有两张表：

• games表：包含winner_id、opponent_id等字段
• teams表：包含team_id、name字段

其中winner_id和opponent_id都关联teams表的team_id，我想要查询每场比赛的两队名称，原本写了如下SQL：

SELECT * 
FROM teams 
LEFT JOIN games ON teams.team_id = games.winner_id 
                OR teams.team_id = games.opponent_id

我以为OR会在左侧条件成立时忽略右侧，只返回获胜球队的信息，但实际结果是每场比赛对应两行数据，每行只显示一个球队的信息，而不是一行包含两队名称。

实际查询结果 #

teams表结构 #

Table "public.teams"
 Column  |  Type   | Collation | Nullable |                Default                 
---------+---------+-----------+----------+----------------------------------------
 team_id | integer |           | not null | nextval('teams_team_id_seq'::regclass)
 name    | text    |           | not null | 
Indexes:
    "teams_pkey" PRIMARY KEY, btree (team_id)
    "teams_name_key" UNIQUE CONSTRAINT, btree (name)
Referenced by:
    TABLE "games" CONSTRAINT "games_opponent_id_fkey" FOREIGN KEY (opponent_id) REFERENCES teams(team_id)
    TABLE "games" CONSTRAINT "games_winner_id_fkey" FOREIGN KEY (winner_id) REFERENCES teams(team_id)

games表结构 #

Table "public.games"
     Column     |         Type          | Collation | Nullable |                Default                 
----------------+-----------------------+-----------+----------+----------------------------------------
 game_id        | integer               |           | not null | nextval('games_game_id_seq'::regclass)
 year           | integer               |           | not null | 
 round          | character varying(20) |           | not null | 
 winner_id      | integer               |           | not null | 
 opponent_id    | integer               |           | not null | 
 winner_goals   | integer               |           | not null | 
 opponent_goals | integer               |           | not null | 
Indexes:
    "games_pkey" PRIMARY KEY, btree (game_id)
Foreign-key constraints:
    "games_opponent_id_fkey" FOREIGN KEY (opponent_id) REFERENCES teams(team_id)
    "games_winner_id_fkey" FOREIGN KEY (winner_id) REFERENCES teams(team_id)

解决方案 #

你需要对teams表进行两次内连接（若需包含无关联的球队可改用左连接），分别关联games表的winner_id和opponent_id，就能在一行中同时获取两队的名称。示例SQL如下：

SELECT
    g.game_id,
    g.year,
    g.round,
    t_winner.name AS winner_name,
    t_opponent.name AS opponent_name,
    g.winner_goals,
    g.opponent_goals
FROM games g
JOIN teams t_winner ON g.winner_id = t_winner.team_id
JOIN teams t_opponent ON g.opponent_id = t_opponent.team_id;

结果说明 #

这条SQL以games表为主表，分别关联获胜球队和对手球队的信息，最终每条结果行对应一场比赛，包含两队名称、比分等完整信息，符合需求。

原SQL失效原因 #

原SQL中OR的作用是：只要球队的team_id是比赛的winner_id或者opponent_id，就会生成一条关联记录。所以每场比赛会匹配到两个球队，自然产生两行数据，而非合并成一行。OR在这里是筛选满足任一条件的关联关系，并非“短路”只取第一个匹配项。

内容的提问来源于stack exchange，提问作者stackcen