mysql 右连接(right join)

2022/2/1 19:09:42

本文主要是介绍mysql 右连接(right join),对大家解决编程问题具有一定的参考价值,需要的程序猿们随着小编来一起学习吧!

原文链接:这里
0.前言

前面我们已经简单介绍过mysql的左连接left join。这篇文章简单介绍下mysql的右连接right join。

1.简单使用

“右连接”,表1右连接表2,以右为主,表示以表2为主,关联查询表1的数据,查出表2所有数据以及表1和表2有交集的数据,如下:

DROP TABLE IF EXISTS `students`; CREATE TABLE `students` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `stu_name` varchar(20) NOT NULL , `stu_sex` varchar(10) NOT NULL , `class_id` int(10) NOT NULL , `age` int(10) NOT NULL DEFAULT 18, `create_time` datetime NULL DEFAULT CURRENT_TIMESTAMP, `update_time` datetime NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=1000 DEFAULT CHARSET=utf8;       insert into students (stu_name,stu_sex,class_id,age) values ('张三','男',1001,18), ('王晓红','女',2001,20), ('李清乐','男',1001,23), ('赵倚天','男',3001,23), ('赵四','男',1001,22), ('王燕','女',3001,21), ('李西西','女',2001,20), ('高严','男',3001,22);   DROP TABLE IF EXISTS `teacher`; CREATE TABLE `teacher` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `teacher_name` varchar(20) NOT NULL , `teacher_sex` varchar(10) NOT NULL , `class_id` int(10) NOT NULL , `age` int(10) NOT NULL DEFAULT 18, `tel` varchar(11) , `create_time` datetime NULL DEFAULT CURRENT_TIMESTAMP, `update_time` datetime NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=1000 DEFAULT CHARSET=utf8;     insert into teacher (teacher_name,teacher_sex,class_id,age,tel) values ('王老师','男',1001,38,'13011112222'), ('李老师','男',2001,28,'13012344222'), ('赵老师','男',3001,38,'13011112233'), ('孙老师','男',4001,28,'14011112222'), ('周老师','男',5001,18,' ');

表1:

表2:

我们执行右连接:

select * 
from students as s
right join teacher as t
on s.class_id = t.class_id

结果:

可以看出,数据返回的结果是以表2(也就是右表)为主,即时出现空的对于也会正常显示。

2.扩展

如果我们只要最右边部分,则执行下面的语句。

select * 
from students as s
right join teacher as t
on s.class_id = t.class_id
where s.class_id is null


结果:

 



这篇关于mysql 右连接(right join)的文章就介绍到这儿,希望我们推荐的文章对大家有所帮助,也希望大家多多支持为之网!


扫一扫关注最新编程教程