SQL连接

一、NATURAL JOIN
用employees和jobs表作为例子，NATURAL JOIN会自动匹配相同的列来连接表

相同的列只有job_id、但是必须要有一个相同的列作为连接，否则不能连接两张表中的不同的列
SELECT employees.employee_id,employees.first_name,job_id,jobs.job_title FROM employees NATURAL JOIN jobs;

来自不同表的返回值可以加前缀来识别也可以不用加，但是相同连接列不能，否则报错


二、INNER JOIN
接下来用employees和department表作为例子来明INNER JOIN的特性

DEPARTMENT_ID MANAGER_ID作为两张表共有的特征
NATURAL JOIN会自动匹配他们

如果我只需要其中一个列作为匹配连接需要用到INNER JOIN内连接

DEPARTMENT_ID 为连接，而且要输出MANAGER_ID必需要加前缀，否则报错

内连接还有如下的表达方式：
SELECT e.employee_id,e.last_name,e.department_id,d.department_id,d.location_id
FROM employees e JOIN departments d
ON (e.department_id = d.department_id)

SELECT employee_id,city,department_name
FROM employees e
JOIN departments d
ON d.department_id = e.department_id
JOIN locations l
ON d.location_id = l.location_id;

三个表之间的连接
SELECT e.employee_id, l.city, d.department_name
FROM employees e
JOIN departments d
USING (department_id)
JOIN locations l
USING (location_id) 同上的USING写法
中间的表一定会有第一张和第三张表的相同匹配列，不然无法连接

带有筛选的写法：
SELECT e.employee_id,e.last_name,e.department_id,d.department_id,d.location_id
FROM employees e JOIN departments d
ON (e.department_id = d.department_id)
AND e.manager_id = 149;

SELECT e.employee_id,e.last_name,e.department_id,d.department_id,d.location_id
FROM employees e JOIN departments d
ON (e.department_id = d.department_id)
WHERE e.manager_id = 149;