表连接：
-
内连接
只要符合连接条件的记录
-
自连接
-
外连接
-
结果集连接
1、内连接
语法参考：
SELECT * FROM table1 JOIN table2 ON join_condition
SELECT * FROM table1 JOIN table2 WHERE join_condition
SELECT * FROM table1, table2 WHERE join_condition
-- 1、查询每个学生的学号、姓名、年龄、课程、老师、成绩
-- 方式1：使用 join on
SELECT t1.stu_no, t1.stu_name, t1.age, t2.score, t3.teacher, t3.course_name
FROM school_student_info t1
JOIN school_student_grade t2 ON t2.student_id = t1.id
-- JOIN school_course_info t3 ON t3.id = t2.course_id
JOIN school_course_info t3 ON t2.course_id = t3.id
-- WHERE t2.course_id = 1
WHERE t3.course_name = '语文'
;
-- 方式2：使用 join where
SELECT t1.stu_no, t1.stu_name, t1.age, t2.score, t3.teacher, t3.course_name
FROM school_student_info t1 JOIN school_student_grade t2
JOIN school_course_info t3
WHERE t1.id = t2.student_id AND t2.course_id = t3.id
AND t1.stu_no = 1001
;
-- 方式3：使用 where
SELECT t1.stu_no, t1.stu_name, t1.age, t2.score, t3.teacher, t3.course_name
FROM school_student_info t1, school_student_grade t2, school_course_info t3
WHERE t1.id = t2.student_id AND t2.course_id = t3.id;
2、自连接
使用场景： 在SELECT语句中多次引用相同的表
-- 设计一个评论及评论回复表，查询某条评论下的直接回复记录
-- 自连接
-- 设计一个评论及评论回复表，查询某条评论下的直接回复记录
SELECT t1.id, t1.content AS '评论', t2.id, t2.content AS '回复'
FROM bbs_comment t1, bbs_comment t2
WHERE t2.parent_id = t1.id
;
3、外连接
要全部的记录（不符合条件的也要）
外连接分为3种：
- 左外连接：LEFT OUTER JOIN
- 右外连接：RIGHT OUTER JOIN
- 全连接：FULL JOIN（MySQL不支持）
-- 外连接
-- 缺失的学生信息和成绩信息
-- 缺考的学生信息
SELECT t1.stu_no, t1.stu_name, t2.score
FROM school_student_info t1
LEFT OUTER JOIN school_student_grade t2 ON t1.id = t2.student_id
WHERE t2.score IS NULL;
-- 缺考了哪一门
SELECT t1.id, t1.stu_no, t1.stu_name, t2.course_id, t2.score
FROM school_student_info t1
LEFT OUTER JOIN school_student_grade t2 ON t1.id = t2.student_id
WHERE t1.stu_no=1037;
SELECT * FROM school_student_grade WHERE student_id = 37;
-- 没有录入的学生信息
SELECT t1.stu_no, t1.stu_name, t2.student_id, t2.course_id, t2.score
FROM school_student_info t1
RIGHT OUTER JOIN school_student_grade t2 ON t2.student_id = t1.id
WHERE t1.stu_no IS NULL;
4、结果集连接
UNION关键字连接多个查询结果
- 要求：每个查询必须包含相同的列、表达式或聚集函数
- 使用UNION ALL可以返回所有匹配的行（不去重）
SELECT * FROM table1
UNION [ALL]
SELECT * FROM table2
-- 结果集连接
-- UNION 的使用
SELECT * FROM school_student_info WHERE id <= 10;
SELECT * FROM school_student_info WHERE id <= 15;
SELECT * FROM school_student_info WHERE id <= 10
UNION ALL
SELECT * FROM school_student_info WHERE id <= 15;
