Unnecessary inner join for MenuMapper.getMenusByRoleIds

[wtune]

The following query defined in MenuMapper.xml is not optimal:

    <select id="getMenusByRoleIds" resultType="cn.stylefeng.guns.base.pojo.node.MenuNode">
        SELECT
        m1.menu_id AS id,
        m1.code AS code,
        m1.icon AS icon,
        (
        CASE
        WHEN (m2.menu_id = 0 OR m2.menu_id IS NULL) THEN
        0
        ELSE
        m2.menu_id
        END
        ) AS "parentId",
        m1.name as name,
        m1.url as url,
        m1.levels as levels,
        m1.menu_flag as ismenu,
        m1.system_type as systemType,
        m1.sort as num
        FROM
        sys_menu m1
        LEFT join sys_menu m2 ON m1.pcode = m2.code
        INNER JOIN (
        SELECT
        menu_id
        FROM
        sys_menu
        WHERE
        menu_id IN (
        SELECT
        menu_id
        FROM
        sys_relation rela
        WHERE
        rela.role_id IN
        <foreach collection="list" index="index" item="i" open="(" separator="," close=")">
            #{i}
        </foreach>
        )
        ) m3 ON m1.menu_id = m3.menu_id
        where m1.menu_flag = 'Y'
        order by levels,m1.sort asc
    </select>

The INNER JOIN for m3 is unnecessary and could be optimized out to the following:

    <select id="getMenusByRoleIds" resultType="cn.stylefeng.guns.base.pojo.node.MenuNode">
        SELECT
        m1.menu_id AS id,
        m1.code AS code,
        m1.icon AS icon,
        (
        CASE
        WHEN (m2.menu_id = 0 OR m2.menu_id IS NULL) THEN
        0
        ELSE
        m2.menu_id
        END
        ) AS "parentId",
        m1.name as name,
        m1.url as url,
        m1.levels as levels,
        m1.menu_flag as ismenu,
        m1.system_type as systemType,
        m1.sort as num
        FROM
        sys_menu m1
        LEFT join sys_menu m2 ON m1.pcode = m2.code
        where m1.menu_flag = 'Y' and
        m1.menu_id IN (
            SELECT menu_id FROM sys_relation rela WHERE rela.role_id IN
                <foreach collection="list" index="index" item="i" open="(" separator="," close=")">
                    #{i}
                </foreach>
        )
        order by levels,m1.sort asc
    </select>