博客

一、嵌套查询

嵌套查询我们以mybatis中的嵌套查询举例:

1.1、创建数据库

CREATE TABLE `teacher` (
  `id` INT(10) NOT NULL,
  `name` VARCHAR(30) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8
​
INSERT INTO teacher(`id`, `name`) VALUES (1, '秦老师'); 
​
CREATE TABLE `student` (
  `id` INT(10) NOT NULL,
  `name` VARCHAR(30) DEFAULT NULL,
  `tid` INT(10) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `fktid` (`tid`),
  CONSTRAINT `fktid` FOREIGN KEY (`tid`) REFERENCES `teacher` (`id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8INSERT INTO `student` (`id`, `name`, `tid`) VALUES ('1', '小明', '1'); 
INSERT INTO `student` (`id`, `name`, `tid`) VALUES ('2', '小红', '1'); 
INSERT INTO `student` (`id`, `name`, `tid`) VALUES ('3', '小张', '1'); 
INSERT INTO `student` (`id`, `name`, `tid`) VALUES ('4', '小李', '1'); 
INSERT INTO `student` (`id`, `name`, `tid`) VALUES ('5', '小王', '1');

其中是n个学生对应1个老师

，因此pojo层的学生多了一个teacher属性，

按照查询嵌套处理:

<mapper namespace="com.dao.StudentMapper">
    <!--
        思路:
            1、查询所有的学生信息。
            2、根据查询出来的学生的tid，寻找对应的老师。即子查询。
    -->
    <select id="getStudents" resultMap="StudentTeacher">
        select * from student
    </select>
    
    <resultMap id="StudentTeacher" type="Student">
        <result property="id" column="id"/>
        <result property="name" column="name"/>
        <!--复杂的属性，我们需要单独处理。对象用association,集合用collection-->
        <association property="teacher" column="tid" javaType="Teacher" select="getTeacher"/>
    </resultMap>
    
    <select id="getTeacher" resultType="Teacher">
        select * from teacher where id = #{id}
    </select>
</mapper>

注意：子查询效率低，原因是因为需要查询两次表格。

按照结果嵌套处理（推荐且常用）

<select id="getStudent2" resultMap="StudentTeacher2">
    select s.id sid,s.name sname,t.name tname
    from student s,teacher t
    where s.tid = t.id;
</select>
​
<resultMap id="StudentTeacher2" type="Student">
    <result property="id" column="sid"/>
    <result property="name" column="sname"/>
    <association property="teacher" javaType="Teacher">
        <result property="name" column="tname"/>
    </association>
</resultMap>

嵌套查询和嵌套结果区别：

嵌套查询:

1、嵌套查询是在查询SQL中嵌入一个子查询SQL;

2、嵌套查询会执行多条SQL语句；

3、查询效率低，要多查一轮

嵌套结果：

1、嵌套结果是一个嵌套的多表查询SQL；

2、嵌套结果只会执行一条复杂的SQL语句；

二、连接查询

2.1 内连接

关键字：

inner join……on

说明：组合两个表中的记录，返回关联字段符合查询条件的记录，也就是返回两个表的交集（阴影）部分。

select *
from table1 inner join table2
where table1.id = table2.id

2.2 左外连接

关键字：left outer join……on

说明：左(外)连接，左表(table1)的记录将会全部表示出来，而右表(table2)只会显示符合查询条件的记录。右表记录不足的地方均为NULL。或者：是指以左边的表的数据为基准，去匹配右边的表的数据，如果匹配到就显示，匹配不到就显示为null

SELECT t1.col1, t2.col2
FROM table1 t1 LEFT OUTER JOIN table2 t2 
ON t1.id = t2.id;

2.3 右外连接

和左外连接同理，不赘述

2.4 全外连接

顾名思义，把两张表的字段都查出来，没有对应的值就显示null，但是注意：MySql是没有全外连接的(MySql中没有full outer join关键字)，想要达到全外连接的效果，可以使用union关键字连接左外连接和右外连接。

select e.empName,d.deptName
     FROM t_employee e 
     left JOIN t_dept d
     ON e.dept = d.id
UNION
select e.empName,d.deptName
     FROM t_employee e 
     RIGHT JOIN t_dept d
     ON e.dept = d.id;

UNION求两个结果集的并集，UNION连接的查询也较组合查询或符合查询。UNION会自动去除重复行，如果不喜欢去除重复行可以用UNION ALL。

2.5 自连接

自连接查询就是当前表与自身的连接查询，关键点在于虚拟化出一张表给一个别名

例如：查询员工以及他的上司的名称，由于上司也是员工，所以这里虚拟化出一张上司表

SELECT e.empName,b.empName
     from t_employee e LEFT JOIN t_employee b
     ON e.bossId = b.id;
​

三、嵌套查询和连接查询的区别

查询嵌套是利用子查询来实现多表对应，结果嵌套是利用一句复杂的sql语句来实现多表对应（下列对比以嵌套查询为例）；而连接查询则是先对要查询的表进行笛卡尔积之后，筛选对比。

本人认为存在以下两种情况：

情况一

：

如果只是对应一个连接条件的话，两种查询都是一样的，效率无太多差距。但是相比于嵌套查询，连接查询的学习成本和使用体验会更加好。同时，连接查询可以通过调整主关联表，来先查询数据少的表，提高效率。

情况二:

如果存在多种连接的情况，比如多对一关系，我们可以使用嵌套查询来清晰的写出关系逻辑，对查询进行修改时，也会更加方便。在效率上，由于嵌套结果查询是直接执行一个复杂的sql语句来进行多表的查询，而虽然连接查询也是通过对多个表的查询，但它多出了一个使用笛卡尔积的过程，因此效率不如嵌套查询。

注意

：本文是作者通过查阅和学习网上资料整理，但并没找到真实书本依据，如有错误，本人虚心请教。