如何用Nested Query实现与INNER JOIN等效的SQL查询效果

等价嵌套查询实现 #

下面两种写法和你给出的INNER JOIN语句执行结果完全一致，均符合嵌套查询的语法要求：

写法1：基于EXISTS相关子查询实现（标准嵌套查询写法）

SELECT 
  PLAYERS.PLAYER_TAG, 
  PLAYERS.POSITION, 
  PLAYERS.TOTAL_KILLS,
  (SELECT TEAM_NAMES FROM TEAMS WHERE TEAMS.TEAM_NAMES = PLAYERS.TEAM) AS TEAM_NAMES,
  (SELECT GAMES_PLAYED FROM TEAMS WHERE TEAMS.TEAM_NAMES = PLAYERS.TEAM) AS GAMES_PLAYED,
  (SELECT WINS FROM TEAMS WHERE TEAMS.TEAM_NAMES = PLAYERS.TEAM) AS WINS
FROM PLAYERS
WHERE EXISTS (
  SELECT 1 FROM TEAMS 
  WHERE TEAMS.TEAM_NAMES = PLAYERS.TEAM
)
ORDER BY TEAM_NAMES ASC;

逻辑说明：

• 外层查询遍历PLAYERS表的所有行
• EXISTS子查询过滤掉没有对应球队记录的球员，这部分逻辑和INNER JOIN的关联过滤完全等价
• SELECT列表里的三个相关子查询分别取出匹配球队的对应字段，和原语句返回的字段完全一致

写法2：基于派生表的嵌套查询实现（执行效率更高）

SELECT 
  PLAYERS.PLAYER_TAG, 
  PLAYERS.POSITION, 
  PLAYERS.TOTAL_KILLS,
  t.TEAM_NAMES,
  t.GAMES_PLAYED,
  t.WINS
FROM PLAYERS,
     (SELECT TEAM_NAMES, GAMES_PLAYED, WINS FROM TEAMS) t
WHERE t.TEAM_NAMES = PLAYERS.TEAM
ORDER BY t.TEAM_NAMES ASC;

逻辑说明：

• 先通过子查询把TEAMS表需要的字段查询出来作为临时派生表t
• 外层查询关联PLAYERS表和派生表t，关联条件和原INNER JOIN完全一致
• 绝大多数数据库的查询优化器会将该写法和原INNER JOIN生成完全相同的执行计划，性能无差异

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