[NH] Support lateral joins

[jseldess]

Background: https://airtable.com/tblD3oZPLJgGhCmch/viw1DKmbKhg2MIECH/recueu9RI5gmwC1r3

Description: Similar to a correlated subquery, a lateral inner subquery can refer to fields in rows of the table reference to determine which rows to return. A lateral subquery iterates through each row in the table reference, evaluating the inner subquery for each row, like a foreach loop. The rows returned by the inner subquery are added to the result of the join with the outer query. Without the LATERAL keyword, each subquery is evaluated independently and cannot refer to items in FROM. Note, many queries are implicitly LATERAL even if the keyword is not used.

Many customers, especially those using JSON, want to evaluate data efficiently without independently referring to the FROM clause. Currently, each subquery is evaluated independently and cannot refer to items in FROM which can be slow and complex to implement.

Team: Andrew Woods, Radu Berinde, Justin Jaffray, Chris Seto

Github Tracking Issue: cockroachdb/cockroach#24560 cockroachdb/cockroach#7841 https://github.com/cockroachdb/cockroach/pull/40945/files/aa051269c8ca407f88d1c2418308fa532361ab42..b36223663ee5d2a6a008db25d824773ff1494f06

[ericharmeling]

From release note issue:

PR: cockroachdb/cockroach#40945

From release notes:

Added support for the JOIN LATERAL syntax. [#40945][#40945] {% comment %}doc{% endcomment %}

[rytaft]

Related to this issue, I think we should also have documentation for "apply join". Apply join is the operator that actually executes a lateral join if the optimizer is not able to decorrelate it (i.e., rewrite the query to use a regular join). Most of the time, customers would not need to know about apply joins since the optimizer can decorrelate most queries. However, there are some cases where the optimizer cannot perform this rewrite, and therefore "apply join" would show up in the EXPLAIN output for the query. The optimizer also replaces correlated subqueries with apply joins, and therefore "apply join" may appear in the EXPLAIN output even if LATERAL was not used.

I bring this up because a customer using 19.2 saw "apply join" in their EXPLAIN plan and didn't know what it was.