一种方法是在where
子句中进行比较:
select h.*, p.*
from humans as h left join
humans_pets_junction as j
on j.human_id = h.id left join
pets as p
on j.pet_id = p.id and p.name = 'fluffy'
where h.id = 13 and (p.name = 'fluffy' or p.id is null);
或者,将联结表和pets表作为子查询或CTE连接:
select h.*, p.*
from humans h left join
(select j.*
from humans_pets_junction j join
pets p
on j.pet_id = p.id and p.name = 'fluffy'
) pj
on pj.human_id = h.id
where h.id = 13;