全外连接在开发中的运用实例！

 <!-- 查询角色 -->
    <select id="queryRole" parameterType="com.coship.web.uc.dto.RoleParam" resultType="com.coship.web.uc.model.Roles">
		SELECT
		t1.id,t1.NAME,t1.msg,t1.type,t1.creator_id,t1.LEVEL,t1.UPDATE_TIME AS updateTime,t2.user_id
		FROM
		t_role t1 LEFT OUTER JOIN t_user_role t2 ON t1.id=t2.ROLE_ID
		WHERE 1 = 1
		AND t1.creator_id = (
		        SELECT id FROM t_user WHERE LOGIN_ID = #{loginId}
		)
		AND t2.USER_ID !=#{userId}
        <if test="id != null and id !=''">
             and id = #{id}
        </if>
        <if test="name != null and name !=''">
             and name like CONCAT('%', '${name}', '%')
        </if>
        <if test="msg != null and msg !=''">
             and msg like CONCAT('%', '${msg}', '%')
        </if>
        <if test="type != null">
             and type=#{type}
        </if>
        <if test="creator_id != null and creator_id !=''">
             and creator_id=#{creator_id},
        </if>
        <if test="level != null and level !=''">
             and level=#{level}
        </if>
        order by UPDATE_TIME desc
    </select>

结果：