自连接查询是内连接查询的一种,只是将一个表和其自身连接。理解自连接查询,我们先考虑内连接查询。
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打印出来。同理模仿下面的查找。
注意我这里不仅打印了表一的查找到的,还打印出与之对应的表二的信息。运行结果如下: