SQL JOINS的7种结构(SQL语法以mysql为例)
一、建表语句
部门表:
DROP TABLE IF EXISTS `tbl_dept`;
CREATE TABLE `tbl_dept` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`deptName` varchar(30) DEFAULT NULL,
`locAdd` varchar(40) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of tbl_dept
-- ----------------------------
INSERT INTO `tbl_dept` VALUES ('1', 'RD', '11');
INSERT INTO `tbl_dept` VALUES ('2', 'HR', '12');
INSERT INTO `tbl_dept` VALUES ('3', 'MK', '13');
INSERT INTO `tbl_dept` VALUES ('4', 'MIS', '14');
INSERT INTO `tbl_dept` VALUES ('5', 'FD', '15');
员工表:
DROP TABLE IF EXISTS `tbl_emp`;
CREATE TABLE `tbl_emp` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(30) DEFAULT NULL,
`deptId` int(11) NOT NULL,
PRIMARY KEY (`id`),
KEY `fk_dept_id` (`deptId`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of tbl_emp
-- ----------------------------
INSERT INTO `tbl_emp` VALUES ('1', 'Z3', '1');
INSERT INTO `tbl_emp` VALUES ('2', 'Z4', '1');
INSERT INTO `tbl_emp` VALUES ('3', 'Z5', '1');
INSERT INTO `tbl_emp` VALUES ('4', 'W5', '2');
INSERT INTO `tbl_emp` VALUES ('5', 'W6', '2');
INSERT INTO `tbl_emp` VALUES ('6', 'S7', '3');
INSERT INTO `tbl_emp` VALUES ('7', 'S8', '4');
INSERT INTO `tbl_emp` VALUES ('8', 'S9', '51');
二、情景分析
1.情景一:
左连接:left join
2.情景二:
右连接:right join
3.情景三:
内连接:inner join
4 .情景四
左外连接:left join 且where 右表等于null;
5.情景五
右外连接:right join 且where 左表等于null;
6.情景六
全连接:select * from tbl_emp e full outer join tbl_dept d on e.deptId = d.id;
鉴于mysql不支持full outer join 【full outer join 在oracle支持】
所以,使用union方式,左侧left join ,右侧right join
7.情景七
全外连接:union前右表为null,union 后左表为null