我以为我理解了左连接的工作原理,但是我遇到了一种情况,它没有起作用,而且我不确定我的查询结构是否有误,或者它是一个数据问题。
背景:我有以下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)
我在这里错过了什么?从我的理解来看,基本查询语句对我来说是正确的,但显然我缺少一些关键信息。