mysql常见join

mysql常见7种join

CREATE TABLE `t_dept` (  `id` INT(11) NOT NULL AUTO_INCREMENT,  `deptName` VARCHAR(30) DEFAULT NULL,  `address` VARCHAR(40) DEFAULT NULL,  PRIMARY KEY (`id`) ) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;   CREATE TABLE `t_emp` (  `id` INT(11) NOT NULL AUTO_INCREMENT,  `name` VARCHAR(20) DEFAULT NULL,   `age` INT(3) DEFAULT NULL,  `deptId` INT(11) DEFAULT NULL, empno int  not null,  PRIMARY KEY (`id`),  KEY `idx_dept_id` (`deptId`) ) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;   INSERT INTO `t_dept` (`id`, `deptName`, `address`) VALUES ('1', '华山', '华山'); INSERT INTO `t_dept` (`id`, `deptName`, `address`) VALUES ('2', '丐帮', '洛阳'); INSERT INTO `t_dept` (`id`, `deptName`, `address`) VALUES ('3', '峨眉', '峨眉山'); INSERT INTO `t_dept` (`id`, `deptName`, `address`) VALUES ('4', '武当', '武当山'); INSERT INTO `t_dept` (`id`, `deptName`, `address`) VALUES ('5', '明教', '光明顶'); INSERT INTO `t_dept` (`id`, `deptName`, `address`) VALUES ('6', '少林', '少林寺');     INSERT INTO `t_emp` (`id`, `name`, `age`, `deptId`, `empno`) VALUES ('1', '岳不群', '50', '1', '100002'); INSERT INTO `t_emp` (`id`, `name`, `age`, `deptId`, `empno`) VALUES ('2', '令狐冲', '24', '1', '100003'); INSERT INTO `t_emp` (`id`, `name`, `age`, `deptId`, `empno`) VALUES ('3', '洪七公', '76', '2', '100004'); INSERT INTO `t_emp` (`id`, `name`, `age`, `deptId`, `empno`) VALUES ('4', '苏乞儿', '22', '2', '100001'); INSERT INTO `t_emp` (`id`, `name`, `age`, `deptId`, `empno`) VALUES ('5', '乔峰', '35', '2', '100005'); INSERT INTO `t_emp` (`id`, `name`, `age`, `deptId`, `empno`) VALUES ('6', '灭绝师太', '70', '3', '100006'); INSERT INTO `t_emp` (`id`, `name`, `age`, `deptId`, `empno`) VALUES ('7', '周芷若', '18', '3', '100007'); INSERT INTO `t_emp` (`id`, `name`, `age`, `deptId`, `empno`) VALUES ('8', '张三丰', '100', '4', '100008'); INSERT INTO `t_emp` (`id`, `name`, `age`, `deptId`, `empno`) VALUES ('9', '张无忌', '25', '5', '100009'); INSERT INTO `t_emp` (`id`, `name`, `age`, `deptId`, `empno`) VALUES ('10', '韦小宝', '18', NULL, '100010');

 上图解释如下SQL
1   所有有门派的人员信息 
（ A、B两表共有 图中3）
 select * from t_emp a inner join t_dept b on a.deptId = b.id; 
 
2   列出所有用户，并显示其机构信息 
 （A的全集 图中1）
 select * from t_emp a left join t_dept b on a.deptId = b.id; 
 
3   列出所有门派 
（B的全集 图中2）
 select * from  t_dept  b  
 
4   所有不入门派的人员 
（A的独有 图中1a）
select * from t_emp a left join t_dept b on a.deptId = b.id where b.id is null; 
 
5   所有没人入的门派 
（B的独有 图中2a）
 select * from t_dept b left join  t_emp a on a.deptId = b.id where a.deptId is null;  
 
6  列出所有人员和机构的对照关系
(AB全有  图中4)
#MySQL Full Join的实现 因为MySQL不支持FULL JOIN,下面是替代方法
 #left join + union(可去除重复数据)+ right join
 
SELECT * FROM t_emp A LEFT JOIN t_dept B ON A.deptId = B.id
UNION
SELECT * FROM t_emp A RIGHT JOIN t_dept B ON A.deptId = B.id
 
7 列出所有没入派的人员和没人入的门派
（A的独有+B的独有 图中4a）
SELECT * FROM t_emp A LEFT JOIN t_dept B ON A.deptId = B.id WHERE B.`id` IS NULL
UNION
SELECT * FROM t_emp A RIGHT JOIN t_dept B ON A.deptId = B.id WHERE A.`deptId` IS NULL;