mysql - 让 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

很多人已经回答了，但我也会尝试，希望能提供更多说明。我一直是如何解释它的(你可以查看我用 LEFT 连接回复的许多其他帖子)，我尝试从第一个开始列出我想要的所有内容(左侧......因此从左到右阅读)。然后在它们之间的任何条件下将左连接到“其他”表(右侧)......然后，在进行左连接时，并且有针对右侧表的附加条件，这些条件将保持该连接条件.通过将它们带入“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)

通过公共(public) ID 和游戏 ID 值注意“a”和“ga”之间的直接关系，但随后会附加到特定的游戏玩家身上。 where 子句只关心基于特定游戏的外部成就。