06. 多表查询

6.1 一个案例引入多表查询

现在有两张表employees和department。

现在的要求是

# 查询每个员工的姓名和对应的部门名称
SELECT last_name, department_name 
FROM employees, departments;
# 部分结果举例
/*
| Gietz       | Payroll              |
| Gietz       | Recruiting           |
| Gietz       | Retail Sales         |
| Gietz       | Government Sales     |
| Gietz       | IT Helpdesk          |
| Gietz       | NOC                  |
| Gietz       | IT Support           |
| Gietz       | Operations           |
| Gietz       | Contracting          |
| Gietz       | Construction         |
| Gietz       | Manufacturing        |
| Gietz       | Benefits             |
| Gietz       | Shareholder Services |
| Gietz       | Control And Credit   |
| Gietz       | Corporate Tax        |
| Gietz       | Treasury             |
| Gietz       | Accounting           |
| Gietz       | Finance              |
| Gietz       | Executive            |
| Gietz       | Sales                |
| Gietz       | Public Relations     |
| Gietz       | IT                   |
| Gietz       | Shipping             |
| Gietz       | Human Resources      |
| Gietz       | Purchasing           |
| Gietz       | Marketing            |
| Gietz       | Administration       |
+-------------+----------------------+
2889 rows in set (1.04 sec)
*/

从结果上看最终查询的数量是 employees表的行数 * departments表的行数 (107 * 27 )。

那么为什么会出现这种现象呢？

笛卡尔积

这里需要引入笛卡尔积的概念，什么是笛卡尔积呢？

从图中可以看出，笛卡尔积就是所有可能性结果，也称之为交叉连接，英文是CROSS JOIN，SQL99中也是使用CROSS JOIN来表示交叉连接，作用就是可以将任意表进行连接，哪怕两张表不相关。

笛卡尔积错误的产生

• 省略多个表的连接条件
• 连接条件无效
• 所有表中的所有行互相连接

为了避免笛卡尔积的条件，需要用WHERE加入有效的连接条件

# 查询每个员工的姓名和对应的部门名称
SELECT last_name, department_name 
FROM employees, departments
WHERE employees.department_id = departments.department_id;

6.2 多表查询的分类

等值连接 VS 非等值连接

等值连接

利用一张表的某列的值和另外一张表中某列的值相等的关系，把多余的数据过滤掉

刚才的查询就是一个典型的等值连接

# 查询每个员工的姓名和对应的部门名称
SELECT last_name, department_name 
FROM employees, departments
WHERE employees.department_id = departments.department_id;

• 多个条件可以用逻辑运算符连接起来

• 如果两个表中有相同的列名，这个时候需要带上表名.区分，可以给 表取名然后用别名区分

  注：如果给表起了别名，过了条件中只能使用别名，不能使用原来的表名

• 多个表连接需要更多的条件，N个表连接至少愮用N-1个条件

非等值连接

除了利用相等，还可以利用范围条件实现两个表的查询。

# 查询所有员工的工资等级
SELECT e.last_name, e.salary, j.grade_level
FROM employees e, job_grades j
WHERE e.salary BETWEEN j.lowest_sal AND j.highest_sal;

自连接 VS 非自连接

• table1和table2本质上是同一张表，只是取别名的方式虚拟成两张表代表不同的意义，然后两张表再进行内连接，外连接等查询，这就是自连接。

# 查询employees表，返回“Xxx works for Xxx”
SELECT CONCAT(worker.last_name," works for ",manager.last_name)
FROM employees worker, employees manager
WHERE worker.manager_id = manager.employee_id;

# 查询出last_name为 ‘Chen’ 的员工的 manager 的信息
SELECT manager.*
FROM employees worker, employees manager
WHERE worker.last_name = 'Chen' AND worker.manager_id = manager.employee_id;

内连接 VS 外连接

刚才上面的查询都是内连接

• 内连接：合并具有同一列的两个以上的表的行，结果集中不包含一个表和另一个表不匹配的行

