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;

我们要控制连接表的数量。多表连接就相当于嵌套 for 循环一样，非常消耗资源，会让 SQL 查询性能下降得很严重，因此不要连接不必要的表。在许多 DBMS 中，也都会有最大连接表的限制。

【强制】超过三个表禁止 join。需要 join 的字段，数据类型保持绝对一致；

多表关联查询时，保证被关联的字段需要有索引。

说明：即使双表 join 也要注意表索引、SQL 性能。