Mysql梳理10——使用SQL99实现7中JOIN操作
10 使用SQL99实现7中JOIN操作
10.1 使用SQL99实现7中JOIN操作
本案例的数据库文件分享：
通过百度网盘分享的文件：atguigudb.sql
链接：https://pan.baidu.com/s/1iEAJIl0ne3Y07kHd8diMag?pwd=2233
提取码：2233
# 正中图
SELECT employee_id,last_name,department_name
FROM employees e JOIN departments d
on e.department_id=d.department_id;#左上图
SELECT employee_id,last_name,department_name
FROM employees e LEFT JOIN departments d
ON e.department_id=d.department_id;#右上图
SELECT employee_id,last_name,department_name
FROM employees e RIGHT JOIN departments d
ON e.department_id=d.department_id;# 左中图(A-A∩B)
select employee_id,last_name,department_name
FROM employees e RIGHT JOIN departments d
on e.department_id=d.department_id
where d.department_id is null;
# 右中图(B-A∩B)
SELECT employee_id,last_name,department_name
FROM employees e LEFT JOIN departments d
on e.department_id=d.department_id
where e.department_id is null;#左下图：满外连接
SELECT employee_id,last_name,department_name
FROM employees e LEFT JOIN departments d
ON e.department_id=d.department_id
UNION ALL
SELECT employee_id,last_name,department_name
FROM employees e RIGHT JOIN departments d
ON e.department_id=d.department_id
WHERE e.department_id is null;#右下图：满外连接-A∩B
SELECT employee_id,last_name,department_name
FROM employees e LEFT JOIN departments d
ON e.department_id=d.department_id
WHERE d.department_id is null
UNION ALL
SELECT employee_id,last_name,department_name
FROM employees e RIGHT JOIN departments d
ON e.department_id=d.department_id
WHERE e.department_id is null;10.2 语法格式小结
#中间图
#实现A∩B(内连接)
select 字段列表
from A表 inner join从表
on 关联条件
#左上图
#实现A
select 字段列表
from A表 left join 从表
on 关联条件
where 从表关联字段 is null and 等其他子句;
#左中图
#实现A - A∩B
select 字段列表
from 主表 left join 从表(B)
on 关联条件
where 从表关联字段 is null ;
#右上图
#实现B
select 字段列表
from 主表 right join 从表(B)
on 关联条件
where 从表关联字段 is null and 等其他子句;
#右中图
#实现B - A∩B
select 字段列表
from 主表 right join 从表(B)
on 关联条件
where 从表关联字段 is null ;
#左下图
#实现查询结果是A∪B
### 用左外的A，union all 右外的B-（A∩B）
select 字段列表
from 主表 left join 从表(B)
on 关联条件
where 等其他子句
union all
select 字段列表
from 主表 right join 从表(B)
on 关联条件
where 等其他子句;
#右下图
select 字段列表
from 主表 left join 从表
on 关联条件
where 从表 is null
union all
select 字段列表
from 主表 right join 从表
on 关联条件
where 从表 is null;
10.3 SQL99语法新特性
10.3.1 自然连接
SQL99 在 SQL92 的基础上提供了一些特殊语法，比如 NATURAL JOIN 用来表示自然连接。我们可以把
自然连接理解为 SQL92 中的等值连接。它会帮你自动查询两张连接表中 所有相同的字段 ，然后进行 等值
连接 。
在SQL92标准中：
SELECT employee_id,last_name,department_name
FROM employees e JOIN departments d
ON e.department_id = d.department_id
AND e.manager_id = d.manager_id;在 SQL99 中你可以写成：
SELECT employee_id,last_name,department_name
FROM employees e NATURAL JOIN departments d;10.3.2 USING连接
当我们进行连接的时候，SQL99还支持使用 USING 指定数据表里的同名字段 进行等值连接。但是只能配合JOIN一起使用。比如：
SELECT employee_id,last_name,department_name
FROM employees e JOIN departments d
USING (department_id);
你能看出与自然连接 NATURAL JOIN 不同的是，USING 指定了具体的相同的字段名称，你需要在 USING的括号 () 中填入要指定的同名字段。同时使用 JOIN...USING 可以简化 JOIN ON 的等值连接。
它与下面的 SQL 查询结果是相同的：
SELECT employee_id,last_name,department_name
FROM employees e ,departments d
WHERE e.department_id = d.department_id;10.4 章节小结
表连接的约束条件可以有三种方式：WHERE, ON, USING
WHERE：适用于所有关联查询 ON ：只能和JOIN一起使用，只能写关联条件。虽然关联条件可以并到WHERE中和其他条件一起写，但分开写可读性更好。
USING：只能和JOIN一起使用，而且要求两个关联字段在关联表中名称一致，而且只能表示关联字段值相等
#关联条件
#把关联条件写在where后面
SELECT last_name,department_name
FROM employees,departments
WHERE employees.department_id = departments.department_id;
#把关联条件写在on后面，只能和JOIN一起使用
SELECT last_name,department_name
FROM employees INNER JOIN departments
ON employees.department_id = departments.department_id;
SELECT last_name,department_name
FROM employees CROSS JOIN departments
ON employees.department_id = departments.department_id;
SELECT last_name,department_name
FROM employees JOIN departments
ON employees.department_id = departments.department_id;
#把关联字段写在using()中，只能和JOIN一起使用
#而且两个表中的关联字段必须名称相同，而且只能表示=
#查询员工姓名与基本工资
SELECT last_name,job_title
FROM employees INNER JOIN jobs USING(job_id);
#n张表关联，需要n-1个关联条件
#查询员工姓名，基本工资，部门名称
SELECT last_name,job_title,department_name FROM employees,departments,jobs
WHERE employees.department_id = departments.department_id
AND employees.job_id = jobs.job_id;
SELECT last_name,job_title,department_name
FROM employees INNER JOIN departments INNER JOIN jobs
ON employees.department_id = departments.department_id
AND employees.job_id = jobs.job_id;