一张图搞定七种 JOIN 关系

在 mysql 查询语句中,JOIN 扮演的角色很重要,所以掌握其用法很重要。很多同学可能只是会用几种常用的,但要成为高级的工程师是需要掌握透彻,360度全无死角。

图片精华版

文字解释版

1. 需要准备好两个table:subject(学科表)和 student_score(学生成绩表)
通过学生成绩表的subject_id字段(学科ID)和学科表的id字段(主键ID)进行关联

一张图搞定七种JOIN关系

一张图搞定七种JOIN关系

2. 分别填充数据 

一张图搞定七种JOIN关系

一张图搞定七种JOIN关系

3. inner join
语句:select score.student_name,score.score,subject.name,subject.teacher from student_score as score inner join subject on score.subject_id = subject.id;

一张图搞定七种JOIN关系

4. left join (共有+右表不匹配补NULL)
语句:select score.student_name,score.score,subject.name,subject.teacher from student_score as score left join subject on score.subject_id = subject.id;

一张图搞定七种JOIN关系

5. left join (左表独有)
语句:select score.student_name,score.score,subject.name,subject.teacher from student_score as score left join subject on score.subject_id = subject.id where subject.id is null;

一张图搞定七种JOIN关系

6. right join (共有+左表不匹配补NULL)
语句:select score.student_name,score.score,subject.name,subject.teacher from student_score as score right join subject on score.subject_id = subject.id;

一张图搞定七种JOIN关系

7. right join (右表独有)
语句:select score.student_name,score.score,subject.name,subject.teacher from student_score as score right join subject on score.subject_id = subject.id where score.id is null;

一张图搞定七种JOIN关系

8. union (左右表合并并去重)
语句:
select score.student_name,score.score,subject.name,subject.teacher from student_score as score left join subject on score.subject_id = subject.id
union 
select score.student_name,score.score,subject.name,subject.teacher from student_score as score right join subject on score.subject_id = subject.id;

一张图搞定七种JOIN关系

9. union (左右表独有)
语句:
select score.student_name,score.score,subject.name,subject.teacher from student_score as score left join subject on score.subject_id = subject.id where subject.id is null
union
select score.student_name,score.score,subject.name,subject.teacher from student_score as score right join subject on score.subject_id = subject.id where score.id is null;

一张图搞定七种JOIN关系

本作品采用《CC 协议》,转载必须注明作者和本文链接

阿德

本帖由系统于 6个月前 自动加精
zhangdeTalk
讨论数量: 4
惊风破浪

挺好的

4周前 评论

图片上左表独有是inner join还是left join

3周前 评论
zhangdeTalk

@linkgeek 是left join 不好意思,编辑有误,图片已修正

3周前 评论

请勿发布不友善或者负能量的内容。与人为善,比聪明更重要!