LEFT JOIN across three tables (with junction table)

Question

In Postgres, is there a way to perform a left join between tables linked by a junction table, with some filtering on the linked table?

Say, I have two tables, humans and pets, and I want to perform a query where I have the human ID, and the pet name. If the human ID exists, but they don't have a pet with that name, I still want the human's row to be returned.

If I had a FK relationship from pets to humans, this would work:

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

and I'd get a row with human 13's details, and fluffy's values. In addition, if human 13 didn't have a pet named 'fluffy', I'd get a row with human 13's values, and empty values for the pet's columns.

BUT, I don't have a direct FK relationship, I have a junction table between humans and pets, so I'm trying a query like:

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

Which returns rows for all of human 13's pets, with empty columns except for fluffy's row.

If I add p.name = 'fluffy' to the WHERE clause, that filters out all the empty rows, but also means I get 0 rows if human 13 doesn't have a pet named fluffy at all.

Is there a way to replicate the behavior of the FK-style left join, but when used with a junction table?

Answer 1

One method is to do the comparison in the where clause:

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);

Alternatively, join the junction table and the pets table as a subquery or 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;

Answer 2

In Postgres you can use parentheses to prioritize JOIN order. You do not need a subquery:

SELECT h.*, p.id AS p_id, p.name AS pet_name
FROM   humans  h
LEFT   JOIN (pets p
       JOIN  humans_pets_junction j ON p.name = 'fluffy'
                                   AND j.pet_id = p.id
                                   AND j.human_id = 13) ON TRUE
WHERE  h.id = 13;

Per documentation:

Parentheses can be used around JOIN clauses to control the join order. In the absence of parentheses, JOIN clauses nest left-to-right.

I added the predicate j.human_id = 13 to the join between your junction table and the pets to eliminate irrelevant rows at the earliest opportunity. The outer LEFT JOIN only needs the dummy condition ON TRUE.

SQL Fiddle.

Aside 1: I assume you are aware that you have a textbook implementation of a n:m (many-to-many) relationship?

• How to implement a many-to-many relationship in PostgreSQL?

Aside 2: The unfortunate naming convention in the example makes it necessary to deal out column aliases. Don't use "id" and "name" as column names in your actual tables to avoid such conflicts. Use proper names like "pet_id", "human_id" etc.