Mysql第二章 多表查询的操作

因为直接连接多表时，笛卡尔积的问题引出了多表联查的问题，多表查询基本分为三类，外连接和内连接，等值和非等值，自连接和非自连接

一 外连接与内连接的概念

这些连接关系，归根结底是集合的交并补运算
求出两个表的公共部分，叫做内连接，相当于是交集
求出两个表的公共部分加上左边的，叫做左外连接
如果是左外连接，则连接条件中左边的表也称为 主表 ，右边的表称为从表
如果是右外连接，则连接条件右边的表称为主表，左边的表称为从表

sql99语法实现 默认是内连接

SELECT last_name,department_name
FROM employees e INNER JOIN departments d
on e.department_id=d.department_id

结果显示为102条数据：

sql99语法实现左外连接，把没有部门的员工也查出来

SELECT last_name,department_name
FROM employees e LEFT  OUTER JOIN departments d
on e.department_id=d.department_id;

结果为103条数据
相当于是左边是员工表的全部信息，右边是部门表的部分信息
员工表和部门表的交集，e交d,和部门表为NULL但员工表有人的信息

sql99语法实现右外连接，把没有人的部门查出来

SELECT last_name,department_name
FROM employees e RIGHT  OUTER JOIN departments d
on e.department_id=d.department_id;

结果如图可见，有119条信息

也就是关键是右边的部门表，所以叫做，右外连接，首要查出的是所有的部门

sql99语法实现满外连接，mysql不支持这样写

SELECT  last_name,department_name
FROM employees e
FULL OUTER departments d
ON e.department_id=d.department_id 

mysql中如果要实现满外连接的效果，推荐使用union关键字

Union关键字，返回一个并集，类似于A并B，会执行去重检索的操作
union all 返回并集加上交集 ，优点是效率比较高

• 查询部门编号>90或邮箱包含a的员工信息

SELECT * FROM employees WHERE email LIKE '%a%' 
UNION SELECT * FROM employees WHERE department_id>90;

• 查询所有部门号和所有员工姓名，需要去重

#查找所有的员工名字，以及所有的部门
SELECT last_name,department_name
FROM employees e LEFT  OUTER JOIN departments d
on e.department_id=d.department_id
UNION 
SELECT last_name,department_name
FROM employees e RIGHT  OUTER JOIN departments d
on e.department_id=d.department_id;

结果显示出来118条信息

这里出现了一个问题，那就是，右外连接的数据，居然比去重后的并集数目还要多，以后再解决吧

• 查询所有部门号和所有员工姓名，不需要去重
  结果222条信息

SELECT last_name,department_name
FROM employees e LEFT  OUTER JOIN departments d
on e.department_id=d.department_id
UNION ALL
SELECT last_name,department_name
FROM employees e RIGHT  OUTER JOIN departments d
on e.department_id=d.department_id;

二 自连接和非自连接的概念

• 自连接，就是多表查询中自己引用自己
  查询员工id,员工姓名，管理者的ID和姓名

# 查询员工id,员工姓名，管理者的ID和姓名
SELECT emp.employee_id,emp.last_name,mgr.employee_id 经理工号,mgr.last_name 经理名字
FROM employees emp,employees mgr
WHERE emp.manager_id=mgr.employee_id;

• 非自连接，普通的多表查询

SELECT last_name,department_name
FROM employees e INNER JOIN departments d
on e.department_id=d.department_id

三 等值连接和非等值连接的概念

1.1 等值连接

等值连接也称为显示内连接，在进行多表联合查询时通过“=”等号来连接多张表之间相字段对应的值，其产生的结果会出现重复列。意思是，如果对多张表进行等值连接操作，那么前提要求是这多张表之间必须有相同的字段名。，比方说一个表的主键是另一个表的外键

SELECT last_name,department_name
FROM employees e INNER JOIN departments d
on e.department_id=d.department_id

1.1 非等值连接

非等值连接最大的特点就是：连接条件中的关系是非等量关系

查询一个员工的名字，工资和所处的等级

SELECT last_name,salary,grade_level
FROM employees e,job_grades j
WHERE e.salary BETWEEN j.lowest_sal and j.highest_sal;

四 七种JOIN的实现

1 内连接 A∩B

# 内连接 A∩B
SELECT e.employee_id,e.last_name,d.department_name
FROM employees e
JOIN departments d
ON  e.department_id=d.department_id;

2 左外连接

SELECT e.employee_id,e.last_name,d.department_name
FROM employees e
LEFT JOIN departments d
ON e.department_id=d.department_id;

3 右外连接

# 右外连接，右边的项目作为主表，左边的是从表
SELECT e.employee_id,e.last_name,d.department_name
FROM employees e
RIGHT JOIN departments d 
ON e.department_id =d.department_id;

4 A - A∩B

# A - A∩B 有名字，没有部门
SELECT e.employee_id ,e.last_name,d.department_name
FROM employees e
LEFT JOIN departments d
ON e.department_id=d.department_id
WHERE d.department_id
IS NULL;

5 B - A∩B

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;

6 满外连接

左外连接并上B - A∩B

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
union all
SELECT e.employee_id,e.last_name,d.department_name
FROM employees e
LEFT JOIN departments d
ON e.department_id=d.department_id;

7 满外连接- 内连接

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
union all
SELECT e.employee_id ,e.last_name,d.department_name
FROM employees e
LEFT JOIN departments d
ON e.department_id=d.department_id
WHERE d.department_id
IS NULL;

五 natural join与USING

自动查询两张连接表中 所有相同的字段 ，然后进行 等值 连接

SELECT employee_id,last_name,department_name 
FROM employees e NATURAL JOIN departments d;

USING相对于natural join 优化了一点

SELECT employee_id,last_name,department_name
FROM employees e JOIN departments d 
USING (department_id);

多表查询需要限制，太多了相当于多重for循环，消耗资源