• 外连接：两个个表在连接过程中除了返回满足连接条件的行以外还返回左(或右)表中不满足条件的行，这种连接称为左（或右）外连接，没有匹配的行，结果表中相对应的列为NULL

• 如果是左外连接，则连接条件中的左边的表为主表，右边的表为从表

  如果是左外连接，则连接条件中的右边的表为主表，左边的表为从表

6.3 多表查询的实现

基本语法

# 使用JOIN ON子句创建连接
SELECT table1.column, table2.column,table3.column
FROM table1
	JOIN table2 ON table1 和 table2 的连接条件
		JOIN table3 ON table2 和 table3 的连接条件

内连接(INNER JOIN)的实现

• 语法

  SELECT 字段列表
  FROM A表 INNER JOIN B表
  ON 关联条件
  WHERE 等其他子句;
  

• 练习

  继续用刚才的等值连接的题目

  # 查询每个员工的姓名和对应的部门名称
  SELECT last_name, department_name 
  FROM employees INNER JOIN departments
  ON employees.department_id = departments.department_id;
  
  # INNER JOIN可以省略成JOIN
  # 查询员工工号以及对应的工作城市和工作部门
  SELECT employee_id, city, deparment_name
  FROM employees e JOIN departments d
  ON e.department_id = d.department_id
  JOIN ON locations l
  ON d.location_id = l.location_id;
  

​

外连接(OUTER JOIN)的实现

• 语法

  # 左外连接，A表是主表，最终结果包含A表所有内容
  SELECT 字段列表
  FROM A表 LEFT OUTER JOIN B表
  ON 关联条件
  WHERE 等其他子句;
  
  # 右外连接，B表是主表，最终结果包含B表所有内容
  SELECT 字段列表
  FROM A表 RIGHT OUTER JOIN B表
  ON 关联条件
  WHERE 等其他子句;
  

  和刚才一样，这里的OUTER可以省略

• 举例

  # 左外连接
  # 查询所有员工对应的部门和部门名称
  SELECT e.last_name, e.employee_id, d.department_id, department_name
  FROM employees e LEFT JOIN departments d
  ON e.department_id = d.department_id;
  
  /*
  有一条数据是没有部门的但是还会在查询结果中
  | Grant       |         178 | NULL          | NULL             |
  */
  
  # 右外连接
  # 查询部门对应的员工
  SELECT e.last_name, e.employee_id, d.department_id, department_name
  FROM employees e RIGHT JOIN departments d
  ON e.department_id = d.department_id;
  
  /*
  有几个部门没有员工也会在查询结果中
  | NULL        | NULL        |           120 | Treasury             |
  | NULL        | NULL        |           130 | Corporate Tax        |
  | NULL        | NULL        |           140 | Control And Credit   |
  | NULL        | NULL        |           150 | Shareholder Services |
  | NULL        | NULL        |           160 | Benefits             |
  | NULL        | NULL        |           170 | Manufacturing        |
  | NULL        | NULL        |           180 | Construction         |
  | NULL        | NULL        |           190 | Contracting          |
  | NULL        | NULL        |           200 | Operations           |
  | NULL        | NULL        |           210 | IT Support           |
  | NULL        | NULL        |           220 | NOC                  |
  | NULL        | NULL        |           230 | IT Helpdesk          |
  | NULL        | NULL        |           240 | Government Sales     |
  | NULL        | NULL        |           250 | Retail Sales         |
  | NULL        | NULL        |           260 | Recruiting           |
  | NULL        | NULL        |           270 | Payroll              |
  */
  

• 满外连接

  • FULL OUTER JOIN 就是满外连接，所有的查询结果都会在出现，无论左边右边的表是否有对应结果
  • MYSQL不支持FULL JOIN，采用 UNION替代

UNION

UNION有两种查询，分别是UNION和UNION ALL

• UNION操作

  返回两个结果集的并集，并且去掉重复记录

• UNION ALL操作

  返回两个结果集的并集，不去掉重复记录

