从头开始学MySQL--------内连接、左连接、右连接（4）

7.4.1  内连接

        内连接通过比较运算符在不同的表之间操作，并列出不同的表中连接条件相同的数据行，将其拼接成新的记录。也就是说，只有满足条件的记录才能出现在结果关系中。

DROP TABLE IF EXISTS t_student; CREATE TABLE t_student ( id INT(11) PRIMARY KEY AUTO_INCREMENT, stuName VARCHAR(255) NOT NULL, classId INT(11) NOT NULL ); DROP TABLE IF EXISTS t_teacher; CREATE TABLE t_teacher ( id INT(11) PRIMARY KEY AUTO_INCREMENT, teacName VARCHAR(255) NOT NULL, classId INT(11) NOT NULL ); INSERT INTO t_student(stuName,classId) VALUES('大宇',1),('小大宇',1),('小宇',1); INSERT INTO t_student(stuName,classId) VALUES('小雨',2),('小大雨',2),('小雨',2); INSERT INTO t_student(stuName,classId) VALUES('亚古兽',3),('暴龙兽',3),('纠集暴龙兽',3); INSERT INTO t_teacher(teacName,classId) VALUES('李老师',1); INSERT INTO t_teacher(teacName,classId) VALUES('李老师',2); INSERT INTO t_teacher(teacName,classId) VALUES('王老师',1); INSERT INTO t_teacher(teacName,classId) VALUES('王老师',3); INSERT INTO t_teacher(teacName,classId) VALUES('田老师',2); INSERT INTO t_teacher(teacName,classId) VALUES('田老师',3);

        现有3个班的学生，还有3个老师。每个老师教2个班。

 

        查询李老师教的所有学生。

        第一种写法就是通过 条件WHERE进行连接不同表之间的符合条件的记录。

SELECT t_student.* FROM t_student, t_teacher WHERE t_student.classId = t_teacher.classId AND t_teacher.teacName LIKE '%李%'

 

        第二种写法就是通过内连接查询。通过JOIN关键词进行连接查询。

SELECT t_student.* FROM t_student JOIN t_teacher ON t_student.classId = t_teacher.classId WHERE t_teacher.teacName LIKE '%李%'

 

        两种写法的对比，书上是这么说的：使用WHERE字句定义条件比较简单明了，而使用 INNER JOIN 查询的优势在于不会忘记连接条件。另外，WHERE在某些时候会影响查询的性能。

        如果一个查询中，涉及的两个表都是同一个表，那么这种查询称之为自连接查询。自连接查询是一种特殊的内连接，它指相互连接的表在物理上为同一张表，但在逻辑上可以分为两张表。

        自连接的使用条件是，当某张表里的字段  指向的还是 当前表里的另外一个字段。

        请看下例

DROP TABLE IF EXISTS t_employ; CREATE TABLE t_employ ( id INT (11) PRIMARY KEY AUTO_INCREMENT, NAME VARCHAR (255), bossId INT (11) ); INSERT INTO t_employ (id, NAME, bossId) VALUES (1, '张三', 3), (2, '李四', 3), (3, '王五', 4), (4, '赵六', NULL);

  

        找到每个员工的上司。

        首先看如下SQL，因为上司也是员工表里面的一员，所以需要自连接。

SELECT * FROM t_employ t1 JOIN t_employ t2 ON t1.id = t2.id;

  

   这样就一目了然了，先通过自连接的id属性自己与自己连接，直观上看到自己的上司应该是谁，再通过修改条件找到自己的上司。

SELECT *, t1. NAME 当前员工名, t2. NAME 上司名 FROM t_employ t1 JOIN t_employ t2 ON t1.bossId = t2.id;

  

        从最终的结果来看，因为赵六没有上司，所以这条记录被舍弃了。这也说明了 内连接 的另外一个特点，只查询符合条件的记录，不符合条件的记录不用NULL补充，直接舍弃。

因此可以做出总结：自连接的使用条件是，当某张表里的字段  指向的还是 当前表里的另外一个字段。  比如这个例子里面的bossId指向主键id。

7.4.2  外连接

        如果说内连接是在当前表中进行自己跟自己连接，那么外连接就是在多个表之间进行连接。

        内连接会忽略符合不符合连接条件的记录，但有时候需要包含没有关联的记录中的数据。

LEFT JOIN     左连接：返回包括左表中的所有记录和右表中连接字段相等的记录。

RIGHT JOIN  右连接：返回包括右表中的所有记录和左表中连接字段相等的记录。

（1）左连接

        如果左表的某行在右表中没有匹配行，则在相关联的结果行中，右表的所有列的值均为NULL。

DROP TABLE IF EXISTS t_teacher; CREATE TABLE t_teacher( id INT(11) PRIMARY KEY AUTO_INCREMENT, teacName VARCHAR(255) NOT NULL UNIQUE, age INT(11) ); INSERT INTO t_teacher(teacName,age) VALUES('田教授数据库原理',44),('秀芳姐C语言',31), ('黄金城通信原理',36),('韩院长',55);

 

