SQL笔记 -- 多表查询（内连接、左外连接、右外连接、UNION）

多表查询（内连接、左外连接、右外连接、UNION）

1.概念知识

1.1内连接

如果要在表A与表B中进行内连接查询，那么就相当于是在求集合A与集合B的“交集”，即，返回结果只会包含与两个表都匹配的数据。举例如下：
员工表emp：

emp_id	emp_name	depart_id
1	张三	1
2	李四	2
3	王五	3
4	赵六	4

部门表depart:

depart_id	depart_name
1	开发
2	测试
3	运维
5	销售

表emp与表depart都有字段depart_id，现在通过条件emp.depart_id = depart.depart_id对两表进行内连接，结果如下：

emp_id	emp_name	depart_id	depart_name
1	张三	1	开发
2	李四	2	测试
3	王五	3	运维

可以看到，员工赵六的depart_id与表depart中的depart_id不匹配，不符合条件emp.depart_id = depart.depart_id，因此结果中没有赵六的信息。部门销售部也同理。

1.2 外连接

两个表在连接过程中不仅会返回满足连接条件的行，还会返回主表中不满足条件的行 ，这种连接称为外连接。外连接由包含左外连接、右外连接和满外连接。

1.2.1 左外连接

如果是左外连接，则连接条件中左边的表称为主表 ，右边的表称为从表。例如表A左外连接表B时，表A就是主表，连接结果不仅包含满足连接条件的行（也就是内连接的结果），还包含表A中不符合条件的行。
拿上面例子举例，表emp左外连接表depart的结果如下：

emp_id	emp_name	depart_id	depart_name
1	张三	1	开发
2	李四	2	测试
3	王五	3	运维
4	赵六	4	null

1.2.2 右外连接

如果是右外连接，则连接条件中右边的表称为主表 ，左边的表称为从表 。例如表A右外连接表B时，表B就是主表，连接结果不仅包含满足连接条件的行（也就是内连接的结果），还包含表B中不符合条件的行。
拿上面例子举例，表emp右外连接表depart的结果如下：

emp_id	emp_name	depart_id	depart_name
1	张三	1	开发
2	李四	2	测试
3	王五	3	运维
null	null	5	销售

1.2.3 满外连接

满外连接可以理解成两个表都是主表，也就是说，连接结果包含三部分：满足连接条件的行（也就是内连接的结果）、表A中不符合条件的行、表B中不符合条件的行。
拿上面例子举例，表emp满外连接表depart的结果如下：

emp_id	emp_name	depart_id	depart_name
1	张三	1	开发
2	李四	2	测试
3	王五	3	运维
4	赵六	4	null
null	null	5	销售

2. 代码示例

2.1 内连接

-- 语法格式 两表连接
SELECT 字段列表
FROM A表 INNER JOIN B表
ON 关联条件
WHERE 等其他子句;

-- 语法格式 三表连接
SELECT 字段列表
FROM A表 
	INNER JOIN B表 ON 关联条件
		INNER JOIN C表 ON 关联条件
WHERE 等其他子句;

注意：INNER可以省略

举例：

SELECT e.employee_id, e.emp_name, e.depart_id, d.depart_name
FROM emp e JOIN depart d
ON e.depart_id = d.depart_id;

2.2 外连接

2.2.1 左外连接

-- 语法格式 两表连接
SELECT 字段列表
FROM A表 LEFT OUTER JOIN B表
ON 关联条件
WHERE 等其他子句;

-- 语法格式 三表连接
SELECT 字段列表
FROM A表 
	LEFT OUTER JOIN B表 ON 关联条件
		LEFT OUTER JOIN C表 ON 关联条件
WHERE 等其他子句;

注意：OUTER可以省略

举例：

SELECT e.employee_id, e.emp_name, e.depart_id, d.depart_name
FROM emp e LEFT JOIN depart d
ON e.depart_id = d.depart_id;

2.2.2 右外连接

-- 语法格式 两表连接
SELECT 字段列表
FROM A表 RIGHT OUTER JOIN B表
ON 关联条件
WHERE 等其他子句;

-- 语法格式 三表连接
SELECT 字段列表
FROM A表 
	RIGHT OUTER JOIN B表 ON 关联条件
		RIGHT OUTER JOIN C表 ON 关联条件
