But there is no absolute reason to use JOIN LATERAL (...) ON TRUE because you could just write it as a CROSS JOIN LATERAL instead

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

typical use would be to reference a json or jsonb column laterally from another table in the query's FROM clause.

And that can all can be written with CROSS JOIN LATERAL which is much cleaner, SELECT ARRAY( SELECT DISTINCT e FROM ( VALUES ('foo', 'bar', 'foo', 'baz' ) ) AS t(a,b,c,d) CROSS JOIN LATERAL unnest(ARRAY[a,b,c,d]) AS a(e) -- ORDER BY e; -- if you want it sorted );

A LATERAL item can appear at top level in the FROM list, or within a JOIN tree. In the latter case it can also refer to any items that are on the left-hand side of a JOIN that it is on the right-hand side of.

SELECT base.nr, multiples.multiple FROM (SELECT generate_series(1,10) AS nr) base, LATERAL ( SELECT multiples.multiple FROM ( SELECT generate_series(1,10) AS b_nr, base.nr * 2 AS multiple ) multiples WHERE multiples.b_nr = base.nr ) multiples;

SELECT s.id, s1.percent_water , s1.percent_water * 100 AS percent_water_100 FROM samples s , LATERAL (SELECT s.wet_weight / NULLIF(s.dry_weight - 1, 0) AS percent_water) s1;

SELECT DISTINCT ON (1) t.id, t.name, d.last FROM tbl t LEFT JOIN LATERAL json_array_elements_text(data) WITH ORDINALITY d(last, rn) ON d.last <> t.name ORDER BY d.rn DESC;

Unnest in a LEFT JOIN LATERAL (clean and standard-conforming)

The clean way to call a set-returning function is LEFT [OUTER] JOIN LATERAL. This includes rows without children. To exclude those, change to a [INNER] JOIN LATERAL

JOIN LATERAL

The age_in_years is calculated for every record of the blog table. So, it works like a correlated subquery, but the subquery records are joined with the primary table and, for this reason, we can reference the columns produced by the subquery.

SELECT * FROM departments AS d, LATERAL (SELECT * FROM employees AS e WHERE e.department_ID = d.department_ID)

In a FROM clause, the LATERAL keyword allows an inline view to reference columns from a table expression that precedes that inline view.

Subqueries appearing in FROM can be preceded by the key word LATERAL. This allows them to reference columns provided by preceding FROM items. (Without LATERAL, each subquery is evaluated independently and so cannot cross-reference any other FROM item.) TL;DR - LATERAL allows subqueries to reference earlier tables.