[筆記][SQL] 連接join

在學習菜鳥教程里的MySQL教程時，對左右連接的結果有點不解。

其中有如下兩個表：

runoob_tbl:
+-----------+--------------+---------------+-----------------+
| runoob_id | runoob_title | runoob_author | submission_date |
+-----------+--------------+---------------+-----------------+
|         1 | 學習PHP      | 菜鳥教程      | 2017-04-12      |
|         2 | 學習MySQL    | 菜鳥教程      | 2017-04-12      |
|         3 | 學習Java     | RUNOOB.COM    | 2015-05-01      |
|         4 | 學習Python   | RUNOOB.COM    | 2016-03-06      |
|         5 | 學習C        | FK            | 2017-03-06      |
+-----------+--------------+---------------+-----------------+

tcount_tbl:
+---------------+--------------+
| runoob_author | runoob_count |
+---------------+--------------+
| 菜鳥教程      |           10 |
| RUNOOB.COM    |           20 |
| GOOGLE        |           22 |
+---------------+--------------+

執行右連接語句後：

SELECT a.runoob_id, a.runoob_author, b.runoob_count 
FROM runoob_tbl a RIGHT JOIN tcount_tbl b 
ON a.runoob_author = b.runoob_author;

得到的結果是：

+-----------+---------------+--------------+
| runoob_id | runoob_author | runoob_count |
+-----------+---------------+--------------+
|         1 | 菜鳥教程      |           10 |
|         2 | 菜鳥教程      |           10 |
|         3 | RUNOOB.COM    |           20 |
|         4 | RUNOOB.COM    |           20 |
|      NULL | NULL          |           22 |
+-----------+---------------+--------------+

我對這個結果感到很奇怪，右連接是會返回右表的所有行，不管有無匹配，但右表runoob_author明明有Google這個數據，為什麼會是NULL。

試著把所有列列印出來：

+-----------+--------------+---------------+-----------------+---------------+--------------+
| runoob_id | runoob_title | runoob_author | submission_date | runoob_author | runoob_count |
+-----------+--------------+---------------+-----------------+---------------+--------------+
|         1 | 學習PHP      | 菜鳥教程      | 2017-04-12      | 菜鳥教程      |           10 |
|         2 | 學習MySQL    | 菜鳥教程      | 2017-04-12      | 菜鳥教程      |           10 |
|         3 | 學習Java     | RUNOOB.COM    | 2015-05-01      | RUNOOB.COM    |           20 |
|         4 | 學習Python   | RUNOOB.COM    | 2016-03-06      | RUNOOB.COM    |           20 |
|      NULL | NULL         | NULL          | NULL            | GOOGLE        |           22 |
+-----------+--------------+---------------+-----------------+---------------+--------------+

可以看到GOOGLE還是在的。這才明白原來select的是左表的runoob_author，所以列印出來才會是NULL。只要換成b.runoob_author就可以看到期望的結果了：

+---------------+--------------+
| runoob_author | runoob_count |
+---------------+--------------+
| 菜鳥教程      |           10 |
| RUNOOB.COM    |           20 |
| GOOGLE        |           22 |
+---------------+--------------+