mysql里inner on_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)