The JOIN Matrix: How Cross-Index JOIN Actually Works on Elasticsearch

This is the deep technical post for the SoftClient4ES launch. Previously: Stop ETL’ing Elasticsearch Into Your Warehouse Just to JOIN It — the launch overview. Here we open the hood on all three rows of the JOIN ladder, with a worked SQL example for each.

The Question This Post Answers

“Elasticsearch has no native cross-index JOIN. So how does SELECT … FROM a JOIN b return rows?”

The short version: an embedded DuckDB engine does the joining, and Elasticsearch does what it’s good at — fetching the rows for each side. SoftClient4ES turns each table reference in your JOIN into an Elasticsearch sub-query, pulls the matching rows out, and joins them in a real relational engine that ships inside the driver. You write SQL; the engine decides what to push down to Elasticsearch and what to compute itself.

That single idea scales into three distinct shapes — the three rows of the JOIN matrix. We’ll walk each one with a concrete query.

Throughout, we use the employees + departments shape: jdbc_join_emp (id, name, dept_id, salary) and jdbc_join_dept (dept_id, dept_name, region).

Row 1 — Same-Cluster Passthrough

The architecture

Both tables live in the same Elasticsearch cluster. When you submit a JOIN:

1. The planner splits the query into one Elasticsearch sub-query per table reference.
2. Each sub-query is pushed to Elasticsearch with as much of the WHERE clause as can be evaluated there (predicate pushdown — more on this below).
3. The rows come back and are loaded into the embedded DuckDB engine.
4. DuckDB performs the actual JOIN, the GROUP BY, the HAVING, the ORDER BY, and any post-join projection.
5. You get standard rows back — a JDBC ResultSet, an Arrow batch, or a REPL table, depending on the surface.

No coordinator. No second process. No data leaves the cluster. The whole thing happens where the SQL is parsed.

The worked example

SELECT  e.name,
        e.salary,
        d.dept_name
FROM    jdbc_join_emp  AS e
JOIN    jdbc_join_dept AS d
        ON e.dept_id = d.dept_id
WHERE   e.salary > 5000
ORDER BY e.salary DESC;

Two indices. One JOIN key (e.dept_id = d.dept_id). One WHERE predicate that filters the employee side before the join. The salary > 5000 filter is pushed into the Elasticsearch sub-query for jdbc_join_emp, so Elasticsearch only ships the high earners — then DuckDB joins the (smaller) result against departments.

INNER, LEFT, and the rest

This release supports the standard join flavors. INNER JOIN (the default) returns only matched rows; LEFT JOIN keeps every left row and null-fills the right:

-- Every employee, even those whose department row is missing
SELECT  e.name, e.salary, d.dept_name
FROM    jdbc_join_emp  AS e
LEFT JOIN jdbc_join_dept AS d ON e.dept_id = d.dept_id
ORDER BY e.name;

Because the join executes in DuckDB, the relational semantics are the ones you expect from a real SQL engine — not an enrich-policy approximation.

Predicate pushdown

The planner’s job is to make Elasticsearch do as much filtering as possible before rows reach DuckDB. A predicate on a single table (e.salary > 5000, d.region = 'EU') is pushed into that table’s Elasticsearch sub-query. The smaller the rows that come back, the cheaper the join. Predicates that reference both sides of the join (genuine join conditions beyond the key) are evaluated in DuckDB after the rows meet.

GROUP BY and HAVING over a JOIN

The join result is a relation like any other, so you aggregate it directly:

SELECT  d.dept_name,
        COUNT(*)       AS headcount,
        AVG(e.salary)  AS avg_salary,
        MAX(e.salary)  AS top_salary
FROM    jdbc_join_emp  AS e
JOIN    jdbc_join_dept AS d ON e.dept_id = d.dept_id
GROUP BY d.dept_name
HAVING  AVG(e.salary) > 75000
ORDER BY AVG(e.salary) DESC;

This is the report that, without this release, forces you to denormalize employees-with-departments into a third index first. Here it’s one statement.

INSERT … SELECT … JOIN, and CTAS

The join result can be a source for writes. Materialize a joined report straight into a new index:

-- Write the joined, filtered report into a fresh index
INSERT INTO high_earner_report (name, salary, dept_name)
SELECT  e.name, e.salary, d.dept_name
FROM    jdbc_join_emp  AS e
JOIN    jdbc_join_dept AS d ON e.dept_id = d.dept_id
WHERE   e.salary > 5000;
-- Or create the target table and fill it in one shot (CTAS)
CREATE TABLE high_earner_report AS
SELECT  e.name, e.salary, d.dept_name
FROM    jdbc_join_emp  AS e
JOIN    jdbc_join_dept AS d ON e.dept_id = d.dept_id
WHERE   e.salary > 5000;

ON CONFLICT upsert

When the target index has a primary key, you can upsert the joined rows instead of blindly inserting — ON CONFLICT (…) DO UPDATE replaces the conflicting row with the incoming values:

INSERT INTO dept_rollup (dept_name, headcount)
SELECT  d.dept_name, COUNT(*)
FROM    jdbc_join_emp  AS e
JOIN    jdbc_join_dept AS d ON e.dept_id = d.dept_id
GROUP BY d.dept_name
ON CONFLICT (dept_name) DO UPDATE;

Prepared statements

Row 1 JOINs work through prepared statements with bound parameters — exactly what BI tools and parameterized application code expect:

SELECT e.name, d.dept_name
FROM   jdbc_join_emp  e
JOIN   jdbc_join_dept d ON e.dept_id = d.dept_id
WHERE  d.region = ? AND e.salary > ?;

The parameters are substituted before parsing, so the full join plan is built against concrete values.

Row 2 — Cross-Cluster Conveyor

The architecture

