INNER JOIN

最常用也最重要的连接形式是内连接，有时候也被称作“EQUIJOIN”（等值连接）。

内连接根据连接谓词来组合两个表中的字段，以创建一个新的结果表。SQL 查询会比较逐个比较表 1 和表 2 中的每一条记录，来寻找满足连接谓词的所有记录对。当连接谓词得以满足时，所有满足条件的记录对的字段将会结合在一起构成结果表。

3.1.1 INNER JOIN 基本语法

SELECT table1.column1, table2.column2...
FROM table1
INNER JOIN table2
ON table1.common_field = table2.common_field;

或

SELECT table1.column1, table2.column2...
FROM table1
JOIN table2
ON table1.common_field = table2.common_field;

INNER JOIN 中 INNER 可以省略不写

演示数据库

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

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

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

3.1.2 INNER JOIN 实例

请注意，"courses" 表中的 "teacher_id" 列指向 "teachers" 表中的字段 "id"。上面这两个表是通过 "teacher_id" 列联系起来的。

然后，如果我们运行下面的 SQL 语句（包含 INNER JOIN）：

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

执行输出结果

mysql> SELECT c.id, c.name AS course_name, t.name AS teacher_name
    -> FROM courses c
    ->  INNER JOIN teachers t ON c.teacher_id = t.id;
+----+-------------------------+------------------+
| id | course_name             | teacher_name     |
+----+-------------------------+------------------+
|  1 | Senior Algorithm        | Southern Emperor |
|  2 | System Design           | Western Venom    |
|  3 | Django                  | Western Venom    |
|  4 | Web                     | Southern Emperor |
|  5 | Big Data                | Eastern Heretic  |
|  6 | Artificial Intelligence | Western Venom    |
|  7 | Java P6+                | Western Venom    |
|  8 | Data Analysis           | Eastern Heretic  |
| 10 | Object Oriented Design  | Southern Emperor |
| 12 | Dynamic Programming     | Eastern Heretic  |
+----+-------------------------+------------------+
10 rows in set (0.05 sec)

练习题：INNER JOIN

查询 “Eastern Heretic” 老师所教的所有课程的课程名和课程id。

代码输入框：

SELECT c.id, c.name AS course_name, t.name AS teacher_name
FROM teachers t
    INNER JOIN courses c ON c.teacher_id = t.id AND t.name = 'Eastern Heretic';

执行输出结果

+----+---------------------+-----------------+
| id | course_name         | teacher_name    |
+----+---------------------+-----------------+
|  5 | Big Data            | Eastern Heretic |
|  8 | Data Analysis       | Eastern Heretic |
| 12 | Dynamic Programming | Eastern Heretic |
+----+---------------------+-----------------+