SQL关联查询————LEFT JOIN关键字的使用

引言

关联查询一直是非常重要的SQL使用技巧。

在一次查询操作中，使用mybatis进行条件查询，在没有使用 LEFT JOIN 关键字的情况下是这样写的：

    <!-- 查找成员 -->
    <select id="selectUsers" resultMap="selectUsers_ResultMap">
        SELECT *
        FROM
            sys_user u, sys_user_role ur, sys_role r, sys_dept d
        <where>
            <if test="roleId != null">
                AND ur.user_id = u.user_id
                AND ur.role_id = r.role_id
                AND r.role_id = #{roleId}
            </if>
            <if test="deptId != null">
                AND u.department_id = d.dept_id
                AND u.department_id = #{deptId}
            </if>
            <if test="username != null">
                AND u.username = #{username}
            </if>
        </where>
    </select>

这个查询操作的需求是：根据roleId(角色id)、deptId(部门id)、username(账号)查找用户列表。

虽然可以正常执行，但是查询的结果并不完全正确。

BUG重现

上述查询SQL看似逻辑比较严谨，该关联的都画了等号，但是查询结果是错误的：

首先，进行接口调用，查询所有的 roleId = 12 的用户

真实数据

• role表：role_id = 12

• user_role表：role_id = 12 ——>user_id = 31

• user表：user_id = 31 ——> department_id = 1

•  dept表：dept_id = 1 ——> 研发总监部

数据库user_id = 31的用户department_id = 1 ，即“研发总监部”。

查询结果

上图，是通过swagger API 间接调用的接口，执行的就是引言中的SQL语句，可以看到，虽然 roleId = 12 查询正常，如果不细心可能不会发现这个问题，部门信息为什么会是 deptId = 9 的 “炼丹部” ？

错误原因分析

经过仔细思考，得出结论：

在错误的SQL中，我们的 ID关联条件 都写在了WHERE子句中，且通过动态SQL进行分支执行。

这就导致了：由于只传入了roleId = 12 的条件，而deptId未作为查询条件传入，此时 user.department_id = dept.dept_id 也不会对查询进行外键约束，换言之，这个约束条件可能会被WHERE动态拼接后的SQL所舍弃。因此，导致了查询结果中用户与部门的对应关系与数据库实际的对应关系不一致的情况。

引入LEFT JOIN 

 解决方案

清晰了问题的症结所在，那么如何解决问题呢？

我们的要求是不论WHERE子句中的查询条件有或没有，都要将 id 进行关联。不论是 user.role_id = role.role_id ，还是user.department_id = dept.dept_id 都要在任何查询情况下进行关联，这样就可以得出与数据库对应关系相符的数据。

最终加入LEFT JOIN后的SQL是这样的

<!-- 查找成员 此SQL必须用left join，因为如果将关联条件写在where中，分支将会忽略未执行的关联条件，导致查询结果出错-->
    <select id="selectUsers" resultMap="selectUsers_ResultMap">
	    SELECT *
		FROM (
		    SELECT u.*, ur.role_id
		    FROM sys_user u
		    LEFT JOIN sys_user_role ur
            ON  u.user_id = ur.user_id
		) u_role
		    LEFT JOIN sys_role r ON u_role.role_id = r.role_id
		    LEFT JOIN sys_dept d ON u_role.department_id = d.dept_id
        <where>
            <if test="roleId != null">
                AND r.role_id = #{roleId}
            </if>
            <if test="deptId != null">
                AND u_role.department_id = #{deptId}
            </if>
            <if test="username != null">
                AND u_role.username = #{username}
            </if>
        </where>
    </select>

声明一个问题：为什么会有子查询？

子查询主要是解决 user 表、user_role 表、role 表之间的关联关系，其中user_role 表是一个只存储 user_id 和 role_id 的中间表，这条子查询仅仅适用于 一个用户只拥有一个角色的情况（角色是用户一个分组，本来可以完全不用中间表，但是为了后期扩展为用户-角色 呈多对多的关系，故加入user_role 中间表）。

针对于上述实际的SQL语句来说，这条子查询，仅仅是将 用户所对应的唯一的 role_id 拼接到了user表的末尾，并连同user表的所有数据一同查出。这里其实也可以使用一个LEFT JOIN ，但是由于子查询中的WHERE 子句一定会执行，因此这样写也是可以的。

另外注意：

子查询一定要记得加别名，否则SQL执行会报错！！

子查询一定要记得加别名，否则SQL执行会报错！！

子查询一定要记得加别名，否则SQL执行会报错！！

 修改后测试

同样只传入 roleId = 12 查询全部用户：

可以看到，查出的用户已经与数据库的关联信息保持一致了，其他的成员也都是如此。说明，我们的SQL执行结果符合我们的期望。

复习LEFT JOIN

定义

LEFT JOIN 关键字会从左表 (table_name1) 那里返回所有的行，即使在右表 (table_name2) 中没有匹配的行。

 语法

SELECT column_name(s)
FROM table_name1
LEFT JOIN table_name2 
ON table_name1.column_name=table_name2.column_name 

 

综上，就是对于SQL关联查询的爬坑随笔，比较 隐蔽的一个错误。希望能够对大家有所帮助。欢迎文末留言。