Support for LATERAL clause when joining

Describe the problem you’re experiencing?
See this: How does TABLE(JSON_TO_ARRAY(Col)) work?

We would like to be able to join tables where the right table is a sub-select of the first. Essentially, something equivalent to the LATERAL keyword in Postgres. There is almost always a way of re-writing the query in a different way to achieve the same result, but sometimes, it can get weirdly convoluted. An obvious example is your TABLE(JSON_TO_ARRAY(Col)) construct, which probably can be written without the implicit LATERAL join, but would look unnecessarily complicated.

What is your ideal solution? What are you looking for?
Either an explicit or implicit support for LATERAL join clauses. For example, if this could work:

SELECT date, symbol, filled_quantity FROM ExecutionReport e JOIN Latest_Security( s ON e.symbol=s.symbol;

What version(s) of MemSQL or related tools is this affecting?
7.3.7 (but all versions really)

@prerak Thanks for this feedback! We are tracking this as a possible enhancement and are interested in how popular this is with others, so we’ll watch the vote count.

By the way, for others reading this, LATERAL is the same as CROSS APPLY in SQL Server.

1 Like

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

But I suppose we could have both options somehow. I’m interested to hear about your use case.

thanks again .

What do you mean by both options, @Capricorn?