LEFT OUTER JOIN 工作时出现问题

Question

我以为我了解左外连接的工作原理，但我遇到了一种不起作用的情况，并且我不能 100% 确定我的查询结构是否不正确，或者是否是数据问题。

作为背景，我有以下 MySQL 表结构：

mysql> describe achievement;
+-------------+----------------------+------+-----+---------+-------+
| Field       | Type                 | Null | Key | Default | Extra |
+-------------+----------------------+------+-----+---------+-------+
| id          | varchar(64)          | NO   | PRI | NULL    |       |
| game_id     | varchar(10)          | NO   | PRI | NULL    |       |
| name        | varchar(64)          | NO   |     | NULL    |       |
| description | varchar(255)         | NO   |     | NULL    |       |
| image_url   | varchar(255)         | NO   |     | NULL    |       |
| gamerscore  | smallint(5) unsigned | NO   |     | 0       |       |
| hidden      | tinyint(1)           | NO   |     | 0       |       |
| base_hidden | tinyint(1)           | NO   |     | 0       |       |
+-------------+----------------------+------+-----+---------+-------+
8 rows in set (0.00 sec)

至于

mysql> describe gamer_achievement;
+----------------+---------------------+------+-----+---------+-------+
| Field          | Type                | Null | Key | Default | Extra |
+----------------+---------------------+------+-----+---------+-------+
| game_id        | varchar(10)         | NO   | PRI | NULL    |       |
| achievement_id | varchar(64)         | NO   | PRI | NULL    |       |
| gamer_id       | varchar(36)         | NO   | PRI | NULL    |       |
| earned_epoch   | bigint(20) unsigned | NO   |     | 0       |       |
| offline        | tinyint(1)          | NO   |     | 0       |       |
+----------------+---------------------+------+-----+---------+-------+
5 rows in set (0.00 sec)

数据，这就是我在此处填充的内容（为了简洁起见，仅包含相关列）：

+----+------------+------------------------------+
| id | game_id    | name                         |
+----+------------+------------------------------+
| 1  | 1480656849 | Cluster Buster               |
| 2  | 1480656849 | Star Gazer                   |
| 3  | 1480656849 | Flower Child                 |
| 4  | 1480656849 | Oyster-meister               |
| 5  | 1480656849 | Big Cheese of the South Seas |
| 6  | 1480656849 | Hexic Addict                 |
| 7  | 1480656849 | Collapse Master              |
| 8  | 1480656849 | Survivalist                  |
| 9  | 1480656849 | Tick-Tock Doc                |
| 10 | 1480656849 | Marathon Mogul               |
| 11 | 1480656849 | Millionaire Extraordinaire   |
| 12 | 1480656849 | Grand Pearl Pooh-Bah         |
+----+------------+------------------------------+
12 rows in set (0.00 sec)

在这种特殊情况下

+----------------+------------+--------------+---------+
| achievement_id | game_id    | earned_epoch | offline |
+----------------+------------+--------------+---------+
| 1              | 1480656849 |            0 |       1 |
| 2              | 1480656849 |            0 |       1 |
| 3              | 1480656849 |            0 |       1 |
| 4              | 1480656849 |   1149789371 |       0 |
| 7              | 1480656849 |   1149800406 |       0 |
| 8              | 1480656849 |            0 |       1 |
| 9              | 1480656849 |   1149794790 |       0 |
| 10             | 1480656849 |   1149792417 |       0 |
+----------------+------------+--------------+---------+
8 rows in set (0.02 sec)

，achievement 表是“主”表将包含我一直想查看的信息。 gamer_achievement 表仅包含实际获得的成就的信息。对于任何特定玩家的任何特定游戏，gamer_achievement 表中可以有任意数量的行 - 如果该游戏没有获得任何成就，则不包括任何行。例如，在上面的示例数据中，尚未获得 ID 为 5、6、11 和 12 的成就。

我目前所写的是，

select a.id,
       a.name,
       ga.earned_epoch,
       ga.offline
from   achievement a 
       LEFT OUTER JOIN gamer_achievement ga 
       ON (a.id = ga.achievement_id and a.game_id = ga.game_id)
where  ga.gamer_id = 'fba8fcaa-f57b-44c6-9431-4ab78605b024' 
       and a.game_id = '1480656849'
order by convert (a.id, unsigned)

但这仅返回实际获得的成就的完整信息 - 右侧表 (gamer_achievement) 中未获得的成就信息未显示为 NULL 值正如我对这种类型的查询所期望的那样。这就是我期待看到的：

