RIGHT JOIN

右连接返回右表中的所有记录，即是左表中没有任何满足匹配条件的记录。这意味着，如果 ON 子句在左表中匹配到了 0 条记录，该连接仍然会返回至少一条记录，不过返回的记录中所有来自左表的字段都为 NULL。

这就意味着，右连接会返回右表中的所有记录，加上左表中匹配到的记录，或者是 NULL （如果连接谓词无法匹配到任何记录的话）。

1 RIGHT JOIN 基本语法

SELECT column_name(s)
FROM table1
RIGHT JOIN table2
ON table1.column_name=table2.column_name;

或

SELECT column_name(s)
FROM table1
RIGHT OUTER JOIN table2
ON table1.column_name=table2.column_name;

注释：在某些数据库中，RIGHT JOIN 称为 RIGHT OUTER JOIN。

演示数据库

在本教程中，我们将使用 LintCode 样本数据库。

下面是选自 "teachers" 教师表的数据：

下面是 "courses" 课程表的数据：

2 RIGHT JOIN 实例

下面的 SQL 语句将返回所有教师的名称和所教的课程名称 (没有教授课程则课程名为 NULL)。

以下实例中我们把 courses 作为左表，teachers 作为右表：

SELECT c.name AS course_name, t.name AS teacher_name
FROM courses c
    RIGHT JOIN teachers t ON c.teacher_id = t.id;

执行输出结果

mysql> SELECT c.name AS course_name, t.name AS teacher_name
    -> FROM courses c
    ->  RIGHT JOIN teachers t ON c.teacher_id = t.id;
+-------------------------+------------------+
| course_name             | teacher_name     |
+-------------------------+------------------+
| Big Data                | Eastern Heretic  |
| Data Analysis           | Eastern Heretic  |
| Dynamic Programming     | Eastern Heretic  |
| NULL                    | Northern Beggar  |
| System Design           | Western Venom    |
| Django                  | Western Venom    |
| Artificial Intelligence | Western Venom    |
| Java P6+                | Western Venom    |
| Senior Algorithm        | Southern Emperor |
| Web                     | Southern Emperor |
| Object Oriented Design  | Southern Emperor |
| NULL                    | Linghu Chong     |
+-------------------------+------------------+
12 rows in set (0.00 sec)

练习题：RIGHT JOIN

使用 RIGHT JOIN 查询 所有 ' country' 等于 'CN' 的教师的名称和所教的课程名称 (没有教授课程则课程名为 NULL)。

代码输入框：

SELECT c.name AS course_name, t.name AS teacher_name
FROM  courses c
    RIGHT JOIN teachers t ON c.teacher_id = t.id
WHERE t.country = 'CN';

执行输出结果

+------------------------+------------------+
| course_name            | teacher_name     |
+------------------------+------------------+
| Big Data               | Eastern Heretic  |
| Data Analysis          | Eastern Heretic  |
| Dynamic Programming    | Eastern Heretic  |
| NULL                   | Northern Beggar  |
| Senior Algorithm       | Southern Emperor |
| Web                    | Southern Emperor |
| Object Oriented Design | Southern Emperor |
| NULL                   | Linghu Chong     |
+------------------------+------------------+