mysql,SQL标准,多表查询中内连接，外连接，自然连接等详解之查询结果集的笛卡尔积的演化

intsmaze-刘洋

发布于 2018-08-29 16:56:22

2.5K0

发布于 2018-08-29 16:56:22

先附上数据

CREATE TABLE `course` (
  `cno` int(11) NOT NULL,
  `cname` char(30) CHARACTER SET utf8 NOT NULL,
  `ctime` int(11) NOT NULL,
  `scount` int(11) NOT NULL,
  `ctest` datetime NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
INSERT INTO `course` VALUES ('4', '应用数学基础', '48', '120', '2016-03-10 10:08:29');
INSERT INTO `course` VALUES ('5', '生物工程概论', '32', '80', '2016-03-10 10:09:24');
INSERT INTO `course` VALUES ('1', '计算机软件基础', '32', '70', '2016-03-10 10:09:47');
INSERT INTO `course` VALUES ('2', '计算机硬件基础', '24', '80', '2016-03-10 10:10:28');
INSERT INTO `course` VALUES ('8', '模拟电路设计', '28', '90', '2016-04-06 10:11:02');
INSERT INTO `course` VALUES ('7', '机械设计实践', '48', '68', '2016-03-10 10:11:29');
INSERT INTO `course` VALUES ('3', '生物化学', '32', '40', '2016-03-29 10:11:54');
INSERT INTO `course` VALUES ('9', '数据库设计', '16', '80', '2016-03-10 10:12:14');
INSERT INTO `course` VALUES ('6', '设计理论', '28', '45', '2016-03-10 10:12:33');
INSERT INTO `course` VALUES ('10', '计算机入门', '24', '150', '2016-03-10 10:12:53');
INSERT INTO `course` VALUES ('11', '数字电路设计基础', '30', '125', '2016-03-10 10:13:10');

CREATE TABLE `student` (
  `sno` char(4) CHARACTER SET utf8 DEFAULT NULL,
  `sname` char(10) CHARACTER SET utf8 DEFAULT NULL,
  `dname` char(10) CHARACTER SET utf8 DEFAULT NULL,
  `ssex` char(2) CHARACTER SET utf8 NOT NULL,
  `cno` int(11) NOT NULL,
  `mark` decimal(3,1) NOT NULL,
  `type` char(4) CHARACTER SET utf8 NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
INSERT INTO `student` VALUES ('9701', '刘建国', '管理工程', '男', '4', '82.5', '必修');
INSERT INTO `student` VALUES ('9701', '刘建国', '管理工程', '男', '10', '70.0', '必修');
INSERT INTO `student` VALUES ('9701', '刘建国', '管理工程', '男', '1', '78.5', '选修');
INSERT INTO `student` VALUES ('9702', '李春', '环境工程', '女', '5', '63.0', '必修');
INSERT INTO `student` VALUES ('9702', '李春', '环境工程', '女', '10', '58.0', '选修');
INSERT INTO `student` VALUES ('9703', '王天', '生物', '男', '5', '48.5', '必修');
INSERT INTO `student` VALUES ('9703', '王天', '生物', '男', '2', '86.0', '选修');
INSERT INTO `student` VALUES ('9704', '李华', '计算机', '女', '4', '76.0', '必修');
INSERT INTO `student` VALUES ('9704', '李华', '计算机', '女', '1', '92.0', '必修');
INSERT INTO `student` VALUES ('9704', '李华', '计算机', '女', '2', '89.0', '必修');
INSERT INTO `student` VALUES ('9704', '李华', '计算机', '女', '9', '80.0', '必修');
INSERT INTO `student` VALUES ('9704', '李华', '计算机', '女', '8', '70.0', '选修');
INSERT INTO `student` VALUES ('9705', '孙庆', '电子工程', '男', '8', '79.0', '必修');
INSERT INTO `student` VALUES ('9705', '孙庆', '电子工程', '男', '1', '59.0', '必修');
INSERT INTO `student` VALUES ('9705', '孙庆', '电子工程', '男', '11', '52.0', '必修');
INSERT INTO `student` VALUES ('9705', '孙庆', '电子工程', '男', '6', '68.0', '必修');
INSERT INTO `student` VALUES ('9706', '高伟', '机械工程', '男', '13', '93.0', '必修');
INSERT INTO `student` VALUES ('9706', '高伟', '机械工程', '男', '12', '88.5', '必修');
INSERT INTO `student` VALUES ('9706', '高伟', '机械工程', '男', '1', '78.0', '选修');
INSERT INTO `student` VALUES ('9706', '高伟', '机械工程', '男', '10', '76.0', '选修');

CREATE TABLE `teacher` (
  `tno` int(11) NOT NULL,
  `tname` varchar(10) CHARACTER SET utf8 NOT NULL,
  `cno` int(11) NOT NULL,
  `sal` int(11) DEFAULT NULL,
  `dname` char(10) CHARACTER SET utf8 NOT NULL,
  `tsex` char(2) CHARACTER SET utf8 NOT NULL,
  `age` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

INSERT INTO `teacher` VALUES ('1', '王军', '4', '800', '数学', '男', '32');
INSERT INTO `teacher` VALUES ('2', '李丹', '5', '1200', '生物', '女', '54');
INSERT INTO `teacher` VALUES ('3', '王永军', '1', '900', '计算机', '男', '40');
INSERT INTO `teacher` VALUES ('4', '刘小静', '2', '1200', '计算机', '女', '46');
INSERT INTO `teacher` VALUES ('5', '高伟', '8', '2100', '电子工程', '男', '39');
INSERT INTO `teacher` VALUES ('6', '李伟', '7', '1200', '机械工程', '男', '29');
INSERT INTO `teacher` VALUES ('7', '刘辉', '3', '900', '生物', '女', '46');
INSERT INTO `teacher` VALUES ('8', '刘静', '12', '1300', '经济管理', '女', '28');
INSERT INTO `teacher` VALUES ('9', '李伟', '9', null, '计算机', '女', '43');
INSERT INTO `teacher` VALUES ('10', '刘一凯', '13', null, '计算机', '女', '33');

简单的二表连接

SELECT tname,dname,cname,ctest from teacher,course WHERE teacher.cno=course.cno

该语句的执行过程实例可以表示这样：

a,系统首先执行from子句，这里from子句列出有两个表teacher表和course表，DBMS讲计算这两个表的笛卡尔积，列出这两个表中行的所以可能组合，形成一个中间表。中间表中的每条记录包含了两个表中的所有行。

b,然后系统执行where子句，根据teacher.cno=course.cno关系对中间表进行搜索，去除那些不满足该关系的记录。

c,最后系统执行select语句，从执行where子句后得到的中间表的每条记录中，提取tname,dname,cname,ctest4个字段的信息作为结果表。

需要强调，表的连接所依据的关系是在where子句中定义的。在实际应用中，用户要实现表的连接必然要依据一定的关系。

如果不指明连接关系，即不使用where子句。

SELECT tname,dname,cname,ctest from teacher,course

从结果可以看到，每个教师的信息均与所有课程信息进行了匹配连接。它实际返回连接表中所有数据行的笛卡尔积，其结果集合中的数据行数等于第一个表中符合查询条件的数据行乘以第二个表中符合查询条件的数据行数，即10X11=110条记录。

采用join关键字建立连接

也可以在from子句中，通过连接关键字实现表的连接，这样有助于将连接操作与where的搜索条件区分开来。

SELECT COLUMN from join_table join_type join_table on (join_condition)

join_type为连接类型，可分为4种：自然连接，内连接，外连接和交叉连接。

自连接

自连接是指表与其自身进行连接，这需要使用表别名。

查询成绩中存在不及格课程的学生的姓名，所在系，所有的课程及成绩信息。

SELECT s.sname,s.dname,s.cno,s.mark
from student s
where s.mark<60

无法得到想要结果

SELECT s.sname,s.dname,s.cno,s.mark
from student s
where s.sno in(SELECT DISTINCT s.sno from student s where s.mark<60)

得到想要结果

SELECT DISTINCT s.sname,s.dname,s.cno,s.mark
from student s,student s2
where s.sno=s2.sno
and s2.mark<60

from子句中的两个表实际上都是表student。为了独立地使用它们，采用表别名方法。

SELECT s.sname,s.dname,s.cno,s.mark
from student s,student s2
where s.sno=s2.sno
and s2.mark<60

系统首先执行from子句，将student表S1与它自身S2的笛卡尔积，作为中间表。

实际上，该中间表的每一条记录包含两部分信息，一部分是S1的记录，一部分是S2的记录。而后执行where子句，在中间表中，搜索S2中成绩低于60的学生的记录，同时要求记录中S1与S2是同一个学生的记录即学号相同。最后执行select语句，从中间表获取S1中相应的信息作为结果表。

当执行where子句，从中间表中逐条搜索S2中成绩低于60的学生的记录时，由于孙庆有两门课程不及格，所以对每门不及格的记录都满足搜索条件，因此导致从S1得到的信息中出现了重复的记录。

简单来说，中间表是没有重复记录的，但是S1部分字段是有重复的，而结果集提取的只是S1部分的字段，因此就有可能有重复记录。

一般情况，自连接也可以使用子查询的方式实现。

SELECT DISTINCT s.sname,s.dname,s.cno,s.mark
from student s,student s2
where s.sno=s2.sno
and s.mark<60

自然连接

它将表中具有相同名称的列自动进行记录匹配，自然连接不必指定任何同等连接条件。

自然连接自动判断相同名称的列，而后形成匹配。缺点是，虽然可以指定查询结果包括哪些列，但是不能人为地指定哪些列被匹配。另外，自然连接的一个特点是连接后的结果表中匹配的列只有一个。如上，在自然连接后的表中只有一列C。

从student表和teacher表中查询学生姓名，所在系，所修的本系教师开设的课程的课程号以及开课教师姓名。这时候就采用natural join对两个表进行自然连接。

SELECT sname,dname,cno,tname
from student NATURAL join teacher

等价

SELECT sname,s.dname,s.cno,tname
from student s, teacher t
where s.dname=t.dname
and s.cno=t.cno

事实上，使用基于where子句的等值连接要比使用natural join运算符进行自然连接要灵活的多。

正如前面介绍的，使用natural join运算符自动判断出具有相同名称的列，而后形成匹配，不能人为地指定哪些列被匹配。当自然连接student和teacher表时，CNO和dname列同时被匹配，而不能只匹配一列。

外连接

不管是内连接还是带where子句的多表查询，都组合自多个表，并生成结果表。换句话说，如果任何一个源表中的行在另一个源表中没有匹配，DBMS将把该行放在最后的结果表中。

而外连接告诉ODBC生成的结果表，不仅包含符合条件的行，而且还包含左表（左外连接时），右表（右外连接时）或两个边接表（全外连接）中所有的数据行。

SQL的外连接共有三种类型：左外连接，右外连接，全外连接。

1，左外连接

左外连接，left outer join ,告诉DBMS生成的结果表中，除了包括匹配行外，还包括join关键字（from子句中）左边表的不匹配行。

左外连接实际可以表示为：

左外连接=内连接+左边表中失配的元组。

其中，缺少的右边表中的属性值用null表示。如下：

SELECT s.sno,sname,s.cno,cname,ctest,mark
from student s LEFT JOIN course c
on s.cno=c.cno
ORDER BY sname

右外连接

右外连接，right outer join ,告诉DBMS生成的结果表中，除了包括匹配行外，还包括join关键字（from子句中）右边表的不匹配行。

右外连接实际可以表示为：

右外连接=内连接+右边表中失配的元组。

其中，缺少的左边表中的属性值用null表示。如下：

SELECT s.sno,sname,s.cno,cname,ctest,mark
from student s RIGHT JOIN course c
on s.cno=c.cno
ORDER BY sname

全外连接

全外连接，full outer join，告诉DBMS生成的结果表中，除了包括匹配行外，还包括join关键字（from子句中）左边表和右边表的不匹配行。

可以这样表示：

全外连接=内连接+左边表中失配的元组+右边表中失配的元组

SELECT s.sno,sname,s.cno,cname,ctest,mark
from student s full OUTER JOIN course c
on s.cno=c.cno
ORDER BY sname

本人使用mysql数据库，因为mysql暂时还不支持全外连接full的功能.

一些语句流程顺序，等我有空回顾在写把。等我。勿急躁。

本文参与腾讯云自媒体分享计划，分享自作者个人站点/博客。

原始发表：2016-03-11 ，如有侵权请联系 cloudcommunity@tencent.com 删除

其他

本文分享自作者个人站点/博客前往查看

如有侵权，请联系 cloudcommunity@tencent.com 删除。

本文参与腾讯云自媒体分享计划，欢迎热爱写作的你一起参与！

其他

登录后参与评论

0 条评论

热度