如何简化多列外键关联查询？SQL语句优化需求

表结构 #

relationship表 #

+-----------+-----------+--------------+
| playerid1 | playerid2 | relationship |
+-----------+-----------+--------------+
|         1 |         3 | friend       |
|         1 |         2 | enemy        |
+-----------+-----------+--------------+

player表 #

+----+--------+
| id | name   |
+----+--------+
|  1 | Hattie |
|  2 | Jazz   |
|  3 | Ben    |
+----+--------+

需求 #

查询所有互为敌人的玩家名称，例如返回 'Hattie', 'Jazz'。

现有问题SQL #

我目前写出的SQL逻辑重复、代码冗长：

select 
  player1.name, 
  player2.name 
from 
  (
    select 
      player.name 
    from 
      player, 
      relationship r 
    where 
      r.relationship = 'enemy' 
      and player.id = r.playerid1
  ) player1, 
  (
    select 
      player.name 
    from 
      relationship r, 
      player 
    where 
      r.relationship = 'enemy' 
      and player.id = r.playerid2
  ) player2;

想知道有没有更简洁的写法？我知道可以通过select playerid1, playerid2 from relationship where relationship = 'enemy';获取敌人ID对，但希望找到更优雅的关联查询方式，避免手动编写重复子查询。

优化后的SQL #

可以通过两次关联player表的方式直接获取双方名称，代码简洁且逻辑清晰：

select 
    p1.name as player1_name,
    p2.name as player2_name
from 
    relationship r
join 
    player p1 on r.playerid1 = p1.id
join 
    player p2 on r.playerid2 = p2.id
where 
    r.relationship = 'enemy';

优化说明 #

• 使用显式JOIN语法替代隐式逗号关联，可读性更强；
• 给player表设置两个别名p1和p2，分别关联relationship表的playerid1和playerid2，一次查询就能获取双方名称；
• 过滤条件集中在WHERE子句，没有重复逻辑，代码更简洁。

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