七种JOIN

2020-06-04 约 1374 字预计阅读 3 分钟次阅读

七种JOIN

 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


/* 1 A的独有+共有*/
SELECT <select_list> FROM TableA A LEFT JOIN TableB B ON A.Key = B.Key;

/* 2 共有+B的独有 */
SELECT <select_list> FROM TableA A RIGHT JOIN TableB B ON A.Key = B.Key;

/* 3 共有 */
SELECT <select_list> FROM TableA A INNER JOIN TableB B ON A.Key = B.Key;

/* 4 A的独有 */
SELECT <select_list> FROM TableA A LEFT JOIN TableB B ON A.Key = B.Key WHERE B.Key IS NULL;

/* 5 B的独有 */
SELECT <select_list> FROM TableA A RIGHT JOIN TableB B ON A.Key = B.Key WHERE A.Key IS NULL;

/* 6 A的独有 + B的独有 + 共有，union合并&&自动去重 */
SELECT <select_list> FROM TableA A FULL OUTER JOIN TableB B ON A.Key = B.Key;
/* MySQL不支持FULL OUTER JOIN这种语法 可以改成 1+2 */
SELECT <select_list> FROM TableA A LEFT JOIN TableB B ON A.Key = B.Key
UNION
SELECT <select_list> FROM TableA A RIGHT JOIN TableB B ON A.Key = B.Key;

/* 7 A的独有 + B的独有 */
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;
/* MySQL不支持FULL OUTER JOIN这种语法 可以改成 4+5 */
SELECT <select_list> FROM TableA A LEFT JOIN TableB B ON A.Key = B.Key WHERE B.Key IS NULL;
UNION
SELECT <select_list> FROM TableA A RIGHT JOIN TableB B ON A.Key = B.Key WHERE A.Key IS NULL;

  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
 39
 40
 41
 42
 43
 44
 45
 46
 47
 48
 49
 50
 51
 52
 53
 54
 55
 56
 57
 58
 59
 60
 61
 62
 63
 64
 65
 66
 67
 68
 69
 70
 71
 72
 73
 74
 75
 76
 77
 78
 79
 80
 81
 82
 83
 84
 85
 86
 87
 88
 89
 90
 91
 92
 93
 94
 95
 96
 97
 98
 99
100
101
102
103
104
105
106


# 部门表
CREATE TABLE `t_dept` (
	`id` INT ( 11 ) NOT NULL AUTO_INCREMENT,# id
	`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,# id
	`name` VARCHAR ( 20 ) DEFAULT NULL,# 员工姓名
	`age` INT ( 3 ) DEFAULT NULL,# 年龄
	`deptId` INT ( 11 ) DEFAULT NULL,# 所属部门id
	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 );
	
# 演示
select * from t_emp; # 10条
select * from t_dept; # 6 条

# INNER JOIN 9条
# 1.所有有门派人员的信息（要求显示门派名称）
SELECT e.`name`,d.`deptName` FROM t_emp e INNER JOIN t_dept d ON e.`deptId`=d.`id`;

# LEFT JOIN 10条
# 2. 列出所有人员及其门派信息
SELECT e.`name`,d.`deptName` FROM t_emp e LEFT JOIN t_dept d ON e.`deptId`=d.`id`;


# 3. 列出所有门派
SELECT * FROM t_dept;

# 4. 所有无门派人士
SELECT * FROM t_emp WHERE deptId IS NULL;

# RIGHT JOIN
# 5. 所有无人门派
SELECT d.* FROM t_emp e RIGHT JOIN t_dept d ON d.`id`=e.`deptId` WHERE e.`deptId` IS NULL;

# MySQL不支持FULL OUTER JOIN这种语法 可以改成 2+5
# 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_emp e RIGHT JOIN t_dept d  ON e.`deptId`=d.`id` WHERE e.`deptId` IS NULL;

目录

七种JOIN

七种JOIN