Why Elasticsearch SQL Can't Do Joins

A search engine answers per shard#

An Elasticsearch index is a set of shards. Every shard is a self-contained Lucene index that scores and filters its own documents. A search request fans out to the shards, each one returns its best hits, and the coordinating node merges the partial lists into one response. That design is why a 40-shard index answers a term query in milliseconds: nothing has to be shuffled between nodes.

A join breaks the model. To join orders to suppliers on a key, the engine would have to move one side's rows to wherever the matching rows of the other side live, or build a hash table that every shard can consult, and do that for two indices that may be refreshing independently. Elasticsearch has no shuffle stage and no transaction that spans two indices. Documents are the unit of consistency; there is no snapshot that pins orders and suppliers at the same moment. Scoring makes it worse: a hit's relevance belongs to one query on one shard, and nobody has defined what the score of a joined row should be.

So the cluster never grew a join operator, and the SQL layers built on top of it inherited that.

What x-pack SQL offers instead#

The official SELECT syntax page is direct about it: "Currently FROM supports exactly one table." The table can be an alias or a pattern that resolves to several indices, "with the restriction that all resolved concrete tables have exact mapping." That is a union of identically shaped indices, not a join between differently shaped ones.

The SQL limitations page closes the other door. Sub-selects are supported only when they can be flattened into a single SELECT; anything with GROUP BY or HAVING inside, or an outer query more complex than SELECT X FROM (SELECT ...) WHERE [simple_condition], is rejected. There is no JOIN keyword to document because there is no operator behind it.

The OpenSearch SQL plugin went a different way. Its legacy engine runs joins "on a best-effort basis": no aggregations on the joined result, no pagination, a 60-second default timeout, and a warning about performance above 5 million matching records. The newer V2 engine drops join support. See FlatQL vs OpenSearch SQL for how that plays out in practice.

The alternatives and what they cost#

Denormalise. Copy the supplier's name, country and rating into every order document at index time. Reads are fast and need no join. The cost lands on writes: when a supplier's rating changes, every order that mentions it is stale until you run an update-by-query or reindex. Reference data that changes weekly turns into a reindex schedule.

Parent/child with the join field. Elasticsearch can model one-to-many relations inside a single index. The join field documentation states that parent and child documents must live on the same shard, that "Only one join field mapping is allowed per index", and that "The join field shouldn't be used like joins in a relation database." Each has_child or has_parent query adds cost, and the data has to be ingested with routing from the start.

Enrich processor. An ingest pipeline can look up a document in another index and copy fields in. The enrich processor page says it "works best with reference data that doesn't change frequently" and that Elastic does "not recommend using the enrich processor to append real-time data." Enrichment happens when the document passes through the pipeline, so it is a snapshot of the lookup at ingest time, and you need a policy per lookup.

Application joins. Query one index, collect the keys, query the second index with a terms filter, and stitch the rows together in Python or Java. This works and it is exactly the right idea. The cost is that every analytical question becomes a small program, and someone has to maintain it.

That last pattern is what FlatQL automates.

How FlatQL runs a JOIN#

FlatQL does not install anything on the cluster. It parses your SQL with sqlglot, fetches each index named in FROM or JOIN over the ordinary search API, loads the rows into an embedded DuckDB engine, and lets DuckDB do the relational work. The joins reference lists the supported join types; the interesting part is how little data crosses the wire.

Step 1: push each side's own filters#

Every WHERE conjunct that maps 1:1 to a Query DSL filter is sent to the cluster for the index it belongs to: equality and IN on keyword fields become term and terms, comparisons and BETWEEN on numbers and dates become range, LIKE on a keyword becomes wildcard, IS NULL becomes a negated exists. The full WHERE still runs in DuckDB afterwards; pushing only shrinks what is fetched. The rules are spelled out in pushdown.

Step 2: send the join keys across#

The sides are fetched in a fixed order: tables with a pushed LIMIT first, then tables with pushed filters, then the rest. When the next side is equi-joined to one that has already been fetched, FlatQL collects the distinct values of the join column from the rows it has in hand and adds them to the request as a terms filter (or an ids query when the key is _id). The constant TERMS_PUSHDOWN_MAX caps that list at 50,000 keys, under the 65,536-term limit Elasticsearch imposes. A result note records what happened: "suppliers was filtered to the 1,240 join keys found in organizations."

Two safety rules apply. If the first side was truncated, its keys are not sent, because filtering the second side by an incomplete key set would silently drop matches. And if there are more than 50,000 distinct keys, the second side is scanned without the terms filter.

Step 3: join in DuckDB#

Both row sets are loaded into DuckDB. The JOIN, the ORDER BY, the LIMIT, any window function or QUALIFY, all run there. Show as DSL lists the request sent for each index so you can see what the cluster did and what the engine did; the Show as DSL page explains the panel.

The fetch cap and when it bites#

Each index is fetched up to FETCH_CAP, 50,000 rows per statement. A full scan runs as four sliced scrolls in parallel, 5,000 rows per page, each slice taking its share of the cap. When an index has more matching documents than that, the result carries a warning naming the index, the matching count and the number scanned. The limits page has the full list.

The cap bites when both sides of a join are large and neither has a selective pushed filter: the first side is cut at 50,000, so its keys cannot be used, and the second side is cut at 50,000 as well. The fix is always the same. Filter the smaller side with predicates the cluster can apply, and let the key pushdown take care of the other one.

One example, both requests#

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

Both conjuncts push. Neither side gets the LIMIT, because LIMIT and ORDER BY are pushed only for single-table statements. The organizations side is fetched first with its own filter and only the columns the statement needs:

POST /organizations/_search?scroll=2m

{
  "query": { "bool": { "filter": [ { "term": { "country": "DE" } } ] } },
  "_source": ["country", "name", "primary_domain"],
  "size": 5000,
  "sort": ["_doc"]
}

The distinct primary_domain values from those rows become the terms filter on the suppliers side, next to its own range filter:

POST /suppliers/_search?scroll=2m

{
  "query": {
    "bool": {
      "filter": [
        { "range": { "rating": { "gte": 4 } } },
        { "terms": { "domain": ["acme.example", "bauer.example", "..."] } }
      ]
    }
  },
  "_source": ["category", "domain", "name", "rating"],
  "size": 5000,
  "sort": ["_doc"]
}

DuckDB then joins the two sets on domain = primary_domain, sorts by rating and returns 100 rows. If organizations holds 300 German companies, the supplier side fetches at most the suppliers that match those 300 domains, however many million documents the index holds.

Note o.country = 'DE' is pushed because country is a keyword field. The same predicate on a text field without a keyword sub-field is sent as a match_phrase to narrow the fetch, and the exact comparison still happens in the engine.

Where this leaves you#

Elasticsearch SQL is a thin layer over the search API and is honest about it: one table per statement, flattenable sub-selects only. When you need two indices in one answer, the choices are to reshape the data or to do the join outside the cluster. FlatQL takes the second route and makes it a single SQL statement, with the cluster doing the filtering and a local engine doing the join. The Elasticsearch SQL joins page has more worked examples, and the Elasticsearch SQL limitations page covers the rest of the gap.

The Free plan includes the full engine, joins included, on one cluster. Create an account, connect a cluster and run the statement above against your own indices.

Why Elasticsearch SQL can't do joins (and how FlatQL does)