多表查询：内连接、外连接、子链接查询

前言：多表查询是非常重要的！其实就是多张表一起查询（当然是有一定条件的查询）

引入

首先我们先建立两个表：

CREATE TABLE DEPARTMENT (
	DEP_ID INT PRIMARY KEY AUTO_INCREMENT, -- 主键
	DEP_NAME VARCHAR(20)
);

CREATE TABLE EMPLOYEE (
   ID INT PRIMARY KEY AUTO_INCREMENT, -- 主键
   NAME VARCHAR(20) NOT NULL,
   AGE INT,
   SALARY INT,
   JOIN_DATE TIMESTAMP,
   DEP_ID INT, -- 外键
   CONSTRAINT EMP_DEP_ID FOREIGN KEY (DEP_ID) REFERENCES DEPARTMENT (DEP_ID) -- 添加外键
);

按照我们之前的查询方法：
SELECT * FROM DEPARTMENT,EMPLOYEE；就是这样的结果：（多表查询出来的结果集也叫笛卡尔积）

概念：笛卡尔积：A，B两个集合，这两个集合的所有组成情况。

eg：A集合中有三个元素，B集合中有两个元素，那么A集合和B集合的笛卡尔积就有六种组合情况。

这里有很多不合理的数据：eg：我们添加外键的目的就是让多个员工对应一个部门，而不是多个部门对应一个员工（eg：第二三行的员工数据和部门对应不上）。所以我们需要消除这些无用的数据，或者说保留我们想要的数据

多表查询的分类

我们就用一下三种方式消除无用的数据：
1.内连接查询
2.外连接查询
3.子链接查询

1.内连接查询

1.1隐式内连接

• 就是向查询表时的where条件一样。eg：

 SELECT * FROM DEPARTMENT,EMPLOYEE WHERE DEPARTMENT.`DEP_ID` = EMPLOYEE.`DEP_ID`;
 (这里面的`DEP_ID` = EMPLOYEE.`DEP_ID`的单引号可以省略不写)

就会查询到下面的结果：

当然我们也可以把*换成指定的字段（字段就是一个表的某一列名称）eg：查询员工所对应的工资和部门：
(正规工作时我们都会：1.条件写一行，关键字写一行，方便别人也方便自己阅读。2.一般我们都会给这个表起个别名，方便我们书写。3.写注释。)

SELECT 
	t2.name, -- 员工表的姓名
	t2.SALARY, -- 员工表的薪水
	t1.DEP_NAME -- 部门表的名字
FROM 
	DEPARTMENT t1, -- 部门表起别名为t1
	EMPLOYEE t2 -- 员工表起别名为t2
WHERE 
	t1.`DEP_ID` = t2.`DEP_ID`;

结果：

1.2显式内连接

同样的需求（查询员工所对应的工资和部门：）用显式内连接：

SELECT 
	t1.name, -- 员工表的姓名
	t1.SALARY, -- 员工表的薪水
	t2.DEP_NAME -- 部门表的名字
FROM 
	EMPLOYEE T1
INNER JOIN
	DEPARTMENT T2
ON
	T1.`DEP_ID` = T2.`DEP_ID`;

（关键字’INNER’时可以省略的）

可以看到可以达到一样的效果。

内连接查询我们需要知道些什么？
我们需要知道：

• 1.从哪些表中查。
• 2.筛选的条件是什么。
• 3.查询哪些字段。

2.外连接查询

需求：假如现在公司又来一个员工 马儿扎哈，但由于刚来，暂时没有部门，现在我要查询所有员工，如果这个员工有部门就显示部门，没有则只显示姓名等相关信息即可。

• 首先添加数据：

INSERT INTO 
	EMPLOYEE (ID,NAME,AGE,SALARY) 
VALUES 
	(NULL,'马儿扎哈',22,12000);

• 其次，实现查询：
  我们先用内连接的方法试一下：

SELECT 
	t2.name, -- 员工表的姓名
	t2.SALARY, -- 员工表的薪水
	t1.DEP_NAME -- 部门表的名字
FROM 
	DEPARTMENT t1, -- 部门表起别名为t1
	EMPLOYEE t2 -- 员工表起别名为t2
WHERE 
	t1.`DEP_ID` = t2.`DEP_ID`;

发现没有马儿扎哈？！
原因一猜就知道了呗，因为新员工在员工表的DEP_ID是NULL，在父表 部门表中没有相对应的部门，所以当然不显示了。于是内连接无法实现我们这个需求，但外连接可以实现：

2.1左外连接查询

语法：

SELECT 
	T1.`NAME`, -- 员工表的姓名
	T1.`AGE`, -- 员工表的年龄
	T2.`DEP_NAME` -- 部门表的名字
FROM
	EMPLOYEE T1
LEFT OUTER JOIN
	DEPARTMENT T2
