Elasticsearch SQL joins across indices

Elasticsearch has no JOIN. FlatQL fetches each side from the cluster, sends the join keys of the first side to the second as a terms filter, and joins the rows in its engine.

A first join#

organizations carries a primary_domain; suppliers carries a domain. Joining on them gives each organisation its supplier record:

SELECT o.name, o.country, s.category, s.rating
FROM organizations o
JOIN suppliers s ON s.domain = o.primary_domain
WHERE o.country = 'DEU' AND o.status = 'active'
ORDER BY s.rating DESC
LIMIT 100;

What happened, in order:

1. organizations was fetched with the two pushed filters (term on country, term on status) and _source limited to country, name, primary_domain, status.
2. The distinct primary_domain values of those rows were sent to suppliers as a terms filter on domain. The execution line reads suppliers was filtered to the 312 join keys found in organizations.
3. The engine joined the two row sets, sorted by rating and took 100.

ORDER BY and LIMIT are not pushed in a join; the pushdown rules allow them only for single-table statements. The join itself and anything after it run in the engine.

Join types#

Semantics are standard SQL as DuckDB implements them. Null join keys never match. Suppliers with no organisation:

SELECT s.name, s.domain, s.country
FROM suppliers s
LEFT JOIN organizations o ON o.primary_domain = s.domain
WHERE s.country = 'GBR'
ORDER BY s.name;

Here suppliers is the filtered side, so it is fetched first and organizations is narrowed to the supplier domains.

How the key pushdown works#

The engine orders the fetch so the side that is already narrow goes first: a side with a pushed LIMIT before one with pushed filters, and both before an unfiltered side. For every later side that is equi-joined to an already fetched one, FlatQL collects the join-key values from the fetched rows and adds a Query DSL terms filter to that side's request (an ids query when the key is _id).

Three conditions must hold:

• the ON clause contains a plain column = column equality between the two aliases;
• the fetched side was not truncated at the 50,000-row cap, because then its key set would be incomplete and the filter would drop legitimate matches;
• the number of distinct keys is at most 50,000 (TERMS_PUSHDOWN_MAX, below the cluster's 65,536 terms limit).

If any condition fails, the second side is fetched with only its own pushed WHERE conjuncts, up to the cap, and the engine joins what it has. The DSL tab shows the terms filter when it was sent, so you can check.

Filter the smaller side#

The key pushdown only helps when one side is small after its own filters. Compare:

-- the engine must scan both indices up to the cap
SELECT o.name, s.rating
FROM organizations o
JOIN suppliers s ON s.domain = o.primary_domain;

-- organizations is narrowed first, suppliers receives 1 terms filter
SELECT o.name, s.rating
FROM organizations o
JOIN suppliers s ON s.domain = o.primary_domain
WHERE o.country = 'NLD' AND o.founded_year >= 2018;

Rules of thumb:

• Put the selective predicate on the side that has it, written against that side's alias.
• Qualify every column with its alias. In a multi-table statement an unqualified country cannot be attributed to an index, so it is not pushed, and it also makes FlatQL fetch every field of every index.
• When both sides are large, aggregate one side first in a CTE and join the aggregate; see CTEs.
• When the join key is a text field with no keyword sub-field, the terms filter cannot be applied exactly and is skipped. Join on keyword fields.

ASOF JOIN#

An ASOF JOIN matches each left row to the single right row with the same key and the nearest timestamp that satisfies the condition. The Snowflake form is MATCH_CONDITION (left.ts >= right.ts) ON left.key = right.key; FlatQL folds the condition into the ON clause for DuckDB. For every order, the customer's previous order:

SELECT o.order_id, o.customer_id, o.placed_at, o.amount,
       p.order_id AS previous_order, p.amount AS previous_amount
FROM orders o
ASOF JOIN orders p
  MATCH_CONDITION (o.placed_at > p.placed_at)
  ON o.customer_id = p.customer_id
WHERE o.placed_at >= '2026-10-01' AND o.status = 'paid'
ORDER BY o.customer_id, o.placed_at;

The index appears twice under two aliases, so it is fetched twice. The o side carries the pushed filters and goes first; the p side receives a terms filter on customer_id with the keys found in o. The equality in the ON clause is what enables that; the inequality is applied by the engine.

Joining an aggregate to rows#

Average supplier rating per country, joined to each organisation in that country:

WITH rating_by_country AS (
  SELECT s.country, AVG(s.rating) AS avg_rating, COUNT(*) AS suppliers
  FROM suppliers s
  WHERE s.rating IS NOT NULL
  GROUP BY s.country
)
SELECT o.name, o.country, r.avg_rating, r.suppliers
FROM organizations o
JOIN rating_by_country r ON r.country = o.country
WHERE o.status = 'active' AND o.country IN ('DEU', 'FRA', 'ESP')
ORDER BY r.avg_rating DESC, o.name
LIMIT 200;

rating_by_country is a CTE, not an index, so no terms filter flows into it; the suppliers scan is bounded by its own WHERE and the cap. The organizations side is narrowed by the term and terms filters. Because the statement has two SELECTs, the GROUP BY runs in the engine rather than as a composite aggregation; for a large supplier index, run the aggregate on its own first and check the row count.

Reading the DSL tab#

After a join, the DSL tab lists one request per alias. Look for the terms block in the second request and the row count on each chip. If the first request shows scroll (up to 50,000 rows) with 50,000 rows, that side was truncated, the key pushdown was skipped, and the result is incomplete; add filters until it reads search or fewer rows. Show as DSL explains the tab in full.

Joins are the main reason to put a SQL engine in front of Elasticsearch; Elasticsearch SQL joins sets out the alternatives without one. Window functions covers the other large gap.