MySQL 的inner join (on,where,using)三种用法

表一

mysql> select * from departments;
+---------------+------------+
| department_id | dept_name  |
+---------------+------------+
|             1 | Sales      |
|             2 | Markting   |
|             3 | Finance    |
|             4 | Accounting |
|             5 | Warehouse  |
|             6 | Production |
+---------------+------------+
6 rows in set (0.00 sec)

　　表二

mysql> select * from employees;
+----+------------+-----------+---------------+
| id | first_name | last_name | department_id |
+----+------------+-----------+---------------+
|  1 | John       | Doe       |             1 |
|  2 | Bush       | Lily      |             2 |
|  3 | David      | Dave      |             3 |
|  4 | Mary       | Jane      |             4 |
|  5 | Jonatha    | Josh      |             5 |
|  6 | Mateo      | More      |             1 |
+----+------------+-----------+---------------+
6 rows in set (0.00 sec)

　　inner join 用法1：

mysql> select id,first_name,last_name,dept_name from employees inner join departments on employees.department_id = departments.department_id;
+----+------------+-----------+------------+
| id | first_name | last_name | dept_name  |
+----+------------+-----------+------------+
|  1 | John       | Doe       | Sales      |
|  2 | Bush       | Lily      | Markting   |
|  3 | David      | Dave      | Finance    |
|  4 | Mary       | Jane      | Accounting |
|  5 | Jonatha    | Josh      | Warehouse  |
|  6 | Mateo      | More      | Sales      |
+----+------------+-----------+------------+
6 rows in set (0.00 sec) 

　　inner join 用法2：

mysql> select id,first_name,last_name,dept_name from employees inner join departments using (department_id);
+----+------------+-----------+------------+
| id | first_name | last_name | dept_name  |
+----+------------+-----------+------------+
|  1 | John       | Doe       | Sales      |
|  2 | Bush       | Lily      | Markting   |
|  3 | David      | Dave      | Finance    |
|  4 | Mary       | Jane      | Accounting |
|  5 | Jonatha    | Josh      | Warehouse  |
|  6 | Mateo      | More      | Sales      |
+----+------------+-----------+------------+
6 rows in set (0.00 sec) 

　　inner join用法3：

mysql> select id,first_name,last_name,dept_name from employees inner join departments where employees.department_id = departments.department_id;
+----+------------+-----------+------------+
| id | first_name | last_name | dept_name  |
+----+------------+-----------+------------+
|  1 | John       | Doe       | Sales      |
|  2 | Bush       | Lily      | Markting   |
|  3 | David      | Dave      | Finance    |
|  4 | Mary       | Jane      | Accounting |
|  5 | Jonatha    | Josh      | Warehouse  |
|  6 | Mateo      | More      | Sales      |
+----+------------+-----------+------------+
6 rows in set (0.00 sec)