基础-联接方式与集合操作

1.联接方式

通常来讲，联接时由主表（事实表）在左侧，副表（维度表）在右侧

事实表：学生考试成绩表，订单销售表等

维度表：学生信息表，考试信息表，产品信息表等

如果难以区分事实表和维度表，可根据分组字段决定，分组字段所在的为主表，另一表为副表

自连接：员工邻接表（管理者同时也是员工，且每个员工的上级编号就在该员工所在行的字段），左侧作为上级表，右侧作为员工表

1.1.交叉联接


# 笛卡尔积：每个员工都与所有部门组合，假定有n个员工和m个部门，则返回n*m行
SELECT
	dept_name,emp_name
FROM
	employee AS E 
	CROSS JOIN department AS D;# 注意：交叉联接没有联接条件

SELECT
	dept_name,emp_name
FROM
	employee AS E,
	department AS D; # 内部联接可以简写为,形式

1.2.内部联接


# 保留有部门的员工和有员工的部门
SELECT
	dept_name,emp_name
FROM
	employee AS E 
	INNER JOIN department AS D
	ON E.dept_id=D.dept_id;

SELECT
	dept_name,emp_name
FROM
	employee AS E 
	JOIN department AS D # 内部联接可以简写为JOIN形式
	USING(dept_id); # 若联接的字段在两表中相同则可以简写为USING形式

1.3.外部联接


# 保留所有员工和有员工的部门
SELECT
	dept_name,emp_name
FROM
	employee AS E 
	LEFT OUTER JOIN department AS D
	ON E.dept_id=D.dept_id;

SELECT
	dept_name,emp_name
FROM
	employee AS E 
	LEFT JOIN department AS D # 左外联接可以简写为LEFT JOIN形式
	USING(dept_id);# 若联接的字段在两表中相同则可以简写为USING形式

# 保留有部门的员工和所有部门
SELECT
	dept_name,emp_name
FROM
	employee AS E 
	RIGHT OUTER JOIN department AS D
	ON E.dept_id=D.dept_id;

SELECT
	dept_name,emp_name
FROM
	employee AS E RIGHT JOIN department AS D # 右外联接可以简写为RIGHT JOIN形式
	USING(dept_id);# 若联接的字段在两表中相同则可以简写为USING形式

1.4.联接的进阶用法


# 交叉联接+联接条件：内部联接
SELECT
	dept_name,emp_name
FROM
	employee AS E,
	department AS D
WHERE
	E.dept_id=D.dept_id;
# 内部联接+无联接条件：交叉联接
SELECT
	dept_name,emp_name
FROM
	employee AS E 
	JOIN department AS D;
# 在左不在右
SELECT
	dept_name,emp_name
FROM
	employee AS E 
	LEFT JOIN department AS D
	ON E.dept_id=D.dept_id 
WHERE D.dept_id IS NULL;
# 在右不在左
SELECT
	dept_name,emp_name
FROM
	employee AS E 
	RIGHT JOIN department AS D
	ON E.dept_id=D.dept_id 
WHERE E.dept_id IS NULL;
# 完全外部联接：在左不在右和在右不在左
SELECT
	dept_name,emp_name
FROM
	employee AS E 
	LEFT JOIN department AS D
	ON E.dept_id=D.dept_id 
WHERE D.dept_id IS NULL;
UNION
SELECT
	dept_name,emp_name
FROM
	employee AS E 
	RIGHT JOIN department AS D
	ON E.dept_id=D.dept_id 
WHERE E.dept_id IS NULL;

1.5.多表联接


SELECT
	*
FROM
	student_exam_record AS R 
	LEFT JOIN exam_info AS EI
	USING(exam_id)
	LEFT JOIN student_info AS SI
	USING(student_id);
# 多表联接是按顺序的，例如：A,B,C，实际是(A,B),C

2.集合操作

2.1.并集


# 去重
(SELECT
	name,score
FROM student_exam_record_2020)
UNION
(SELECT
	name,score
FROM student_exam_record_2021);
# 不去重
(SELECT
	name,score
FROM student_exam_record_2020)
UNION ALL # UNION默认对合并的表进行去重，若不去重需要使用ALL声明
(SELECT
	name,score
FROM student_exam_record_2021);

# 并集后的排序1
(SELECT
	name,score
FROM student_exam_record_2020)
UNION ALL
(SELECT
	name,score
FROM student_exam_record_2021)
ORDER BY name ASC;
# 并集后的排序2
((SELECT
	name,score
FROM student_exam_record_2020)
UNION ALL
(SELECT
	name,score
FROM student_exam_record_2021))
ORDER BY name ASC;

# 并集前的排序1：错误排序，并集之前进行单独排序是没有意义的，UNION仍会按原表排序
(SELECT
	name,score
FROM student_exam_record_2020
ORDER BY name ASC)
UNION ALL
(SELECT
	name,score
FROM student_exam_record_2021
ORDER BY name ASC);
# 并集前的排序2：正确排序
SELECT
	2020 AS tag, # 并集后的列名以并集前首个查询的列名为准
	name,score
FROM student_exam_record_2020
UNION ALL
SELECT
	2021,
	name,score
FROM student_exam_record_2021
ORDER BY tag ASC,name ASC;

# 并集后的分页
(SELECT
	name,score
FROM student_exam_record_2020)
UNION ALL
(SELECT
	name,score
FROM student_exam_record_2021)
LIMIT 5;
# 并集前的分页
(SELECT
	name,score
FROM student_exam_record_2020
LIMIT 5)
UNION ALL
(SELECT
	name,score
FROM student_exam_record_2021
LIMIT 5);

# 并集前同时使用排序和分页的结果等其他操作请参考官方文档