UNION ALL所需要的资源比UNION少，如果确定结果没有重复记录，建议使用UNION ALL，提高数据查询速度。

练习

# 查询部门编号>90或邮箱包含a的员工信息
# 直接查询
SELECT * FROM employees e
WHERE e.department_id > 90 OR e.email LIKE "%a%";
# UNION查询
SELECT * FROM employees e1
WHERE e1.email LIKE "%a%"
UNION
SELECT * FROM employees e2
WHERE e2.department_id > 90;

# 查询中国用户中男性的信息以及美国用户中年男性的用户信息
# 表t_chinamale 和 表 t_usmale
SELECT id,cname FROM t_chinamale WHERE csex='男'
UNION ALL
SELECT id,tname FROM t_usmale WHERE tGender='male';

6.4 7种SQL JOIN的实现

# 还是拿员工表和部门表作为例子

# 左上角 左外连接
SELECT e.employee_id, e.last_name, department_name
FROM employees e LEFT JOIN departments d
ON e.department_id = d.department_id;
# 右上角 右外连接
SELECT e.employee_id, e.last_name, department_name
FROM employees e RIGHT JOIN departments d
ON e.department_id = d.department_id;
# 中间图内连接
SELECT e.employee_id, e.last_name, department_name
FROM employees e JOIN departments d
ON e.department_id = d.department_id;

# 左下图 这个就是两个集合的并集用UNION就可以处理
SELECT e.employee_id, e.last_name, department_name
FROM employees e LEFT JOIN departments d
ON e.department_id = d.department_id
UNION
SELECT e.employee_id, e.last_name, department_name
FROM employees e RIGHT JOIN departments d
ON e.department_id = d.department_id;

其余四个需要一定的解释，但是拿出来说

先说左中图和右中图，两个是一致的只是最终的结果是相反的

现在以左中图作为例子，实质上结果是 A - A∩B，

# 左中图  A - A∩B
# 可以先查询出A的外连接
SELECT e.employee_id, e.last_name, department_name
FROM employees e LEFT JOIN departments d
ON e.department_id = d.department_id
WHERE d.department_id IS NULL; # 在这里需要删掉B为NULL的时候

这里有的同学可能会误解为什么是判断B表的字段是不是NULL为什么不是A表的？

这里解释一下，JOIN之后的操作实质上就是从笛卡尔集中取出数据，那么在查询出A表的外连接的时候，表会变成什么样呢，可以一起来看看

可能有人会说，这里A表B表的字段都是NULL，为什么不判断B表的呢？
在这里我想说的是，这里尚硅谷给的例子比较特殊，用的是外键去判断，如果这里的条件不是用等值的外键去判断呢，假设A没有这个字段，是不是只有B才有这个字段，也就是说当前获取的集合里面只能通过B表去判断是否为NULL！！！

同理

# 右中图 B - A∩B
SELECT e.employee_id, e.last_name, department_name
FROM employees e RIGHT JOIN departments d
ON e.department_id = d.department_id
WHERE e.department_id IS NULL; # 在这里需要删掉B为NULL的时候

剩下的左下图就是两个的并集

SELECT e.employee_id, e.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
SELECT e.employee_id, e.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.5 SQL99的新特性

自然连接

自然连接就是NATURAL JOIN ，自然连接会自动匹配两张表的相同字段，然后进行等值连接

