LEFT JOIN using a link table in PostgreSQL

Question

There are tables people, people_has_book and books. I want all people in my list but not everybody has a book, so I use a LEFT JOIN to link people to people_has_book, but linking people_has_book to books should not be a LEFT JOIN.

How would I do this?

Well, if you have to make the join between `people_has_book` and `books` an outer join otherwise the hole "join chain" behaves like an inner join. Why do you think you shouldn't do that? — a_horse_with_no_name, Nov 27 '18 at 12:39
`... from people p left join people_has_book phb on phb.people_id = p.id left join books b on phb.book_id = b.id` — a_horse_with_no_name, Nov 27 '18 at 12:44
I tested a simple case and indeed it worked, but I noticed somehow in more extensive cases I got some strange results, but I'm not sure which cases that would be... ;-) — DeniseMeander, Nov 27 '18 at 13:06

Erwin Brandstetter · Accepted Answer · 2018-11-28T18:00:03.507

You can use parentheses to prioritize joins. Like:

SELECT *
FROM   people p
LEFT   JOIN ( people_has_book pb JOIN books b USING (book_id) ) USING (people_id);

This is subtly different from two LEFT JOINs:

SELECT *
FROM   people p
LEFT   JOIN people_has_book pb USING (people_id)
LEFT   JOIN books b USING (book_id);

The latter would show rows from people_has_book even if there is no related entry in books. However, in a classic many-to-many implementation with FK constraints enforcing referential integrity, there is typically no effective difference for your particular query, since all people_has_book.book_id must reference an existing row in books anyway - with the exotic exception of NULL values. (If (people_id, book_id) is the PK of people_has_book, both columns are NOT NULL automatically.)

This is it! Thank you! – DeniseMeander Nov 27 '18 at 13:19 — DeniseMeander, Nov 27 '18 at 13:19

LEFT JOIN using a link table in PostgreSQL

1 Answers1