从关联表获取数据：查询主场落败且与胜者同场馆的球队

首先，我们来梳理下你的需求：需要找出那些在主场输球，并且自己和获胜的客队使用相同场馆ID的球队。结合你提供的数据，因为所有球队的STADIUM_ID都是独立的，所以最终结果确实会是空的。

你的原查询的问题分析 #

你写的查询有两个关键问题：

• 没有筛选主场落败的条件：完全没判断主队得分低于客队得分，也就是缺少HOMESCORE < OPPONENTSCORE这个核心筛选逻辑；
• 子查询无法访问外部表：你在子查询里直接引用matches.home_id，但这种嵌套子查询的写法无法调用外部查询的matches表字段，会导致数据库报错。

正确的SQL查询方案 #

最清晰且高效的写法是使用JOIN关联表，把matches和teams表关联两次（分别对应主队和客队），然后在WHERE子句里加上筛选条件：

SELECT t_home.NAME AS home_team_name
FROM matches m
INNER JOIN teams t_home ON m.HOMETEAM_ID = t_home.ID
INNER JOIN teams t_opponent ON m.OPPONENT_ID = t_opponent.ID
WHERE 
    -- 筛选主场落败：主队得分 < 客队得分
    m.HOMESCORE < m.OPPONENTSCORE
    -- 主客队场馆ID相同
    AND t_home.STADIUM_ID = t_opponent.STADIUM_ID;

这个查询的逻辑说明：

1. 用INNER JOIN把比赛表和球队表关联两次，分别获取主队（t_home）和客队（t_opponent）的场馆信息；
2. 先通过m.HOMESCORE < m.OPPONENTSCORE筛选出主队输球的比赛；
3. 再通过t_home.STADIUM_ID = t_opponent.STADIUM_ID筛选出主客场馆ID相同的情况；
4. 因为你提供的所有球队STADIUM_ID都不重复，所以执行后会返回空结果，符合预期。

如果你更倾向于用子查询的写法，可以用EXISTS来实现正确的关联：

SELECT t_home.NAME AS home_team_name
FROM teams t_home
WHERE EXISTS (
    SELECT 1
    FROM matches m
    JOIN teams t_opponent ON m.OPPONENT_ID = t_opponent.ID
    WHERE 
        m.HOMETEAM_ID = t_home.ID
        AND m.HOMESCORE < m.OPPONENTSCORE
        AND t_home.STADIUM_ID = t_opponent.STADIUM_ID
);

这个写法是从球队表出发，判断当前球队是否存在符合条件的主场输球且场馆ID和客队相同的比赛，逻辑和JOIN写法完全一致。

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