Multiple left joins on multiple tables in one query

by

This kind of query should work – after rewriting with explicit JOIN syntax:

SELECT something
FROM   master      parent
JOIN   master      child ON child.parent_id = parent.id
LEFT   JOIN second parentdata ON parentdata.id = parent.secondary_id
LEFT   JOIN second childdata ON childdata.id = child.secondary_id
WHERE  parent.parent_id = 'rootID';

The tripping wire here is that an explicit JOIN binds before a comma (,), which is otherwise equivalent to CROSS JOIN. The manual here:

In any case JOIN binds more tightly than the commas separating
FROM-list items.

After rewriting the first, all joins are applied left-to-right (logically – Postgres is free to rearrange tables in the query plan otherwise) and it works.

Just to make my point, this would work, too:

SELECT something
FROM   master parent
LEFT   JOIN second parentdata ON parentdata.id = parent.secondary_id
,      master child
LEFT   JOIN second childdata  ON childdata.id = child.secondary_id
WHERE  child.parent_id = parent.id
AND    parent.parent_id = 'rootID';

But explicit JOIN syntax is generally clearer.

And be aware that multiple (LEFT) JOIN can multiply rows:

  • Two SQL LEFT JOINS produce incorrect result

Leave a Comment