# Support for LATERAL clause when joining

**URL:** <https://www.singlestore.com/forum/t/support-for-lateral-clause-when-joining/2934>\
**Category:** Feedback\
**Created:** [April 20, 2021, 11:12am UTC](https://www.singlestore.com/forum/t/support-for-lateral-clause-when-joining/2934 "2021-04-20T11:12:10Z")\
**Posts on this page:** 6\
**Page:** 1

<div class="post-metadata">

**Author:** ![prerak](https://www.singlestore.com/forum/letter_avatar_proxy/v4/letter/p/90db22/32.png) [@prerak](https://www.singlestore.com/forum/u/prerak)\
**Post date:** [April 20, 2021, 11:12am UTC](https://www.singlestore.com/forum/t/support-for-lateral-clause-when-joining/2934/1 "2021-04-20T11:12:11Z")

</div>

**Describe the problem you’re experiencing?**  
See this: [How does TABLE(JSON\_TO\_ARRAY(Col)) work?](https://www.singlestore.com/forum/t/how-does-table-json-to-array-col-work/2926)

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(e.date) s ON e.symbol=s.symbol;

```

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

---

<div class="post-metadata">

**Author:** ![hanson](https://www.singlestore.com/forum/user_avatar/www.singlestore.com/hanson/32/196_2.png) [@hanson](https://www.singlestore.com/forum/u/hanson)\
**Post date:** [April 20, 2021, 9:27pm UTC](https://www.singlestore.com/forum/t/support-for-lateral-clause-when-joining/2934/2 "2021-04-20T21:27:23Z")

</div>

@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.

---

<div class="post-metadata">

**Author:** ![Graham859](https://www.singlestore.com/forum/letter_avatar_proxy/v4/letter/g/6bbea6/32.png) [@Graham859](https://www.singlestore.com/forum/u/Graham859)\
**Post date:** [October 9, 2021, 10:05am UTC](https://www.singlestore.com/forum/t/support-for-lateral-clause-when-joining/2934/3 "2021-10-09T10:05:22Z")

</div>

In a from clause , the lateral keyword allows an inline view to reference columns from a table expression that precedes that inline view[.](https://www.skylightpaycard.me/)

---

<div class="post-metadata">

**Author:** ![Capricorn](https://www.singlestore.com/forum/letter_avatar_proxy/v4/letter/c/c89c15/32.png) [@Capricorn](https://www.singlestore.com/forum/u/Capricorn)\
**Post date:** [November 22, 2021, 10:14am UTC](https://www.singlestore.com/forum/t/support-for-lateral-clause-when-joining/2934/4 "2021-11-22T10:14:02Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![Capricorn](https://www.singlestore.com/forum/letter_avatar_proxy/v4/letter/c/c89c15/32.png) [@Capricorn](https://www.singlestore.com/forum/u/Capricorn)\
**Post date:** [November 23, 2021, 10:27am UTC](https://www.singlestore.com/forum/t/support-for-lateral-clause-when-joining/2934/5 "2021-11-23T10:27:07Z")

</div>

thanks again [.](https://www.jcpenneykiosk.run/)

---

<div class="post-metadata">

**Author:** ![hanson](https://www.singlestore.com/forum/user_avatar/www.singlestore.com/hanson/32/196_2.png) [@hanson](https://www.singlestore.com/forum/u/hanson)\
**Post date:** [November 30, 2021, 4:53pm UTC](https://www.singlestore.com/forum/t/support-for-lateral-clause-when-joining/2934/6 "2021-11-30T16:53:30Z")

</div>

What do you mean by both options, @Capricorn?