ON 
	T1.`DEP_ID`= T2.`DEP_ID`;

(关键字‘OUTER’可省略)

左外连接查询的作用：查询出左表的所有数据和交集部门的数据（哪个表写在前面哪个表就是左表）

所以，我们在查询时，把员工表写在了前面，所以左外连接就会查询出所有的员工表指定字段的数据，加上，同时显示满足条件的指定字段数据，也就满足了需求。

运行结果：

2.2右外连接查询

其实会了左外查询，右外查询自然就会了，一个道理。只要把关键字‘LEFT’变成->'RIGHT’即可。

右外连接查询的作用：查询出右表的所有数据和交集部门的数据（哪个表写在后面哪个表就是右表）

SELECT
	T1.`NAME`,
	T1.`AGE`,
	T2.`DEP_NAME`
FROM 
	DEPARTMENT T2
RIGHT JOIN 
	EMPLOYEE T1
ON 
	T1.`DEP_ID` = T2.`DEP_ID`;

（把员工表放在右面（写在后面）就可以达到一样的效果了）

总结：
其实左外连接和右外连接在使用时没什么太大区别，就是一个相对的概念；用哪个都能达到想要的效果，换一下位置就行了。所以左外连接和右外连接我们掌握一个就够了

3.子链接查询

• 什么是子链接查询？
  查询里面嵌套查询，这个嵌套查询就叫做子链接查询。

我们可以某一部的查询结果直接作为结果，再次当为条件使用。
eg：

• 需求：想要查询员工里工资最高的员工所有信息？

1. 首先我们需要先知道最高的工资是多少：
2. 查询所有信息即可。

SELECT MAX(employee.`SALARY`) FROM employee -- 查询最高工资

-- 子链接查询：
SELECT  -- 查询工资最高的员工所有信息
	*
FROM
	employee T1
WHERE 
	T1.`SALARY` = (SELECT MAX(employee.`SALARY`) FROM employee);

子查询情况：

1.结果为单行单列，(即一个结果)。

如果是单行单列为第一次查询结果，我们常可以用它进行>,<,<=……的操作。

如上面的例子

SELECT MAX(employee.`SALARY`) FROM employee -- 查询最高工资

-- 子链接查询：
SELECT  -- 查询工资最高的员工所有信息
	*
FROM
	employee T1
WHERE 
	T1.`SALARY` = (SELECT MAX(employee.`SALARY`) FROM employee);

查询结果：

2.结果为多行单列

如果是多行单列为第一次查询结果，我们就可以用‘IN……’进行操作。

eg：

• 需求：查询指定部门所有员工的姓名和薪水（这里以‘开发部’，‘策划部’为例）

1. 首先找到指定部门所对应的部门号。
2. 以这个部门号去查找满足条件的员工

代码：
（提一下：DEP_NAME = ‘开发部’ OR ‘策划部’ 这条sql语句等价于 DEP_NAME IN (‘开发部’,‘策划部’)）

-- 查找指定部门对应的DEP_ID:
SELECT 
	T1.`DEP_ID` 
FROM 
	department T1 
WHERE 
	T1.`DEP_NAME` = '开发部' OR T1.`DEP_NAME` = '策划部';

-- 子链接查询：
SELECT 
	T1.`NAME`,
	T1.`SALARY`
FROM
	employee T1
WHERE 
	T1.`DEP_ID` IN (SELECT T2.`DEP_ID` FROM department T2 WHERE T2.`DEP_NAME` IN ('开发部','策划部'));

查询结果:

3.结果为多行多列

结果为多行多列，不就相当于一个表了嘛，就把他当成一个表就行。这个表也叫 “虚拟表”。

eg：

• 需求：查询某某日期之后入职的员工信息和部门信息

1. 查询所有在某日期之后入职的员工信息
   2.第一步查询到的应该是一个表，用这个表再进行操作

-- 查询在2020-05-07 11:40:01后入职的所有员工信息
SELECT 
	* 
FROM 
	employee 
WHERE 
	employee.JOIN_DATE > '2020-05-07 11:40:01';

结果：

那么显然这个查询得到的是一个表，我们只要把这个表也当作表来使用即可。

SELECT 
	* 
FROM
	department T1,
	(SELECT 
		* 
	FROM 
		employee 
	WHERE 
		employee.JOIN_DATE > '2020-05-07 11:40:01') T2
WHERE
	T1.`DEP_ID` = T2.DEP_ID;

运行结果：

后续：

• 表的查询往往都不是只有一个方法的，比如最后一个例子用内连接查询也完全可以：

SELECT 
	*
FROM
	employee T1,
	department T2
WHERE
	T1.`JOIN_DATE` > '2020-05-07 11:40:01' 
AND
	T1.`DEP_ID` = T2.`DEP_ID`;