sql - 跨三个表的 LEFT JOIN(带联结表)

Question

在 Postgres 中，有没有办法在联结表链接的表之间执行 左连接，并对链接表进行一些过滤？

比如说，我有两个表，humans 和 pets，我想在我有人类 ID 和宠物名称的地方执行查询。如果人类 ID 存在，但他们没有该名称的宠物，我仍然希望返回人类的行。

如果我有从宠物到人类的FK关系，这会起作用:

select h.*, p.*
from humans as h
left join pets as p on p.human_id = h.id and p.name = 'fluffy'
where h.id = 13

我会得到关于人类 13 的详细信息和 fluffy 的值(value)观的一行。此外，如果人类 13 没有名为“fluffy”的宠物，我会得到一行包含人类 13 的值，而宠物的列为空值。

但是，我没有直接的 FK 关系，我在 humans 和 pets 之间有一个联结表，所以我尝试这样的查询:

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

它返回人类 13 的所有宠物的行，除了 fluffy 的行外都是空列。

如果我将 p.name = 'fluffy' 添加到 WHERE 子句，这将过滤掉所有空行，但也意味着如果人类 13 我得到 0 行根本没有叫 fluffy 的宠物。

有没有办法复制 FK 风格的 left join 的行为，但是当与联结表一起使用时？

Answer 1

一种方法是在 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;