Summary

Add support for non-correlated subqueries on the right side of ES|QL LEFT OUTER JOIN and INNER JOIN. The implementation preserves repeated matching rows from the subquery and enforces the intermediate local-relation size limit during materialization.

Usage

LEFT OUTER JOIN (LOJ) retains unmatched left rows and returns nulls for the subquery's columns:

ROW key = 1

| LEFT OUTER JOIN (ROW key = 1, value = "right") ON key

INNER JOIN (IJ) returns only rows with a matching key:

ROW key = 1

| INNER JOIN (ROW key = 1, value = "right") ON key

Design

SubqueryJoin and the existing IN/NOT IN subquery join nodes share the SubPlanJoin lifecycle. EsqlSession executes independent right-side subplans bottom-up, materializes each result as a LocalRelation, and replaces the subplan through inlineSubqueryResult. SubqueryJoinInliner then builds a MaterializedSubqueryJoin that preserves the requested join type, right-side payload, and repeated-key matches. This reuses IN-subquery scheduling and replacement without applying IN's deduplication, filtering, or three-valued logic to JOIN semantics.

Performance

The right side is built once into a shared hash table that every driver probes. The table uses the same hashes as hash aggregation, and large tables are built in parallel partitions.

Local benchmark on a single node (14 CPUs, on battery, so compare ratios rather than absolute times). The left index has 1M or 10M rows, the right index has 1K or 1M rows with one row per key, and both result sets match in every cell. Times are medians in ms; the last column is LOOKUP JOIN time divided by subquery join time, so above 1x means the subquery join is faster.

FROM perf_main_10m

| EVAL key = key_1m

| LEFT OUTER JOIN (FROM perf_lk_1m) ON key   // or: LOOKUP JOIN perf_lk_1m ON key

| STATS matched = COUNT(name), score_sum = SUM(score) BY category   // or: KEEP val, key, name, score | LIMIT 1000

Query
Left
Right
LOOKUP JOIN
Subquery join
Lookup / subquery

agg (full scan)
1M
1K
313
86
3.64x

agg (full scan)
1M
1M
527
288
1.83x

agg (full scan)
10M
1K
1768
532
3.32x

agg (full scan)
10M
1M
3790
1434
2.64x

limit 1000 after join
1M
1K
5
5
1.00x

limit 1000 after join
1M
1M
7
197
0.04x

limit 1000 after join
10M
1K
4
4
1.00x

limit 1000 after join
10M
1M
7
194
0.04x

When the query aggregates over the join, so every left row is probed, the subquery join is 1.8x to 3.6x faster than LOOKUP JOIN. Probing an in-memory hash table is much cheaper than LOOKUP JOIN's per-page lookups against the lookup index.

When a LIMIT follows the join, LOOKUP JOIN is usually faster because it does not need to build anything: it looks up only the few left rows needed to fill the limit and stops. The subquery join must first run the subquery and build the whole hash table before it can return its first row. With a 1K-row right side that build is negligible and both take about the same time, but with a 1M-row right side the build dominates (about 195 ms against 7 ms).