SQL的七种join关系

<select_list>：表示要查询的字段
A.key和B.key：表示有关联的字段
内连接

select <select_list> from tableA A inner join tableB B on A.key = B.key

左连接

select <select_list> from tableA A left join tableB B on A.key = B.key

右连接

select <select_list> from tableA A right join tableB B on A.key = B.key

左外连接

select <select_list> from tableA A left join tableB B on A.key = B.key where B.key is null

右外连接

select <select_list> from tableA A right join tableB B on A.key = B.key where A.key is null

全连接

select <select_list> from tableA A full outer join tableB B on A.key = B.key

外联

select <select_list> from tableA A full outer join tableB B on A.key = B.key where A.key is null or B.key is null

练习题

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`) #CONSTRAINT `fk_dept_id` FOREIGN KEY (`deptId`) REFERENCES `t_dept` (`id`)
) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

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 e.*, d.deptName FROM t_emp e INNER JOIN t_dept d ON e.deptId = d.id
-- 2. 列出所有人员及其门派信息
SELECT e.*,d.deptName,d.address FROM t_emp e LEFT JOIN t_dept d ON e.deptId = d.id
-- 3. 列出所有门派
SELECT * FROM t_dept
-- 4. 所有无门派人士
SELECT e.* FROM t_emp e LEFT JOIN t_dept d ON e.deptId = d.id WHERE e.deptId is NULL
-- 5. 所有无人门派
SELECT d.* FROM t_dept d LEFT JOIN t_emp e ON d.id = e.deptId WHERE e.deptId is NULL
-- 6. 所有人员和门派的对应关系
SELECT * FROM t_emp e LEFT JOIN t_dept d ON e.deptId = d.id
UNION
SELECT * FROM t_emp e RIGHT JOIN t_dept d ON e.deptId = d.id
-- 7. 所有没有入门派的人员和没人入的门派
SELECT * FROM t_emp e LEFT JOIN t_dept d ON e.deptId = d.id WHERE e.deptId is NULL
UNION
SELECT * FROM t_dept d LEFT JOIN t_emp e ON d.id = e.deptId WHERE e.deptId is NULL
-- 8. 添加 CEO 字段
ALTER TABLE `t_dept` add CEO INT(11) ;
update t_dept set CEO=2 where id=1;
update t_dept set CEO=4 where id=2;
update t_dept set CEO=6 where id=3;
update t_dept set CEO=8 where id=4;
update t_dept set CEO=9 where id=5;
-- 8.1 求各个门派对应的掌门人名称
SELECT d.deptName,e.`name` FROM t_dept d LEFT JOIN t_emp e ON d.CEO = e.id
-- 8.2 求所有当上掌门人的平均年龄
SELECT AVG(e.age) FROM t_dept d LEFT JOIN t_emp e ON d.CEO = e.id
-- 8.3 求所有人物对应的掌门名称
SELECT e1.`name` '人物',e2.`name` '掌门' FROM t_emp e1
LEFT JOIN t_dept d on e1.deptId = d.id
LEFT JOIN t_emp e2 on d.CEO = e2.id