# 正常内连接写法
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`;

# 使用自然连接
SELECT employee_id,last_name,department_name
FROM employees e NATURAL JOIN departments d;

USING连接

USING连接和自然连接很接近，自然连接时自动匹配所有相同字段，USING是指定一个相同字段

# 内连接
SELECT employee_id,last_name,department_name
FROM employees e ,departments d
WHERE e.department_id = d.department_id;

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

注：可以看出来内连接和刚才的等值连接很像，只是所使用的语法不同，其实从实质上两个可以理解成相同的，在SQL92规范中，内连接就是等值连接，而外连接只需要用+，但是MySQL不支持这种写法，现在的SQL99虽然写起来繁琐，但是比较好分辨。

#左外连接
SELECT last_name,department_name
FROM employees ,departments
WHERE employees.department_id = departments.department_id(+);
#右外连接
SELECT last_name,department_name
FROM employees ,departments
WHERE employees.department_id(+) = departments.department_id;

6.6 练习

练习一

#【题目】
# 1.显示所有员工的姓名，部门号和部门名称。
SELECT e.last_name, e.department_id, d.department_name
FROM employees e LEFT JOIN departments d
ON e.department_id = d.department_id;

# 2.查询90号部门员工的job_id和90号部门的location_id
SELECT job_id, location_id
FROM employees e, departments d
WHERE e.`department_id` = d.`department_id`
AND e.`department_id` = 90;

# 3.选择所有有奖金的员工的 last_name , department_name , location_id , city
SELECT e.last_name, d.department_name, d.location_id, l.city
FROM employees e LEFT JOIN departments d
ON e.department_id = d.department_id
LEFT JOIN locations l
ON d.location_id = l.location_id 
WHERE e.commission_pct IS NOT NULL;

# 4.选择city在Toronto工作的员工的 
# 等值连接
SELECT e.last_name , e.job_id , e.department_id , d.department_name, l.city
FROM employees e, departments d, locations l
WHERE e.department_id = d.department_id
AND d.location_id = l.location_id
AND l.city = "Toronto";
# 外连接
SELECT e.last_name , e.job_id , e.department_id , d.department_name, l.city
FROM employees e
JOIN departments d
ON e.department_id = d.department_id
JOIN locations l
ON d.location_id = l.location_id
WHERE l.city = "Toronto";

# 5.查询员工所在的部门名称、部门地址、姓名、工作、工资，其中员工所在部门的部门名称为’Executive’
# 等值连接
SELECT department_name, l.street_address, e.last_name, e.job_id, e.salary
FROM employees e, departments d, locations l
WHERE e.department_id = d.department_id
AND d.department_name = "Executive"
AND d.location_id = l.location_id;

# 外连接
SELECT department_name, street_address, last_name, job_id, salary
FROM employees e JOIN departments d
ON e.department_id = d.department_id
JOIN locations l
ON d.`location_id` = l.`location_id`
WHERE department_name = 'Executive';

# 6.选择指定员工的姓名，员工号，以及他的管理者的姓名和员工号，结果类似于下面的格式
SELECT e.last_name `employees`, e.employee_id `EMP#`, m.last_name `manager`, e.manager_id `Mgr#`
FROM employees e
LEFT JOIN employees m
ON e.manager_id = m.employee_id;


employees	Emp#	manager	Mgr#
kochhar 	101		king		100

# 7.查询哪些部门没有员工
SELECT d.department_name
FROM departments d LEFT JOIN employees e
ON e.department_id = d.department_id
WHERE e.employee_id IS NULL;

SELECT d.department_name
FROM departments d
WHERE NOT EXISTS (SELECT * FROM employees e WHERE e.department_id = d.department_id);

# 8. 查询哪个城市没有部门
SELECT l.location_id, l.city
FROM locations l LEFT JOIN departments d
ON l.location_id = d.location_id
WHERE d.department_id IS NULL; 

# 9. 查询部门名为 Sales 或 IT 的员工信息
SELECT e.employee_id, e.last_name, d.department_name
FROM employees e JOIN departments d
ON e.department_id = d.department_id
WHERE d.department_name IN("Sales", "IT");

练习二

