sql条件控制主表和辅助表

select e.id,e.merchant_id as merchantId,e.head_url as headUrl,e.name,e.work_code as workCode,
e.phone,e.dept_id as deptId,d.dept_name as deptName,d.dept_full_path as deptFullPath,
e.post_name as postName,e.leader_id as leaderId,e.user_id as userId,
if(l.work_code is null, l.`name`, CONCAT(l.`name`, '/', l.work_code)) leader,
e.sex,e.age,e.birthday,e.edu_code as eduCode,e.acc_status as accStatus,
e.ext1,e.ext2,e.ext3,e.ext4,e.ext5,e.ext6,e.ext7,e.ext8,e.ext9,e.ext10,e.ext11,e.ext12,
e.ext13,e.ext14,e.ext15,e.ext16,e.ext17,e.ext18,e.ext19,e.ext20,e.ext21,e.ext22,e.ext23,e.ext24,e.ext25
from oa_emp e
left join oa_emp l on e.leader_id = l.id AND l.`del_flag` = 0
left join oa_dept d on e.dept_id = d.id
where e.del_flag = 0

多表联查，where后面的条件控制整个外层表，on后面的条件只控制辅助表数据，如上，我不需要查询出已经删掉的直接上级，可以在辅助表（查直接上级）后加条件即可