MySQL的七种join(个人总结)

    声明: 1. 本文为我的个人复习总结, 并非那种从零基础开始普及知识 内容详细全面, 言辞官方的文章
              2. 由于是个人总结, 所以用最精简的话语来写文章
              3. 若有错误不当之处, 请指出
七种join:

案例测试:
1. 表结构以及数据准备:

1. teacher表
   
2. student表
   
   通过表可以看出:
        张老师 有学生
        李老师 没学生

   ---

        小明 有老师
        小红 没老师

2. 六个案例需求:

需求1: 查出所有符合常情的老师和学生,(老师有学生,学生有老师), 即内连接

SELECT teacherName, studentName
FROM teacher
JOIN student ON teacher.teacherId = student.teacherId;

需求2: 查出所有老师,顺带查出他们的学生(无论有没有学生都要查出这个老师), 即外连接

SELECT teacherName, studentName
FROM teacher
LEFT JOIN student ON teacher.teacherId = student.teacherId;

SELECT teacherName, studentName
FROM student
RIGHT JOIN teacher ON teacher.teacherId = student.teacherId;

需求3: 查出没有学生的老师

SELECT teacherName, studentName
FROM teacher
LEFT JOIN student ON teacher.teacherId = student.teacherId
WHERE student.teacherId IS NULL;

需求4: 查出没有老师的学生

SELECT teacherName, studentName
FROM teacher
RIGHT JOIN student ON teacher.teacherId = student.teacherId
WHERE student.teacherId IS NULL;

需求5: 查出所有老师和学生, 即全连接

SELECT teacherName, studentName
FROM teacher
LEFT JOIN student ON teacher.teacherId = student.teacherId
UNION
SELECT teacherName, studentName
FROM teacher
RIGHT JOIN student ON teacher.teacherId = student.teacherId
WHERE student.teacherId IS NULL;

需求6: 查出没有老师的学生 和 没有学生的老师

SELECT teacherName, studentName
FROM teacher
LEFT JOIN student ON teacher.teacherId = student.teacherId
WHERE student.teacherId IS NULL
UNION
SELECT teacherName, studentName
FROM teacher
RIGHT JOIN student ON teacher.teacherId = student.teacherId
WHERE student.teacherId IS NULL;