04mysql查询语句之查询与分页02

1. 所有有门派的人员信息
（ A、B两表共有）
INSERT INTO t_dept(deptName,address) VALUES('华山','华山');
INSERT INTO t_dept(deptName,address) VALUES('丐帮','洛阳');
INSERT INTO t_dept(deptName,address) VALUES('峨眉','峨眉山');
INSERT INTO t_dept(deptName,address) VALUES('武当','武当山');
INSERT INTO t_dept(deptName,address) VALUES('明教','光明顶');
INSERT INTO t_dept(deptName,address) VALUES('少林','少林寺');
INSERT INTO t_emp(NAME,age,deptId,empno) VALUES('风清扬',90,1,100001);
INSERT INTO t_emp(NAME,age,deptId,empno) VALUES('岳不群',50,1,100002);
INSERT INTO t_emp(NAME,age,deptId,empno) VALUES('令狐冲',24,1,100003);
INSERT INTO t_emp(NAME,age,deptId,empno) VALUES('洪七公',70,2,100004);
INSERT INTO t_emp(NAME,age,deptId,empno) VALUES('乔峰',35,2,100005);
INSERT INTO t_emp(NAME,age,deptId,empno) VALUES('灭绝师太',70,3,100006);
INSERT INTO t_emp(NAME,age,deptId,empno) VALUES('周芷若',20,3,100007);
INSERT INTO t_emp(NAME,age,deptId,empno) VALUES('张三丰',100,4,100008);
INSERT INTO t_emp(NAME,age,deptId,empno) VALUES('张无忌',25,5,100009);
INSERT INTO t_emp(NAME,age,deptId,empno) VALUES('韦小宝',18,NULL,100010);

#1.所有有门派的人员信息
SELECT *
FROM t_emp a JOIN t_dept b
ON a.`deptId` = b.`id`
#2.列出所有用户，并显示其机构信息
SELECT *
FROM t_emp a  LEFT JOIN t_dept b
ON a.`deptId` = b.`id`
#3.列出所有门派
SELECT *
FROM t_dept 
#4.所有不入门派的人员
SELECT *
FROM t_emp a LEFT JOIN t_dept b
ON a.`deptId`=b.`id`
WHERE b.`id` IS NULL;
#5.所有没人入的门派
SELECT *
FROM t_dept b LEFT JOIN t_emp a
 ON a.`deptId` = b.`id`
 WHERE a.`deptId` IS NULL;
#6.列出所有人员和机构的对照关系
#(AB全有)

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

#MySQL Full Join的实现 因为MySQL不支持FULL JOIN,下面是替代方法

#left join + union(可去除重复数据)+ right join
#7.列出所有没入派的人员和没人入的门派
（A的独有+B的独有）
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;