SQLServer 和Mysql左连接，右连接，内连接，全连接

前提

• 创建员工表

CREATE TABLE `user`  (
  `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 = 4 CHARACTER 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;

• 向表中插入数据

insert into `user` VALUES (1,'小王',1),(2,'小李',1),(3,'小刘',2)
insert into `department` VALUES (1,'公司办'),(2,'秘书处'),(3,'开发中心')

内连接

      定义：inner join （等值连接或者叫内连接）：只返回两个表中连接字段相等的行。

select user.id,user.`name`,department.dpname from user inner join department on user.dpid=department.id

       增加两个空记录在测试

执行select user.id,user.name,department.dpname from user inner join department on user.dpid=department.id 语句

左连接

      定义：left join （左连接）：返回包括左表中的所有记录和右表中连接字段相等的记录。

执行以下SQL语句

select * from user;
select * from department;
select user.id,user.`name`,department.dpname from user left join department on user.dpid=department.id

右连接

      定义：right join （右连接）：返回包括右表中的所有记录和左表中连接字段相等的记录。

执行以下SQL语句

select * from user;
select * from department;
select * from user right join department on user.dpid=department.id

————————————————————————————————————————————
左连接右连接内连接，Mysql和SqlServer相同，但是Mysql不支持全连接
————————————————————————————————————————————

全连接

• SqlServer

      定义：full join （全外连接）：返回左右表中所有的记录和左右表中连接字段相等的记录。

执行以下SQL语句

select u.id from user as u FULL JOIN department as d on u.dpid=d.id

-Mysql

      mysql不支持全连接全连接，使用union连接左连接和右连接，得到全连接

select * from user as u right JOIN department as d on u.dpid=d.id
UNION 
select * from user left join department on user.dpid=department.id;

参考链接

左连接 ，右连接，内连接和全外连接的4者区别
Mysql—— 内连接、左连接、右连接以及全连接查询
SQLserver Distinct去重复的数据