DROP TABLE IF EXISTS t_student; CREATE TABLE t_student( id INT(11), name VARCHAR(255), teacherId INT(11), CONSTRAINT fk_stu_teac FOREIGN KEY(teacherId) REFERENCES t_teacher(id) ); -- 每个学生选择一个导师，也可以不选，即没有导师 INSERT INTO t_student(id,name,teacherId) VALUES(1,'大宇',4),(2,'小雨',3), (3,'小大宇',2),(4,'学霸',NULL);

 

        有外键关联的表之间是通过从表外键指向主表主键之间形成数据关联关系的。而外连接可以通过相同语义的列进行连接操作，不需要它们一定有外键关联的关系。

        现在的需求是，找到每个学生的导师。

SELECT t_student.name AS stuName, t_teacher.teacName AS teacName FROM t_student LEFT OUTER JOIN t_teacher ON t_student.teacherId = t_teacher.id

        从查询结果来看，也是比较合理的，因为'学霸'没有选择导师，自立成户，但是因为他也是学生，所以逻辑上不能忽略此条记录。右表导师表中的列用NULL补充。

拨云见日

        通过上述SQL的条件可以发现，t_student.teacherId = t_teacher.id 没有指明t_student表中的某条具体的学生记录，那么就意味着需要把学生表中的每条记录都拿到导师表中尝试做一次连接查询。返回的结果是每条学生记录及其对应的导师记录。

        新需求：查询已经选择了导师的学生及其导师、查询还没有选择导师的学生

--查询已经选择了导师的学生及其导师 SELECT t_student.id, t_student.name, t_teacher.teacName FROM t_student JOIN t_teacher ON t_student.teacherId = t_teacher.id

  

--查询还没有选择导师的学生 SELECT * FROM t_student WHERE id NOT IN ( --先查询出已经有导师的学生的ID SELECT t_student.id FROM t_student JOIN t_teacher ON t_student.teacherId = t_teacher.id)

 

        从查询结果来看，即使最外层的SQL查询的是t_student表，但是依旧能够获取到 IN 的嵌套查询到导师表的数据。且IN嵌套的数据只能查询的是ID。若IN（SQL）中的SQL查询了多列数据，则会出现下面的错误提示。

 

 

（2）右连接

        右连接是左连接的反向连接，将返回右表中的所有记录。如果右表的某行在左表中没有匹配行，左表将返回空值。

        右连接的例子莫过于一个导师能够带多个学生了，然后查询某个导师带的学生。

DROP TABLE IF EXISTS t_teacher; CREATE TABLE t_teacher ( id INT(11) PRIMARY KEY AUTO_INCREMENT, name VARCHAR(255) ); INSERT INTO t_teacher(name) VALUES('黄博士'),('韩院长'),('秀芳姐'),('田教授'); DROP TABLE IF EXISTS t_student; CREATE TABLE t_student ( id INT(11) PRIMARY KEY AUTO_INCREMENT, name VARCHAR(255) NOT NULL, teacherId INT(11) NULL, --设置外键关联 CONSTRAINT fk_stu_teac FOREIGN KEY(teacherId) REFERENCES t_teacher(id) ); INSERT INTO t_student(name,teacherId) VALUES('大宇',1),('小宇',1), ('小大宇',2),('学霸',2),('大雨',3),('兵哥',3),('黄大大',3);

  

   

        执行SQL后，效果如上图所示。从现实角度来看，一个导师带多个学生，也有老师是不带学生的，这是符合逻辑的。

        现在的需求是查询出所有老师带的学生。那么，应该把所有老师的记录查询出来。

SELECT t_teacher.id AS teacherId, t_student. NAME AS stuName, t_teacher. NAME AS teacherName FROM t_student --左表 RIGHT OUTER JOIN t_teacher --右表 ON t_student.teacherId = t_teacher.id

  

        从查询结果来看，一个老师可能带多个学生，比如黄博士。也有可能没有带学生，比如田教授。

        因为导师表是右表，所以导师表中的所有记录都被查询出来了，且导师可能出现多次，因为带多个学生。

拨云见日

        原来右查询就是把右表中的纪录一条一条的拿到左表中做连接查询，如果能够按照指定的连接条件连接成功，则返回相关的连接后的记录。如果右表的某条记录按照指定的连接条件，在左表中却没有找到可以连接的记录，那么就用NULL来补充左表的数据。

        一条右表的记录，可能会连接多个左表的记录，那么就分别返回连接后的记录。比如上述结果那样。黄博士在导师表中只有一条记录，但是可以连接多条学生表的记录，那么就分别与学生表做连接，并返回给查询结果。

阅读更多

         目录贴：从头开始学MySQL-------目录帖