[FEATURE]Add multiple sources join query for PPL

[YANG-DB]

Is your feature request related to a problem?
Currently PPL has a single-source query capability:

Example:

search source=account
| where age > 18
| fields firstname, lastname

What solution would you like?
In a similar manner for the SQL engine which accepts multiple indices (sources) to perform a query, PPL should also allow such capability - this will mainly benefit the join use case in which we are required to match documents according to similar dimensions .

Example:

search source=account, purchase
| where account.age > 18, account.id = purchase.accountId
| fields account.firstname, account.lastname, purchase.amount
| stats avg(purchase.amount);

Another important consideration is the fact that we aim to use PPL as the main Observability query language and this would mandate us to allow such join capability for Correlation between different signals...

What alternatives have you considered?
N/A

Do you have any additional context?

• [FEATURE]Support ServiceMap (data-prepper) Integration observability#1473
• https://opensearch.org/docs/latest/search-plugins/sql/sql/complex/

[acarbonetto]

This may be blocked on a [BUG] on implicit joins that would probably exist on the PPL side: #683
We should consider https://github.com/Bit-Quill/opensearch-project-sql/pull/263/files as an initial concept for joins (within a single index). We should seriously consider against joining data between indexes or severely limiting this capability (see the trace-analytics use case: #892).

[YANG-DB]

see PPL - spark based correlation command