/**
* 创建一个 学生表
*/
CREATE TABLE `tbl_student` (
`id` int(10) NOT NULL AUTO_INCREMENT,
`name` varchar(20) COLLATE utf8_bin NOT NULL,
`class_id` int(10) NOT NULL,
PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;// 插入数据
insert into tbl_student(name, class_id)values('乔峰',1),('洪七公',1),('尹志平',2),('赵志敬',2),('梅超风',3),('黄老邪',3),('杨过',4),('小龙女',4),('狗杂种',7);
1.1 inner join A/B 共有的
SELECT
b.id AS stuId,
b.name AS stuName,
a.id AS classId,
a.name AS className
FROM
tbl_class a
INNER JOIN tbl_student b ON a.id = b.class_id;
stuId
stuName
classId
className
1
乔峰
1
一班
2
洪七公
1
一班
3
尹志平
2
二班
4
赵志敬
2
二班
5
梅超风
3
三班
6
黄老邪
3
三班
7
杨过
4
四班
8
小龙女
4
四班
1.2 left join A独有的 + A/B 共有的
SELECT
b.id AS stuId,
b.name AS stuName,
a.id AS classId,
a.name AS className
FROM
tbl_class a
LEFT JOIN tbl_student b ON a.id = b.class_id;
stuId
stuName
classId
className
1
乔峰
1
一班
2
洪七公
1
一班
3
尹志平
2
二班
4
赵志敬
2
二班
5
梅超风
3
三班
6
黄老邪
3
三班
7
杨过
4
四班
8
小龙女
4
四班
null
null
5
五班
null
null
6
六班
五班、六班没有学生,所以在这里,他们是空,是 A独有的
1.3 right join B 独有的 + A/B共有的
SELECT
b.id AS stuId,
b.name AS stuName,
a.id AS classId,
a.name AS className
FROM
tbl_class a
RIGHT JOIN tbl_student b ON a.id = b.class_id;
stuId
stuName
classId
className
1
乔峰
1
一班
2
洪七公
1
一班
3
尹志平
2
二班
4
赵志敬
2
二班
5
梅超风
3
三班
6
黄老邪
3
三班
7
杨过
4
四班
8
小龙女
4
四班
9
狗杂种
null
null
这里 ‘狗杂种’ 的班级不在 tbl_class 表中,所以它是 B独有的
1.4 full outer join 情况一: A + B
SELECT
b.id AS stuId,
b.name AS stuName,
a.id AS classId,
a.name AS className
FROM
tbl_class a
LEFT JOIN tbl_student b ON a.id = b.class_id
UNION
SELECT
b.id AS stuId,
b.name AS stuName,
a.id AS classId,
a.name AS className
FROM
tbl_class a
RIGHT JOIN tbl_student b ON a.id = b.class_id;
stuId
stuName
classId
className
1
乔峰
1
一班
2
洪七公
1
一班
3
尹志平
2
二班
4
赵志敬
2
二班
5
梅超风
3
三班
6
黄老邪
3
三班
7
杨过
4
四班
8
小龙女
4
四班
null
null
5
五班
null
null
6
六班
9
狗杂种
null
null
1.5 full outer join 情况二: A独有的 + B独有的
SELECT
b.id AS stuId,
b.name AS stuName,
a.id AS classId,
a.name AS className
FROM
tbl_class a
LEFT JOIN tbl_student b ON a.id = b.class_id where b.class_id is null
UNION
SELECT
b.id AS stuId,
b.name AS stuName,
a.id AS classId,
a.name AS className
FROM
tbl_class a
RIGHT JOIN tbl_student b ON a.id = b.class_id where a.id is null;
stuId
stuName
classId
className
null
null
5
五班
null
null
6
六班
9
狗杂种
null
null
1.6 left outer join A独有的
SELECT
b.id AS stuId,
b.name AS stuName,
a.id AS classId,
a.name AS className
FROM
tbl_class a
LEFT JOIN tbl_student b ON a.id = b.class_id where b.class_id is null;
stuId
stuName
classId
className
null
null
5
五班
null
null
6
六班
1.7 right outer join B独有的
SELECT
b.id AS stuId,
b.name AS stuName,
a.id AS classId,
a.name AS className
FROM
tbl_class a
RIGHT JOIN tbl_student b ON a.id = b.class_id where a.id is null;