left join on的条件和where条件

矫情吗；* 2024-03-25 16:55 94阅读 0赞

一次把on改成where的bug。

修改xml文件的sql时候把left join on后面的条件修改了，on改成了where导致查询的数据少了。

原始代码如下：

<select id="getOrders" resultType="*****">
            SELECT
            a.*,
            b.id a_id,
            CASE WHEN b.status is null THEN 1 ELSE b.status END status,
            CASE WHEN b.status = 2 THEN '主管审批' WHEN b.status = 3 THEN '审批完成' WHEN b.status = 4 THEN '审批拒绝' ELSE '待申请' END
            status_name
            FROM
            a
            LEFT JOIN b ON a.id = b.u_id
            and b.counter_code = #{query.counterCode}
            where 1=1
            <if test="query.month != null and query.month != ''">
                and a.month = #{query.month}
            </if>
            order by a.month desc
        </select>

修改后的错误代码如下

<select id="getOrders" resultType="*****">
            SELECT
            a.*,
            b.id a_id,
            CASE WHEN b.status is null THEN 1 ELSE b.status END status,
            CASE WHEN b.status = 2 THEN '主管审批' WHEN b.status = 3 THEN '审批完成' WHEN b.status = 4 THEN '审批拒绝' ELSE '待申请' END
            status_name
            FROM
            a
            LEFT JOIN b ON a.id = b.u_id
            where b.counter_code = #{query.counterCode}
            
            <if test="query.month != null and query.month != ''">
                and a.month = #{query.month}
            </if>
            order by a.month desc
        </select>

把

LEFT JOIN b ON a.id = b.u\_id

and b.counter\_code = \#\{query.counterCode\}

修改成

LEFT JOIN b ON a.id = b.u\_id

where b.counter\_code = \#\{query.counterCode\}

正确的代码中

LEFT JOIN b ON a.id = b.u\_id

and b.counter\_code = \#\{query.counterCode\}

把a表中所有的数据都显示了，即使匹配的时候表格里面的b.counter\_code为空，也都显示出来。

错误的代码中

LEFT JOIN b ON a.id = b.u\_id

where b.counter\_code = \#\{query.counterCode\}

匹配的时候表格里面的b.counter\_code为空则不显示，显示的全是和 \#\{query.counterCode\}匹配的数据，有值的数据。

下面给大家举个例子。

a2表如下

![3e6fe15a1ab9ca7de312f871c21bf97b.png][]

a3表如下

![563ab3013c61797e0be869b7990711ec.png][]

运行on链接条件语句和结果如下

SELECT a2.\*,a3.\* FROM \`a2\` left join a3 on a2.id=a3.id and a3.name='张'

![c502cadec17d9a09780b9560f5f246a9.png][]

运行where条件结果如下

SELECT a2.\*,a3.\* FROM \`a2\` left join a3 on a2.id=a3.id where a3.name='张'

![4d91a4b44b7744464968c02aeca8c7fc.png][]

可以看到一个显示左表所有数据，一个只显示部分数据。

在开发过程中要注意用on还是where

[3e6fe15a1ab9ca7de312f871c21bf97b.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/25/04e1a704032c46f68b81080604cbab03.png
[563ab3013c61797e0be869b7990711ec.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/25/ef22c6de30d14cfd9a91ee5705104c4f.png
[c502cadec17d9a09780b9560f5f246a9.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/25/f87012e48d8c482bb86732c1a3aedc7c.png
[4d91a4b44b7744464968c02aeca8c7fc.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/25/03b295b18b584e39ad616226c84a674f.png