Do PostgreSQL LATERAL joins require or allow an ON clause?

Question

I don't often see the ON clause used with the LATERAL joins (PostgreSQL 11+). For example, the official documentation has this example:

A trivial example of LATERAL is

SELECT * FROM foo, LATERAL (SELECT * FROM bar WHERE bar.id = foo.bar_id) ss;

This is not especially useful since it has exactly the same result as the more conventional

SELECT * FROM foo, bar WHERE bar.id = foo.bar_id;

From the example, the equivalent conventional join has an ON clause (written using WHERE, WHERE bar.id = foo.bar_id), but for the LATERAL JOIN, the join condition seems to be "internalized".

I feel that this example is not alone. I see many lateral join usages without an ON clause, but haven't seen much use with an ON clause. Conceptually, it's not clear whether an ON clause is necessary for a lateral join since each set of dependent values is only joined to the rows that they depended on. Using the next example from the same documentation to illustrate:

For example, supposing that vertices(polygon) returns the set of vertices of a polygon, we could identify close-together vertices of polygons stored in a table with:

SELECT p1.id, p2.id, v1, v2
FROM polygons p1, polygons p2,
     LATERAL vertices(p1.poly) v1,
     LATERAL vertices(p2.poly) v2
WHERE (v1 <-> v2) < 10 AND p1.id != p2.id;

Here, the vertex set generated for each polygon p1.poly is only associated with that polygon alone, not any other polygons. There does not seem to be a need for specifying the correlation between the computed result and the original polygon using an ON clause. The "join condition" seems to be implied in the dependence of columns in the lateral join.

I couldn't find confirmation from the linked documentation whether an ON-clause is necessary or even allowed for a LATERAL JOIN. Hence this question:

Does a LATERAL JOIN require/allow a join condition (an ON clause)?

Answer 1

Yes, just get rid of the ancient way of listing tables in the FROM clause separated by commas:

SELECT p1.id, p2.id, v1, v2
FROM polygons p1
  JOIN polygons p2 ON p1.id != p2.id;
  JOIN LATERAL vertices(p1.poly) v1 ON true ???? 
  JOIN LATERAL vertices(p2.poly) v2 ON (v1 <-> v2) < 10 

The ??? marks the position where your original query missed a join condition between v1 and p1 or p2

Answer 2

I don't think that LATERAL changes anything here.

A comma join or a CROSS JOIN cannot have ON (and you need to stuff the conditions into the WHERE if you have any), while other explicit JOIN types require an ON or USING. (I am ignoring NATURAL JOIN, as it justly deserves.)

If you want to return a NULL-extended row in the case where the lateral join returns no rows, then you would use LEFT JOIN LATERAL (...) ON TRUE. Any condition other than TRUE is not necessary, as you could have just wrote it into the subquery itself (but perhaps it looks nicer to put something in the ON, if it increases the parallelism with other queries you also need to maintain, or if it just nicely separates the logic between what row to return, vs whether to return any rows at all)

But there is no absolute reason to use JOIN LATERAL (...) ON TRUE because you could just write it as a CROSS JOIN LATERAL instead. But again, it might "look better" one way versus the other.