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

表一

?

1 2 3 4 5 6 7 8 9 10 11 12

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)

　　表二

?

1 2 3 4 5 6 7 8 9 10 11 12

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：

?

1 2 3 4 5 6 7 8 9 10 11 12

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：

?

1 2 3 4 5 6 7 8 9 10 11 12

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：

?

1 2 3 4 5 6 7 8 9 10 11 12

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)