关联查询的七种结果

测试数据

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
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(11) 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(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,'风清扬',90,1,100001);
INSERT INTO t_emp(id,NAME,age,deptId,empno) VALUES(2,'岳不群',50,1,100002);
INSERT INTO t_emp(id,NAME,age,deptId,empno) VALUES(3,'令狐冲',24,1,100003);

INSERT INTO t_emp(id,NAME,age,deptId,empno) VALUES(4,'洪七公',70,2,100004);
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,'周芷若',20,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);

1、内连接：INNER JOIN

1
2
3
4
5
6
7
8
9
10
select 字段列表
from A表 inner join B表
on 关联条件
where 等其他子句;

或

select 字段列表
from A表 , B表
where 关联条件 and 等其他子句;

代码示例：

1
2
3
4
5
6
7
8
9
10
11
#查询所有有帮派的员工
#员工的姓名在t_emp
#帮派的名称在t_dept
SELECT t_emp.name"姓名",age"年龄",t_dept.deptName"帮派"
FROM t_emp INNER JOIN t_dept
ON t_emp.deptId = t_dept.id;

#查询年龄为25岁的
SELECT t_emp.name"姓名",age"年龄",t_dept.deptName"帮派"
FROM t_emp,t_dept
WHERE t_emp.deptId = t_dept.id AND age = 25;

韦小宝因为没有帮派，消失了。。。

2、左外连接：LEFT JOIN

1
2
3
4
5
6
7
8
9
10
11
#实现查询结果是A
select 字段列表
from A表 left join B表
on 关联条件
where 等其他子句;

#实现A -  A∩B
select 字段列表
from A表 left join B表
on 关联条件
where 从表关联字段 is null and 等其他子句;

代码示例：

1
2
3
4
5
6
7
8
9
10
11
#查询所有员工的姓名和他所在的帮派的名称
SELECT t_emp.id,name,age,empno,t_dept.deptName,address
FROM t_emp LEFT JOIN t_dept
ON t_emp.deptId = t_dept.id;


#查询所有没有帮派的员工
SELECT t_emp.id,name,age,empno, t_dept.deptName,address
FROM t_emp LEFT JOIN t_dept
ON t_emp.deptId = t_dept.id
WHERE t_dept.address IS NULL;

韦小宝被查出来了。。。

3、右外连接：RIGHT JOIN

1
2
3
4
5
6
7
8
9
10
11
#实现查询结果是B
select 字段列表
from A表 right join B表
on 关联条件
where 等其他子句;

#实现B -  A∩B
select 字段列表
from A表 right join B表
on 关联条件
where 从表关联字段 is null and 等其他子句;

代码示例：

1
2
3
4
5
6
7
8
9
10
#查询所有帮派，以及所有帮派下的员工信息
SELECT t_emp.id,name,age, t_dept.deptName,address
FROM t_emp RIGHT JOIN t_dept
ON t_emp.deptId = t_dept.id;

#查询哪些帮派是没有员工
SELECT t_dept.deptName
FROM t_emp RIGHT JOIN t_dept
ON t_emp.deptId = t_dept.id
WHERE t_emp.name IS NULL;

少林。。。

4、用 union 代替全外连接

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
#实现查询结果是A∪B
#用左外的A，union 右外的B
select 字段列表
from A表 left join B表
on 关联条件
where 等其他子句
union
select 字段列表
from A表 right join B表
on 关联条件
where 等其他子句;



#实现A∪B -  A∩B  或   (A -  A∩B) ∪ （B - A∩B）
#使用左外的 (A -  A∩B)  union 右外的（B - A∩B）
select 字段列表
from A表 left join B表
on 关联条件
where 从表关联字段 is null and 等其他子句

union

select 字段列表
from A表 right join B表
on 关联条件
where 从表关联字段 is null and 等其他子句

代码示例：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
#查询所有员工，所有帮派，包括没有员工的帮派，和没有帮派的员工
SELECT t_emp.id,name, t_dept.deptName
FROM t_emp LEFT JOIN t_dept
ON t_emp.deptId = t_dept.id
UNION
SELECT t_emp.id,name, t_dept.deptName
FROM t_emp LEFT JOIN t_dept
ON t_emp.deptId = t_dept.id;

#查询那些没有帮派的员工和所有没有员工的帮派
#没有帮派的员工
SELECT t_emp.id,name, t_dept.deptName
FROM t_emp LEFT JOIN t_dept
ON t_emp.deptId = t_dept.id
WHERE t_dept.deptName IS NULL

UNION

#所有没有员工的帮派
SELECT t_emp.id,name, t_dept.deptName
FROM t_emp RIGHT JOIN t_dept
ON t_emp.deptId = t_dept.id
WHERE t_emp.name IS NULL;

没有帮派的员工和所有没有员工的帮派

上面四种连接就包括了关联查询的七种结果，还有一种特殊的关联查询。

增加测试数据

增加掌门人字段

1
2
3
4
5
6
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;

临时表关联查询

代码示例：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
#查询所有员工对应的掌门
#临时表连接方式1---员工和CEO关系
SELECT t1.name"员工",t_emp.name"掌门"
FROM

#临时表t1：员工姓名对应的掌门id
(SELECT t_emp.name,t_dept.CEO
FROM t_emp
LEFT JOIN
t_dept
ON
t_emp.deptId = t_dept.id) AS t1

LEFT JOIN
t_emp
ON
t1.CEO=t_emp.id;

#表的别名不要加""，给列取别名，可以用""，列的别名也可以使用AS

通过临时表连接方式 1—人和 CEO 关系

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
#查询所有员工对应的掌门
#临时表连接方式2---部门和ceo关系
SELECT t_emp.name "员工",t1.name"掌门"
FROM

#临时表t1：部门id与掌门对应关系
(SELECT t_dept.id,t_emp.name
FROM t_emp
INNER JOIN t_dept
ON t_emp.id = t_dept.ceo) AS t1

INNER JOIN
t_emp
ON
t_emp.deptId = t1.id;

通过临时表连接方式 2—部门和 ceo 关系

1
2
3
4
5
6
7
8
#查询所有员工对应的掌门
#三表左连接方式
SELECT
  t1.name"员工",t2.name"掌门"
FROM
  t_emp AS t1
  LEFT JOIN t_dept ON t1.deptId = t_dept.id
  LEFT JOIN t_emp AS t2 ON t2.id = t_dept.CEO;

三表左连接方式

1
2
3
4
5
#子查询方式
SELECT t_emp.name "员工",(SELECT t_emp.name FROM t_emp WHERE t_emp.id=t_dept.ceo) "掌门"
FROM t_emp
LEFT JOIN t_dept
ON t_emp.deptid = t_dept.id ;

子查询方式