+----+-------------------------------+--------------+---------+
| id | name                          | earned_epoch | offline |
+----+-------------------------------+--------------+---------+
| 1  | Cluster Buster                |            0 |       1 |
| 2  | Star Gazer                    |            0 |       1 |
| 3  | Flower Child                  |            0 |       1 |
| 4  | Oyster-meister                |   1149789371 |       0 |
| 5  | Big Cheese of the South Seas  |         NULL |    NULL |
| 6  | Hexic Addict                  |         NULL |    NULL |
| 7  | Collapse Master               |   1149800406 |       0 |
| 8  | Survivalist                   |            0 |       1 |
| 9  | Tick-Tock Doc                 |   1149794790 |       0 |
| 10 | Marathon Mogul                |   1149792417 |       0 |
| 11 | Millionaire Extraordinaire    |         NULL |    NULL |
| 12 | Grand Pearl Pooh-Bah          |         NULL |    NULL |
+----+-------------------------------+--------------+---------+
12 rows in set (0.00 sec)

我在这里错过了什么？据我了解，基本查询对我来说看起来是正确的，但我显然错过了一些关键信息。

Answer 1

很多人已经回答了，但我也会尝试并希望提供更多澄清。我一直如何解释它（您可以检查我用左连接回复的许多其他帖子），我尝试从第一个（左侧......因此从左到右阅读）列出我想要的所有内容。然后根据它们之间的条件左连接到“其他”表（右侧）...然后，在进行左连接时，并且右侧表有其他条件，这些条件将保留该连接条件。通过将它们带入“WHERE”子句将意味着 INNER JOIN（必须始终匹配），这不是您想要的...我还尝试始终显示左表 alias.field = 右表 alias.field 以保持相关性清除...然后，将 where 子句应用到您想要从第一个表中获得的基本条件..类似于

select 
      a.id,
      a.name,
      ga.earned_epoch,
      ga.offline
   from   
      achievement a 
         LEFT OUTER JOIN gamer_achievement ga 
             ON a.id = ga.achievement_id
            AND a.game_id = ga.game_id
            AND ga.gamer_id = 'fba8fcaa-f57b-44c6-9431-4ab78605b024'
   where
      a.game_id = '1480656849'
   order by 
      convert (a.id, unsigned)

注意“a”和“ga”之间通过公共 ID 和游戏 ID 值的直接关系，但随后添加了特定的游戏玩家。 where 子句仅关心基于特定游戏的外部成就级别。

Answer 2

在 WHERE 子句中，您将丢弃 LEFT JOIN 会用 NULL 值填充的一些行。您希望将条件 ga.gamer_id = 'fba8fcaa-f57b-44c6-9431-4ab78605b024' 放入 JOIN 子句内。

另一种选择是：

 LEFT OUTER JOIN (SELECT * FROM gamer_achievement
                   WHERE  ga.gamer_id = 'fba8fcaa-f57b-44c6-9431-4ab78605b024' 
                 ) ga 

记住执行了join，此时如果不能满足条件，就来NULL值；然后应用 where 过滤器。

Answer 3

WHERE 子句从整个结果集中过滤结果。如果您只想将过滤器应用于 JOIN，则可以将表达式添加到 ON 子句。

在以下查询中，我已将适用于联接表 (ga.gamer_id =) 的筛选表达式从 WHERE 子句移至 ON 子句。这可以防止表达式过滤掉 gamer_achievement 值为 NULL 的行。

SELECT a.id,
       a.name,
       ga.earned_epoch,
       ga.offline
FROM   achievement a 
       LEFT OUTER JOIN gamer_achievement ga 
       ON ga.achievement_id = a.id
       AND ga.game_id = a.game_id
       AND ga.gamer_id = 'fba8fcaa-f57b-44c6-9431-4ab78605b024'
WHERE
       a.game_id = '1480656849'
ORDER BY CONVERT(a.id, UNSIGNED)

Answer 4

这是因为这一行：

where  ga.gamer_id = 'fba8fcaa-f57b-44c6-9431-4ab78605b024'

如果gamer没有获得achievement，则ga.gamer_id值将为NULL code> 且不符合 WHERE 条件。

Answer 5

我的猜测是，where 子句正在过滤掉您想要的结果，将其移动到左连接可能会起作用。

select a.id, 
       a.name, 
       ga.earned_epoch, 
       ga.offline 
from   achievement a  
       LEFT OUTER JOIN gamer_achievement ga  
       ON (a.id = ga.achievement_id and 
           a.game_id = ga.game_id and 
           ga.gamer_id = 'fba8fcaa-f57b-44c6-9431-4ab78605b024' and
           a.game_id = '1480656849') 
order by convert (a.id, unsigned)