SQL中表的连接类型-自连接

举例

题目：查询成绩中存在不及格课程的学生的姓名、学习的所有的课程及成绩信息。

 

按照以前的方法，可以写两个SQL，分两步查询：

1、查出存在不及格课程的学生的学号；

SELECT DISTINCT SNO
FROM   STUDENT
WHERE  MARK<60

2、根据学号查询其姓名、学习的所有课程及成绩。

SELECT SNAME,CNO,MARK
FROM   STUDENT
WHERE  SNO IN ('202201','202202')

 

自连接

而采用自连接，一个SQL即可以实现，如下

SELECT DISTINCT S1.SNAME,S1.CNO,S1.MARK
FROM   STUDENT AS S1,STUDENT AS S2
WHERE  S1.SNO = S2.SNO
AND    S2.MARK<60

补充知识：笛卡尔积  假设集合A={a, b}，集合B={0, 1, 2}，则两个集合的笛卡尔积为{(a, 0), (a, 1), (a, 2), (b, 0), (b, 1), (b, 2)}

 

系统首先执行FROM子句，将STUDENT表S1与S2的笛卡尔积作为中间表。该中间表每一条记录包含两部分信息，直观一点形容，左侧是S1的记录，右侧是S2的记录。而后系统执行WHERE子句，在中间表中搜索S2中分数低于60分的学生的记录。

如图是中间表:

（1）红线左侧是S1的数据，右侧是S2的数据。

（2）黑线框住的5条数据即是S2.MARK<60的数据，因此其对应的S1的数据即是题目要查询的数据。

 

中间表是没有重复记录的，但是查询的结果是S1的部分字段，是有可能重复的，因此需要加上DISTINCT。

注意一下，DISTINCT关键字作用于所有的列，不仅仅是跟在他后面的那一列。

最终查询的结果是：

总结一下自连接：

1、自连接是表与其自身进行连接，需要用到表别名S1,S2；
2、用什么建立连接？SNO。因为一个SNO可以唯一标识一个学生，而查的是学生的所有课程。
3、S2限制条件，S1查询结果。试想，如果S1限制条件，查的也同是S1的字段，那S2用来做什么呢？

 

课后练习

1、查询customer顾客表中与Jim同一公司的顾客；

select c1.cust_name from customer c1,customer c2 where c1.company= c2.company and c2.cust_name='Jim';

连接字段为公司名称；

2、查询series系列表中与A同一组的商品；

select s1.pro from series s1,series s2 where s1.group_id=s2.group_id and s2.pro='A';

 连接字段为组ID；

 

 End.