MySql学习（一）----------MySql常见的7种JOIN

/**
 * 创建一个 班级表
 */
CREATE TABLE `tbl_class` (
  `id` int(10) NOT NULL AUTO_INCREMENT,
  `name` varchar(20) COLLATE utf8_bin NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

// 插入几条数据
insert into tbl_class(name)
values('一班'),('二班'),('三班'),('四班'),('五班'),('六班');

/**
 * 创建一个 学生表
 */
 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;

stuId	stuName	classId	className
9	狗杂种	null	null