# 储备：建表操作：
CREATE TABLE `t_dept` (
`id` INT(11) NOT NULL AUTO_INCREMENT,
`deptName` VARCHAR(30) DEFAULT NULL,
`address` VARCHAR(40) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
CREATE TABLE `t_emp` (
`id` INT(11) NOT NULL AUTO_INCREMENT,
`name` VARCHAR(20) DEFAULT NULL,
`age` INT(3) DEFAULT NULL,
`deptId` INT(11) DEFAULT NULL,
empno int not null,
PRIMARY KEY (`id`),
KEY `idx_dept_id` (`deptId`)
#CONSTRAINT `fk_dept_id` FOREIGN KEY (`deptId`) REFERENCES `t_dept` (`id`)
) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

INSERT INTO t_dept(deptName,address) VALUES('华山','华山');
INSERT INTO t_dept(deptName,address) VALUES('丐帮','洛阳');
INSERT INTO t_dept(deptName,address) VALUES('峨眉','峨眉山');
INSERT INTO t_dept(deptName,address) VALUES('武当','武当山');
INSERT INTO t_dept(deptName,address) VALUES('明教','光明顶');
INSERT INTO t_dept(deptName,address) VALUES('少林','少林寺');
INSERT INTO t_emp(NAME,age,deptId,empno) VALUES('风清扬',90,1,100001);
INSERT INTO t_emp(NAME,age,deptId,empno) VALUES('岳不群',50,1,100002);
INSERT INTO t_emp(NAME,age,deptId,empno) VALUES('令狐冲',24,1,100003);
INSERT INTO t_emp(NAME,age,deptId,empno) VALUES('洪七公',70,2,100004);
INSERT INTO t_emp(NAME,age,deptId,empno) VALUES('乔峰',35,2,100005);
INSERT INTO t_emp(NAME,age,deptId,empno) VALUES('灭绝师太',70,3,100006);
INSERT INTO t_emp(NAME,age,deptId,empno) VALUES('周芷若',20,3,100007);
INSERT INTO t_emp(NAME,age,deptId,empno) VALUES('张三丰',100,4,100008);
INSERT INTO t_emp(NAME,age,deptId,empno) VALUES('张无忌',25,5,100009);
INSERT INTO t_emp(NAME,age,deptId,empno) VALUES('韦小宝',18,null,100010);
【题目】
【题目】
#1.所有有门派的人员信息
（ A、B两表共有）
# 内连接
SELECT * 
FROM t_emp 
JOIN t_dept
ON t_emp.deptId = t_dept.id;
# 等值连接
SELECT t_emp.*
FROM t_emp,t_dept
WHERE t_emp.deptId = t_dept.id;

#2.列出所有用户，并显示其机构信息
（A的全集）
# 左连接
SELECT * 
FROM t_emp 
LEFT JOIN t_dept
ON t_emp.deptId = t_dept.id;

#3.列出所有门派
（B的全集）
SELECT * 
FROM t_dept;

#4.所有不入门派的人员
（A的独有）
# 使用NOT EXISTS
SELECT *
FROM t_emp
WHERE NOT EXISTS(SELECT * FROM t_dept WHERE t_dept.id = t_emp.deptId);
# 外连接
SELECT *
FROM t_emp
LEFT JOIN t_dept
ON t_emp.deptId = t_dept.id
WHERE t_emp.deptId IS NULL;

#5.所有没人入的门派
（B的独有）
SELECT * 
FROM t_dept
LEFT JOIN t_emp
ON t_dept.id = t_emp.deptId
WHERE t_emp.deptId IS NULL;

#6.列出所有人员和机构的对照关系
(AB全有)
#MySQL Full Join的实现 因为MySQL不支持FULL JOIN,下面是替代方法
#left join + union(可去除重复数据)+ right join
SELECT *
FROM t_emp
LEFT JOIN t_dept
ON t_emp.deptId = t_dept.id
UNION
SELECT *
FROM t_emp
RIGHT JOIN t_dept
ON t_emp.deptId = t_dept.id


#7.列出所有没入派的人员和没人入的门派
（A的独有+B的独有）
SELECT *
FROM t_emp
LEFT JOIN t_dept
ON t_emp.deptId = t_dept.id
WHERE t_dept.id IS NULL
UNION
SELECT *
FROM t_emp
RIGHT JOIN t_dept
ON t_emp.deptId = t_dept.id
WHERE t_emp.id IS NULL;