Row 1 lives inside one cluster. Row 2 is the first time data crosses a cluster boundary. The shape is a conveyor belt:

source cluster  ──SELECT──►  Federation coordinator  ──bulk-load──►  target cluster

The source SELECT (which may itself be a Row-1 cross-index JOIN) runs on cluster A. Its result streams through the coordinator. The coordinator bulk-loads those rows into an index on cluster B. You never hand-roll a reindex, a scroll loop, or a cross-cluster reindex API call — you write INSERT … SELECT or CTAS, and the catalog prefix tells the engine which cluster each side lives on.

The worked example

Catalog-qualified table names (cluster.index) tell the planner where each table is:

-- Move completed EU orders into the US analytics cluster, joined with customers
INSERT INTO `prod_us`.eu_orders_enriched (order_id, amount, customer_name)
SELECT  o.order_id,
        o.amount,
        c.customer_name
FROM    `prod_eu`.orders    AS o
JOIN    `prod_eu`.customers AS c ON o.customer_id = c.id
WHERE   o.status = 'completed';

The SELECT (a Row-1 JOIN, both sides on prod_eu) fetches its legs from the EU cluster; the joined result is bulk-loaded into eu_orders_enriched on the US cluster. One statement, two clusters.

CTAS works across clusters too — create the target on cluster B from a query on cluster A:

CREATE TABLE `prod_us`.eu_orders_snapshot AS
SELECT o.order_id, o.amount, o.status
FROM   `prod_eu`.orders AS o
WHERE  o.created_at >= '2026-01-01';

This is the SQL-native replacement for “stand up a nightly export job to copy one cluster’s data into another.” It’s a statement, not a pipeline.

Row 3 — Multi-Source Coordinator

The architecture

Row 3 is the top of the ladder: a single JOIN whose tables live on two or more different clusters. Elasticsearch has no concept of this. Even cross-cluster search doesn’t JOIN — it federates a search, not a relational join.

Here’s how this release does it:

1. Each leg of the join (each per-cluster SELECT) runs on its own cluster.
2. Each leg’s result is staged to a Parquet scratch area on the coordinator.
3. The coordinator exposes each staged leg as a per-query DuckDB view.
4. DuckDB joins the views coordinator-local — the relational join happens in one place, over data gathered from many clusters.

The Parquet staging is what makes 3+ clusters tractable: every leg lands in the same columnar format, in the same place, and the join is a normal local DuckDB join from there.

The worked example: three clusters, one query

SELECT  o.order_id,
        o.amount,
        c.customer_name,
        c.region,
        f.rate
FROM    `prod_us`.orders    AS o
JOIN    `prod_eu`.customers AS c ON o.customer_id = c.id
JOIN    `prod_ap`.fx_rates  AS f ON o.currency    = f.currency;

Orders in the US cluster, customers in the EU cluster, FX rates in the AP cluster — joined in one statement. Each leg is fetched from its own region, staged, and joined coordinator-local. The application sees a single result set.

Today every leg is an Elasticsearch cluster. The same staging mechanism is what will let the upcoming release (Quarter 1 2027) attach heterogeneous sources — Postgres, MySQL, Snowflake — as additional legs through the DuckDB engine. That’s roadmap, not this release; we’re flagging it so the architecture makes sense, not promising it today.

The Licensing Meter

Cross-index JOIN is metered by JOINs per query, and the meter is generous where it matters most:

• Community (free): 2 cross-index JOINs per query. A two-way JOIN (one JOIN keyword) and even a three-table chain are within reach for a huge class of real reports — at no cost.
• Pro: 5 JOINs per query (plus 5 clusters for Row 2/Row 3).
• Enterprise: unlimited.

When a query exceeds your JOIN quota, the JOIN planner rejects it before execution — no half-run query, no silent truncation. The rejection error names the JOIN count in your query, your tier’s limit, the next tier up, and the upgrade URL, so it’s clear what happened and what to do next. The result-row cap (10k Community / 1M Pro) is enforced separately: it either truncates-with-warning or, on an explicit LIMIT over the cap, returns an HTTP 402 — by configuration. And in the Federation deployment, the cluster meter is enforced at startup: an over-quota federation sidecar fails to start by design, so you find out at deploy time, not mid-incident. (402 is reserved for the metered surfaces — Materialized Views and an explicit over-cap LIMIT — not for the JOIN cap.)

Every tier has every JOIN row. The meter only governs scale.

Honest Gap Note: Subqueries and CTEs

This release joins tables. It does not yet join subqueries or support WITH (common table expressions). If your logic today reads like:

-- NOT in this release — planned for Quarter 4 2026
WITH recent AS (
  SELECT * FROM jdbc_join_emp WHERE hired_at >= '2026-01-01'
)
SELECT r.name, d.dept_name
FROM   recent r JOIN jdbc_join_dept d ON r.dept_id = d.dept_id;

you’ll need to flatten it to a direct table JOIN for now. Nested SELECT and CTE support is planned for Quarter 4 2026. We’d rather tell you here than have you discover it from a parser error.

Putting It Together

Start at Row 1. It’s free for two JOINs, needs no coordinator, and covers most cross-index reporting. Climb the ladder only when your topology forces you to.

Resources

• SQL Reference: Full SQL Documentation
• Helm chart (Federation): softclient4es-helm
• GitHub: SoftClient4ES Repository
• Discussions: Ask Questions
• LinkedIn: SoftNetwork

What’s the most expensive cross-index JOIN your team has worked around? If it involved a second copy of the data, there’s now a one-statement alternative.

P.S. — Row 1 cross-index JOIN is free for up to two JOINs per query, runs in an embedded DuckDB engine with each table pushed down to Elasticsearch, and ships on every surface — REPL, JDBC, ADBC, Arrow Flight SQL, and Federation.