如何理解MySQL自连接查询

自连接查询是内连接查询的一种，只是将一个表和其自身连接。理解自连接查询，我们先考虑内连接查询。

1.内连接查询

内连接查询语法：

   select <字段名1，字段名2，....字段名n> from <表名1>inner join <表名2> [on 子句]

在FROM子句中使用关键字INNER JOIN连接两张表，并用ON子句来设置连接条件。

上实例：假设有两张表，表一为学生信息表，名称为tb_student_info。我们关注的是dept_id(学院编号)。

 表二是学院信息表（tb_departments），有学院编号和学院名称。

执行下列代码：

select id,name,age,dept_name

from tb_student_info inner join tb_departments

on tb_student_info.dept_id = tb_departments.dept_id;

结果为：

         

没有id = 7的学生信息，因为我们查找条件是 tb_student_info.dept_id = tb_departments.dept_id;

id = 7 的学生学院编号是7，然后去第二张表里去找，找不到，自然就输出不来，是整个要输出的信息（id,name,age,dept_name）都输出不来。如果是左外连接查询，则能输出id = 7的学生（基表信息），但输不出来学院名称（表二相当于参考表）。如果是右外连接查询，则能输出学院名称（此时学院表相当于基表），但是输出不来学生（学生信息表相当于参考表）。如下图所示：

-- 左外连接查询

select name,dept_name

from tb_student_info as s

left join test_tb.tb_departments as d on s.dept_id = d.dept_id;

-- 右外连接查询

select name,dept_name

from tb_student_info right join test_tb.tb_departments td 

on tb_student_info.dept_id = td.dept_id;

 

 2.自连接查询

用途：如果需要在一个表中查找具有相同列值的行（记录），则可以考虑使用自连接。

自连接查询：两个表都是自己，注意要改两个表的名字。

要求：查找id为1的学生所在学院的其他学生信息？

​
select s1.id,s1.name,s2.id,s2.name

from tb_student_info as s1 inner join tb_student_info as s2

on s1.dept_id = s2.dept_id and s2.id = 1;

​

将表一改名为s1，表二改名为s2。查找条件是s1.dept_id = s2.dept_id 且 s2.id = 1;

模仿一下执行过程，比如第一个是Dany，在第二个表中找和他dept_id相等的，显然找到三个，分别是Dany，Jane和Jim，然后判断有没有表二的id还是等于一的，一看还真有，于是我们把表一的Dany打印出来。同理模仿下面的查找。

注意我这里不仅打印了表一的查找到的，还打印出与之对应的表二的信息。运行结果如下：