条件从使用左连接的查询中排除行

本文关键字：查询 排除 连接 条件 | 更新日期: 2023-09-27 18:01:45

我有一个语句，我试图使用它来检索员工的完整列表，然后是他们的假期。我想留住员工，即使他们没有假期。这是我目前为止的陈述;

SELECT emp.em_pplid, emp.em_name
FROM employs emp
LEFT JOIN people ppl ON emp.em_pplid = ppl.pp_id
LEFT JOIN empwork ew ON emp.em_pplid = ew.ew_pplid
WHERE emp.em_deptid = ?
AND ppl.pp_employee = ?
AND emp.em_type < ?
AND ew.ew_from > ?
ORDER BY emp.em_name"

删除empwork表中没有假日的员工的是AND ew.ew_from = ?。如何保留employees表中所有具有我想要的部门ID的人员，即使他们在empwork表中没有假期?

条件从使用左连接的查询中排除行

LEFT JOIN的右表的条件只能在ON子句中指定。如果它们不是，则由于NULL比较，连接自动转移到INNER JOIN:

SELECT emp.em_pplid, emp.em_name
FROM employs emp
LEFT JOIN people ppl
 ON emp.em_pplid = ppl.pp_id AND ppl.pp_employee = ?
LEFT JOIN empwork ew 
 ON emp.em_pplid = ew.ew_pplid AND ew.ew_from > ?
WHERE emp.em_deptid = ?
AND emp.em_type < ?
ORDER BY emp.em_name"

您的where子句将您的left join s变为inner join s。将条件放入连接的on子句中。

SELECT emp.em_pplid, emp.em_name
FROM employs emp
LEFT JOIN people ppl ON emp.em_pplid = ppl.pp_id                        
LEFT JOIN empwork ew ON emp.em_pplid = ew.ew_pplid
                    AND ew.ew_from > ?
WHERE emp.em_deptid = ?
AND emp.em_type < ?
AND (ppl.pp_employee IS NULL OR ppl.pp_employee = ?)
ORDER BY emp.em_name

如果我对问题理解得很好，这可能行得通:

SELECT emp.em_pplid, emp.em_name
FROM employs emp
LEFT JOIN people ppl ON emp.em_pplid = ppl.pp_id
LEFT JOIN empwork ew ON emp.em_pplid = ew.ew_pplid
WHERE emp.em_deptid = ?
AND ppl.pp_employee = ?
AND emp.em_type < ?
AND (ew.ew_from > ? OR ew.ew_from IS NULL)
ORDER BY emp.em_name"
相关文章：