WHERE 等其他子句;

注意：OUTER可以省略

举例：

SELECT e.employee_id, e.emp_name, e.depart_id, d.depart_name
FROM emp e RIGHT JOIN depart d
ON e.depart_id = d.depart_id;

2.2.3 满外连接

Oracle等数据库可以使用FULL JOIN 或 FULL OUTER JOIN 来实现。

-- 语法格式 两表连接
SELECT 字段列表
FROM A表 FULL OUTER JOIN B表
ON 关联条件
WHERE 等其他子句;

-- 语法格式 三表连接
SELECT 字段列表
FROM A表 
   FULL OUTER JOIN B表 ON 关联条件
   	FULL OUTER JOIN C表 ON 关联条件
WHERE 等其他子句;

注意：OUTER可以省略

举例：

SELECT e.employee_id, e.emp_name, e.depart_id, d.depart_name
FROM emp e FULL JOIN depart d
ON e.depart_id = d.depart_id;

MySQL不支持FULL JOIN，要用到UNION来实现满外连接。

-- 语法格式 
SELECT column,... FROM table1
UNION [ALL]
SELECT column,... FROM table2

注意：UNION 与 UNION ALL的差别在于 UNION 会对连接结果自动去重，因此效率要低于UNION ALL。

举例：

SELECT e.employee_id, e.emp_name, e.depart_id, d.depart_name
FROM emp e LEFT JOIN depart d
ON e.depart_id = d.depart_id
UNION ALL
SELECT e.employee_id, e.emp_name, e.depart_id, d.depart_name
FROM emp e RIGHT JOIN depart d
ON e.depart_id = d.depart_id
WHILE e.depart_id IS NULL;

3. 七种连接结果的实现

3.1 左外连接

SELECT e.employee_id, e.emp_name, e.depart_id, d.depart_name
FROM emp e LEFT JOIN depart d
ON e.depart_id = d.depart_id;

3.1 右外连接

SELECT e.employee_id, e.emp_name, e.depart_id, d.depart_name
FROM emp e RIGHT JOIN depart d
ON e.depart_id = d.depart_id;

3.3 内连接

SELECT e.employee_id, e.emp_name, e.depart_id, d.depart_name
FROM emp e JOIN depart d
ON e.depart_id = d.depart_id;

3.4 emp - emp ∩ depart

可以看到，该代码只比左外连接多了一行WHILE子句。这是因为emp ∩ depart的部分是符合连接条件的，因此该部分既有e.depart_id，也有d.depart_id。而咱们只想要的不包含公共交集的部分是没有d.depart_id的，因此进行一个筛选就好。

SELECT e.employee_id, e.emp_name, e.depart_id, d.depart_name
FROM emp e LEFT JOIN depart d
ON e.depart_id = d.depart_id
WHILE d.depart IS NULL;

3.5 depart - emp ∩ depart

与3.4类似

SELECT e.employee_id, e.emp_name, e.depart_id, d.depart_name
FROM emp e RIGHT JOIN depart d
ON e.depart_id = d.depart_id
WHILE e.depart IS NULL;

3.6 满外连接

这里通过把3.1和3.5并起来得到满外连接，也可以使用别的组合。

SELECT e.employee_id, e.emp_name, e.depart_id, d.depart_name
FROM emp e LEFT JOIN depart d
ON e.depart_id = d.depart_id
UNION ALL
SELECT e.employee_id, e.emp_name, e.depart_id, d.depart_name
FROM emp e RIGHT JOIN depart d
ON e.depart_id = d.depart_id
WHILE e.depart_id IS NULL;

3.7 (emp - emp ∩ depart) ∪ (depart - emp ∩ depart)

这里通过求3.4和3.5的并集实现。

SELECT e.employee_id, e.emp_name, e.depart_id, d.depart_name
FROM emp e LEFT JOIN depart d
ON e.depart_id = d.depart_id
WHILE d.depart IS NULL
UNION ALL
SELECT e.employee_id, e.emp_name, e.depart_id, d.depart_name
FROM emp e RIGHT JOIN depart d
ON e.depart_id = d.depart_id
WHILE e.depart_id IS NULL;