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)?
LATERAL
doesn't change the requirements forON
. If it is an implicit ("comma join") as your examples, you can't put anON
clause. If it aLEFT / INNER JOIN LATERAL
, then it needs anON
clause.