6、联表查询、自连接 、子查询

联表查询

参考：https://www.runoob.com/w3cnote/sql-join-image-explain.html

MySQL中没有OUTER JOIN，因此以上模式都是通过RIGHT JOIN、INNER JOIN、LEFT JOIN实现。上图中6通过1 union 4实现，7通过2 union 5实现

操作	描述
INNER JOIN	将左右表中符合ON条件的行进行拼接
LEFT JOIN	返回左表中所有的行，右表中有符合ON条件的就填充上，不符合的的就填null
RIGHT JOIN	返回右表中所有的值，左表中有符合ON条件的就填充上，不符合的的就填null

注：1、FROM+左表，JOIN+右表 2、一般将两表中共同的字段作为拼接点进行ON条件判断，当然也可以不是共同字段 3、拼接后将会有左表的拼接字段和右表的拼接字段，如果要返回拼接点字段，那么要具体指定是左表的还是右表的，不然会冲突，具体可见下面的例子

LEFT JOIN：

SELECT <select_list>
FROM Table_A A
LEFT JOIN Table_B B
ON A.Key = B.Key

RIGHT JOIN：

SELECT <select_list>
FROM Table_A A
RIGHT JOIN Table_B B
ON A.Key = B.Key

INNER JOIN：

SELECT <select_list>
FROM Table_A A
INNER JOIN Table_B B
ON A.Key = B.Key

=======================================举例： ==================================

grades表有studentno、studentresult两个字段，stu表有studentno、name两个字段

两者的连接点在于studentno字段，因此将此作为ON条件

INNER JOIN举例：

下面这段代码返回所有拼接后的字段，可以看出studentno一共有两个，分别对应着两个表

SELECT *
FROM `stu` AS s
INNER JOIN `grades` AS r
ON s.studentno = r.studentno;

下面这段代码在返回studentno时一定要指定是哪个表的studentno，不然会有歧义

SELECT s.studentno, `name`, studentresult
FROM `stu` AS s
INNER JOIN `grades` AS r
ON s.`studentno` = r.studentno;

LEFT JOIN举例：

SELECT *
FROM `stu` AS s
LEFT JOIN `grades` AS r
ON s.studentno = r.studentno;

下面这段代码，返回不同表的studentno字段，对应不同的结果

SELECT s.studentno, `name`, studentresult
FROM `stu` AS s
LEFT JOIN `grades` AS r
ON s.`studentno` = r.studentno;

SELECT r.studentno, `name`, studentresult
FROM `stu` AS s
LEFT JOIN `grades` AS r
ON s.`studentno` = r.studentno;

RIGHT JOIN举例：
上面已经很详细了，这里只展示一下返回所有字段

自连接

自己和自己连接，相当于将一张表复制一份，然后对两张表分别查询并进行拼接。一般用来查询父子关系

举例：

这是一个学科表，表名为subject，表共有三个字段。学科id表示这个学科的id，父学科id表示这个学科的上一级学科id，就好比软件开发包括数据库和web开发，学科名表示这个学科的名字。现在我们想查询父子信息

SELECT a.`学科名` AS `父类`, b.`学科名` AS `子类`
FROM `subject` AS a, `subject` AS b
WHERE a.`学科id` = b.`父学科id`;

子查询

本质：在WHERE语句中嵌套一个查询语句

搭配联表查询举例：

student内有学生姓名和学生id两个字段，result内有学生id、学科id和学科成绩三个字段，subject
内有学科id和学科名两个字段

现在我们需要查询 高等数学科目分数大于80分的学生i和学生姓名

student

result

subject

第一种：两次联表查询

SELECT s.`studentid` ,`studentname`
FROM `student` s
INNER JOIN `result` r
ON s.`studentid` = r.`studentid`
INNER JOIN `subject` sub
ON r.`subjectid` = sub.`subjectid`
WHERE `grade` >= 80 AND `subjectname` = '高等数学' 

第二种：联表+子查询

SELECT s.`studentid`, `studentname`
FROM `student` s
INNER JOIN `result` r
ON s.`studentid` = r.`studentid`
WHERE `grade` >= 80 AND `subjectid` IN (
  SELECT subjectid FROM `subject` WHERE `subjectname` = '高等数学'
)

第三种：嵌套的子查询

SELECT `studentid`  ,`studentname`
FROM `student`
WHERE `studentid` IN (
  SELECT `studentid` FROM `result` WHERE `grade` >= 80 AND `subjectid` IN (
    SELECT `subjectid` FROM `subject` WHERE `subjectname` = '高等数学'  
  )
)