mysql左连接—left join

2022-01-09 17:50:29

原文链接：这里

0.前言

join 是 SQL查询中很常见的一种操作，具体来讲有join，left join, right join，full join等很多形式。这片文章主要说下left join

1.定义与图解

左联结，会将左侧表中的数据全部取出来。不管右侧是否存在能关联上的数据。

首要条件，A表和B表必须有重合的部分。

我们先执行下面SQL语句生成数据。

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), ('冯清贺','男',4001,23), ('杨丽丽','女',5001,21), ('王和和','男',4001,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,' ');

执行完毕后，我们有两个表了。