　　从学习MySQL开始join的用法都一直弄地不是很清楚，今天重新梳理一遍。

建表

　　建立老师表（teacher）与学生表（student），一个老师对应多个学生。使用tid做外键。

CREATE TABLE `student` (
  `id` int(3) NOT NULL AUTO_INCREMENT,
  `name` varchar(10) NOT NULL,
  `age` int(3) NOT NULL,
  `tid` int(3) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `tid` (`tid`),
  CONSTRAINT `student_ibfk_1` FOREIGN KEY (`tid`) REFERENCES `teacher` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE `teacher` (
  `id` int(3) NOT NULL AUTO_INCREMENT,
  `name` varchar(3) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

　　插入数据：

SELECT语句顺序

　　首先复习一下SELECT语句的编写顺序：

1	select(distinct) from join on where group by having union order by limit

CROSS JOIN（笛卡尔积）

　　笛卡尔积会将两个表强行凭借，不管两表是否有对应关系。也就是说如果A表有m条记录，B表有n条记录，则执行CROSS JOIN后会查出m*n条记录。

1	SELECT * FROM student (CROSS JOIN) teacher

id  name    age tid id  name(1)
1	张三	18	2	1	李老师
1	张三	18	2	2	张老师
1	张三	18	2	3	王老师
1	张三	18	2	4	魏老师
1	张三	18	2	5	杜老师
1	张三	18	2	6	石老师
2	李四	19	3	1	李老师
2	李四	19	3	2	张老师
2	李四	19	3	3	王老师
2	李四	19	3	4	魏老师
2	李四	19	3	5	杜老师
2	李四	19	3	6	石老师
3	王五	20	1	1	李老师
3	王五	20	1	2	张老师
3	王五	20	1	3	王老师
3	王五	20	1	4	魏老师
3	王五	20	1	5	杜老师
3	王五	20	1	6	石老师
4	小明	17	4	1	李老师
4	小明	17	4	2	张老师
4	小明	17	4	3	王老师
4	小明	17	4	4	魏老师
4	小明	17	4	5	杜老师
4	小明	17	4	6	石老师
5	李力	16		1	李老师
5	李力	16		2	张老师
5	李力	16		3	王老师
5	李力	16		4	魏老师
5	李力	16		5	杜老师
5	李力	16		6	石老师
6	杜四	20		1	李老师
6	杜四	20		2	张老师
6	杜四	20		3	王老师
6	杜四	20		4	魏老师
6	杜四	20		5	杜老师
6	杜四	20		6	石老师

七种JOIN理论

左外连接

　　左外连接：LEFT (OUTER) JOIN，其中OUTER可以省略，是外连接的一种。左外连接会将左边的记录全部显示出来，右表只显示符合条件的记录，右表记录不足
的地方均为空。

1	SELECT * FROM student s LEFT JOIN teacher t ON s.tid = t.id;

　　执行结果：

id  name    age tid id  name(1)
1	张三	18	2	2	张老师
2	李四	19	3	3	王老师
3	王五	20	1	1	李老师
4	小明	17	4	4	魏老师
5	李力	16			
6	杜四	20

右外连接

　　right (outer) join,与左外连接相反，右外连接会将右表所有记录显示出来，左表只显示符合条件的记录。

1	SELECT * FROM student s RIGHT JOIN teacher t ON s.tid = t.id

　　执行结果：

id  name    age tid id  name(1)
3	王五	20	1	1	李老师
1	张三	18	2	2	张老师
2	李四	19	3	3	王老师
4	小明	17	4	4	魏老师
				    5	杜老师
				    6	石老师

左连接

　　左连接即左表独有的部分。需要在左外连接的基础上加where语句实现。

1	SELECT * FROM student s LEFT JOIN teacher t ON s.tid=t.id WHERE t.id IS NULL;

1
2
3

id  name    age tid id  name(1)
5	李力	16			
6	杜四	20

右连接

　　与左连接相反，右连接即为右表独有的部分。需要在右外连接的基础上加上where语句实现。

1	SELECT * FROM student s RIGHT JOIN teacher t ON s.tid=t.id WHERE s.id IS NULL;

1
2
3

id  name    age tid id  name(1)
				    5	杜老师
				    6	石老师

内连接

　　inner join on，返回符合条件的记录，即返回图中交集。

1	SELECT * FROM student s INNER JOIN teacher t ON s.tid = t.id;

id  name    age tid id  name(1)
3	王五	20	1	1	李老师
1	张三	18	2	2	张老师
2	李四	19	3	3	王老师
4	小明	17	4	4	魏老师

全连接

　　MySQL不提供全连接的关键字，但可通过union联合去重的特性模拟，即联合左外连接与右外连接。

1 2	SELECT * FROM student s LEFT JOIN teacher t ON s.tid=t.id UNION SELECT * FROM student s RIGHT JOIN teacher t ON s.tid=t.id;

id  name    age tid id  name(1)
1	张三	18	2	2	张老师
2	李四	19	3	3	王老师
3	王五	20	1	1	李老师
4	小明	17	4	4	魏老师
5	李力	16			
6	杜四	20			
				    5	杜老师
				    6	石老师

差集：

　　差集即两表全连接去除重合部分，可以利用union联合左连接右连接实现。

1
2
3

SELECT * FROM student s LEFT JOIN teacher t ON s.tid=t.id WHERE t.id IS NULL
UNION
SELECT * FROM student s RIGHT JOIN teacher t ON s.tid=t.id WHERE s.id IS NULL;SELECT * FROM student s RIGHT JOIN teacher t ON s.tid=t.id WHERE s.id IS NULL;

id  name    age tid id  name(1)
5	李力	16			
6	杜四	20			
				    5	杜老师
				    6	石老师

参考：

https://www.cnblogs.com/fudashi/p/7491039.html
https://blog.csdn.net/plg17/article/details/78758593

注：文中表格中的空格均表示null