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.

An outer join, on the other hand, can be thought of as an inclusive join because unmatched rows from either side of the join (the left side or the right side) can be included

Java中Thread类的join方法到底是如何实现等待的？

The difference between a non- lateral and a lateral join lies in whether you can look to the left hand table's row.

LEFT OUTER JOIN First, an inner join is performed. Then, for each row in T1 that does not satisfy the join condition with any row in T2, a joined row is added with null values in columns of T2. Thus, the joined table always has at least one row for each row in T1.

It is often particularly handy to LEFT JOIN to a LATERAL subquery, so that source rows will appear in the result even if the LATERAL subquery produces no rows for them.

SELECT lateral_subquery.* FROM posts JOIN LATERAL ( SELECT comments.* FROM comments WHERE (comments.post_id = posts.id) LIMIT 3 ) lateral_subquery ON true WHERE posts.id

You want the front page to show a few hundred posts along with the top three comments on each post. You’re planning on being very popular, so the front page will need to be very fast. How do you fetch that data efficiently from postgresql using Activerecord?

Making one Comment query per Post is too expensive; it’s N+1 queries (one to fetch the posts, N to fetch the comments). You could use includes to preload all the comments for all the posts, but that requires hydrating hundreds of thousands of records, even though you only need a few hundred for your front page. What you want is some kind of GROUP BY with a LIMIT on each group — but that doesn’t exist, either in Activerecord nor even in postgres. Postgres has a different solution for this problem: the LATERAL JOIN.

Inner Join Venn Diagram

join = Arel::Nodes::NamedFunction.new('json_b_array_elements', [Arel::Nodes::SqlLiteral.new("subscriptions")]) .as(Arel::Nodes::NamedFunction.new('sd', [Arel::Nodes::SqlLiteral.new("subscription_data")]).to_sql) p = e.project( Arel::Nodes::SqlLiteral.new( Arel::Nodes::Grouping.new( Arel::Nodes::InfixOperation.new('->>', sd[:subscription_data], Arel::Nodes::SqlLiteral.new("'id'"))).to_sql) << '::uuid' ).where( Arel::Nodes::InfixOperation.new('->>', sd[:subscription_data], Arel::Nodes::SqlLiteral.new("'type'").eq( Arel::Nodes::SqlLiteral.new("'Company'") ) ).and(e[:slug].eq(event_slug))) p.join_sources << Arel::Nodes::StringJoin.new( Arel::Nodes::SqlLiteral.new('CROSS JOIN LATERAL')) << join

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.

The preservation of meets and joins, and hence whether a monotone map sustainsgenerative effects, is tightly related to the concept of a Galois connection, or moregenerally an adjunction.

In his work on generative effects, Adam restricts his attention to maps that preservemeets, even while they do not preserve joins. The preservation of meets implies that themapbehaves well when restricting to a subsystem, even if it can throw up surpriseswhen joining systems

n [Ada17], Adam thinks of monotone maps as observations. A monotone map:P!Qis a phenomenon ofPas observed byQ. He defines generative effects of such a mapto be its failure to preserve joins (or more generally, for categories, its failure topreserve colimits)

Example1.61.Consider the two-element setPfp;q;rgwith the discrete ordering.The setAfp;qgdoes not have a join inPbecause ifxwas a join, we would needpxandqx, and there is no such elementx.Example1.62.In any posetP, we havep_pp^pp.Example1.63.In a power set, the meet of a collection of subsets is their intersection,while the join is their union. This justifies the terminology.Example1.64.In a total order, the meet of a set is its infimum, while the join of a set isits supremum.Exercise1.65.Recall the division ordering onNfrom Example 1.29: we say thatnmifndivides perfectly intom. What is the meet of two numbers in this poset? Whatabout the join?

join_paired_ends.py