SQLServer和Mysql左连接，右连接，内连接，全连接前提
创建员⼯表
CREATE TABLE`ur`(
`id`int(11)NOT NULL AUTO_INCREMENT COMMENT'主键',
`name`varchar(255)CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT'员⼯姓名',
`dpid`int(11)NULL DEFAULT NULL COMMENT'部门id',
PRIMARY KEY(`id`)USING BTREE
)ENGINE=InnoDB AUTO_INCREMENT=4CHARACTER SET= utf8 COLLATE= utf8_general_ci ROW_FORMAT = Compact;
创建部门表
CREATE TABLE`department`(
`id`int(11)NOT NULL COMMENT'主键',
`dpname`varchar(255)CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT'部门名称', PRIMARY KEY(`id`)USING BTREE
)ENGINE=InnoDB CHARACTER SET= utf8 COLLATE= utf8_general_ci ROW_FORMAT = Compact;
向表中插⼊数据
inrt into`ur`VALUES(1,'⼩王',1),(2,'⼩李',1),(3,'⼩刘',2)
inrt into`department`VALUES(1,'公司办'),(2,'秘书处'),(3,'开发中⼼')
内连接
定义：inner join （等值连接或者叫内连接）：只返回两个表中连接字段相等的⾏。
驻村帮扶工作总结
lect ur.id,ur.`name`,department.dpname from ur inner join department on ur.dpid=department.id
空气炸锅是什么
增加两个空记录在测试
执⾏lect ur.id,ur.name,department.dpname from ur inner join department on ur.dpid=department.id 语句
左连接
定义：left join （左连接）：返回包括左表中的所有记录和右表中连接字段相等的记录。执⾏以下SQL语句
lect*from ur;
九年级下册历史
lect*from department;
lect ur.id,ur.`name`,department.dpname from ur left join department on ur.dpid=department.id好歌推荐100首
右连接
定义：right join （右连接）：返回包括右表中的所有记录和左表中连接字段相等的记录。执⾏以下SQL语句
lect*from ur;
lect*from department;
虚无假设lect*from ur right join department on ur.dpid=department.id
—
———————————————————————————————————————————
左连接右连接内连接，Mysql和SqlServer相同，但是Mysql不⽀持全连接————————————————————————————————————————————
全连接
SqlServer
漳州土楼定义：full join （全外连接）：返回左右表中所有的记录和左右表中连接字段相等的记录。执⾏以下SQL语句
lect u.id from ur as u FULL JOIN department as d on u.dpid=d.id
傲拼音-Mysql
mysql不⽀持全连接全连接，使⽤union连接左连接和右连接，得到全连接
lect*from ur as u right JOIN department as d on u.dpid=d.id
高一语文必修二UNION
lect*from ur left join department on ur.dpid=department.id;
参考链接
