多表查询---外连接分析---例题详解
连接分类：
1.内连接：两张表中的相同含义的字段值相等时才符合连接查询的条件2.外连接左外连接：返回左表中所有记录和右表中连接字段相等的记录右外连接：返回右表中所有记录和左表中连接字段相等的记录3.交叉连接
理解：
内连接时，返回查询结果集中的仅是符合查询条件和连接条件的行，但有时候需要包含没有关联的行中数据，即返回查询结果中的不仅包含符合连接条件的行，而且还包含左表(左外连接)、右表（右外连接）中的所有数据行。
分析：
左外连接跟右外连接最重要的是分析好左表跟右表，你在我的左边，就相当于我在你的右边，所以我们只介绍左外连接这一种，右外连接可以根据左外连接去推导
**创建四张表，学生表-老师表-课程表-选课表**CREATE TABLE stu (sid INT(11),sname VARCHAR(60) DEFAULT NULL,sphonum CHAR(11) DEFAULT NULL) ENGINE=INNODB DEFAULT CHARSET=utf8;CREATE TABLE tea (tid INT(11) DEFAULT NULL,tname VARCHAR(60) DEFAULT NULL,tphonum CHAR(11) DEFAULT NULL) ENGINE=INNODB DEFAULT CHARSET=utf8;CREATE TABLE course (cid INT(11) DEFAULT NULL,cname VARCHAR(60) DEFAULT NULL) ENGINE=INNODB DEFAULT CHARSET=utf8;CREATE TABLE xuanke (sid INT(11) DEFAULT NULL,tid INT(11) DEFAULT NULL,cid INT(11) DEFAULT NULL,xuefen INT(11) DEFAULT NULL
) ENGINE=INNODB DEFAULT CHARSET=utf8 ;
**向表中插入数据**INSERT INTO stu1 VALUES(1,'张三','111'),(2,'李四',112);
INSERT INTO tea1 VALUES(1,'张老师',1111),(2,'王老师',1112);INSERT INTO course1 VALUES(1,'linux'),(2,'mysql');INSERT INTO xuanke1 VALUES(1,1111,1,4),(1,1112,2,4);
需求1：查找下所有的学生信息以及他们的选课信息
**分析需求：学生信息在stu表，选课信息在选课表跟course表**SELECT s.*,c.*
FROM stu s-----------左表(最后形成的结果集一定包含左表中所有的数据)
LEFT JOIN xuanke m-----拿着左表中的每一行数据区根据连接条件相对应，满足条件的就输出，不满足条件的就用null值填充
ON m.sid=s.sid
LEFT JOIN course c-----------找到选的课程之后，就拿着课程的编号跟course表中的cid向对应
ON m.cid=c.cid;------+--------+---------+------+-------+
| sid | sname | sphonum | cid | cname |
+------+--------+---------+------+-------+
| 1 | 张三 | 111 | 1 | linux |
| 1 | 张三 | 111 | 2 | mysql |
| 2 | 李四 | 112 | NULL | NULL |
+------+--------+---------+------+-------+**对比：**
SELECT s.*,c.*
FROM stu1 s---------只是将满足m.sid=s.sid条件的输出出来
JOIN xuanke1 m
ON m.sid=s.sid
JOIN course1 c
ON m.cid=c.cid;+------+--------+---------+------+-------+
| sid | sname | sphonum | cid | cname |
+------+--------+---------+------+-------+
| 1 | 张三 | 111 | 1 | linux |
| 1 | 张三 | 111 | 2 | mysql |
+------+--------+---------+------+-------+需求2：查找没有选课的学生的信息(就是在stu表中有信息，在xuanke表中没有信息)
**正确SQL语句**跟需求1作比较----------在形成需求1的大表之后，只是增加了where 过滤条件
SELECT s.*,c.*
FROM stu1 s
LEFT JOIN xuanke1 m
ON m.sid=s.sid
LEFT JOIN course1 c
ON m.cid=c.cid
WHERE m.sid IS NULL----------跟需求1和下一个语句作对比
;**错误SQL语句：**SELECT s.*,c.*
FROM stu1 s
LEFT JOIN xuanke1 m
ON m.sid=s.sid
LEFT JOIN course1 c
ON m.cid=c.cid
AND m.sid IS NULL---------------跟上一条语句作对比
;
+------+--------+---------+------+-------+
| sid | sname | sphonum | cid | cname |
+------+--------+---------+------+-------+
| 1 | 张三 | 111 | NULL | NULL |
| 1 | 张三 | 111 | NULL | NULL |
| 2 | 李四 | 112 | NULL | NULL |
+------+--------+---------+------+-------+
and是在连接条件之前先执行，过滤出m.sid 是空值的行，但是在m表中没有，所以没有的到结果集
然后再去执行连接，拿着左表的每一行数据跟右表去匹配连接条件，现在右表根据过滤条件没有结果集，所以三行数据都是匹配空值扩展：
SELECT s.*,m.*
FROM stu s
LEFT JOIN xuanke m
ON m.sid=s.sid
AND m.cid=1
;sid | sname | sphonum | sid | tid | cid | xuefen |
+------+--------+---------+------+------+------+--------+
| 1 | 张三 | 111 | 1 | 1111 | 1 | 4 |
| 2 | 李四 | 112 | NULL | NULL | NULL | NULL |
+------+--------+---------+------+------+------+--------+解释：需求2解释过and过滤条件是需要先执行的，所以过滤出xuanke表中 1 | 1111 | 1 | 4这一行数据来然后执行连接条件，将stu表中的第一行sid=1取出来去右表选课表中查找sid相等的行，在右表中有两行sid=1的数据但是当时我们过滤and条件是只是过滤出第一行，所以只显示第一行数据以此类推，再拿stu中的第二行数据进行查找SELECT m.*,s.*
FROM xuanke m
LEFT JOIN stu s
ON m.sid=s.sid
AND m.cid=1
;
+------+------+------+--------+------+--------+---------+
| sid | tid | cid | xuefen | sid | sname | sphonum |
+------+------+------+--------+------+--------+---------+
| 1 | 1111 | 1 | 4 | 1 | 张三 | 111 |
| 1 | 1112 | 2 | 4 | NULL | NULL | NULL |
+------+------+------+--------+------+--------+---------+
解释：and条件先执行，过滤出 1 | 1111 | 1 | 这一行数据但是现在xuanke表时作为左表的，就需要拿出第一行数据来去stu表匹配连接条件然后再看xuanke表中的第二行数据，它作为左表中的数据本来也应该提取出来拿到右表进行匹配的但是我们在执行and过滤条件的时候，都没有筛选出来，所以只匹配空值
本文来自互联网用户投稿，文章观点仅代表作者本人，不代表本站立场，不承担相关法律责任。如若转载，请注明出处。 如若内容造成侵权/违法违规/事实不符，请点击【内容举报】进行投诉反馈！