在JOIN ON条款中，OR是如何工作的？我有一个包含winner_id和opponent_id列的表games和一个包含team_id列和名称列的teams表.opponent_id和winner_id都是指team_id中的一支球队，我需要它来获得name支球队.
SELECT *
FROM teams
LEFT JOIN games ON teams.team_id = games.winner_id
OR teams.team_id = games.opponent_id
我认为OR表示如果操作数的左或右表达式的计算结果为真，则插入数据.但这样做的目的是为winner_id和opponent_id创建单独的行，而不是所有行上的所有行？
我的预期结果是，它将在winner_id上联接，因为它是第一个表达式，并且只获得获胜者的名称，而忽略ative_id，因为第一个表达式为真？
这是我得到的结果:
| team_id | name | game_id | year | round | winner_id | opponent_id | winner_goals | opponent_goals |
|---|---|---|---|---|---|---|---|---|
| 393 | France | 97 | 2018 | Final | 393 | 394 | 4 | 2 |
| 393 | France | 100 | 2018 | Semi-Final | 393 | 395 | 1 | 0 |
| 393 | France | 104 | 2018 | Quarter-Final | 393 | 400 | 2 | 0 |
| 393 | France | 112 | 2018 | Eighth-Final | 393 | 408 | 4 | 3 |
| 393 | France | 120 | 2014 | Quarter-Final | 409 | 393 | 1 | 0 |
| 393 | France | 123 | 2014 | Eighth-Final | 393 | 413 | 2 | 0 |
| 394 | Croatia | 97 | 2018 | Final | 393 | 394 | 4 | 2 |
| 394 | Croatia | 99 | 2018 | Semi-Final | 394 | 396 | 2 | 1 |
| 394 | Croatia | 101 | 2018 | Quarter-Final | 394 | 397 | 3 | 2 |
| 394 | Croatia | 109 | 2018 | Eighth-Final | 394 | 405 | 2 | 1 |
| 395 | Belgium | 98 | 2018 | Third Place | 395 | 396 | 2 | 0 |
| 395 | Belgium | 100 | 2018 | Semi-Final | 393 | 395 | 1 | 0 |
和我的团队坐在桌子上
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)
还有我的游戏桌
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)