CTEs, UNION, HAVING and subqueries on Elasticsearch

Everything on this page runs in FlatQL's engine. The cluster still receives the WHERE filters of each index, so the usual rule applies, qualify columns and filter early, and the rest of the statement can be as structured as you like.

WITH: common table expressions#

A CTE names a result so the main query reads top down. Active organisations per country next to the supplier count per country:

WITH orgs AS (
  SELECT o.country, COUNT(*) AS organizations
  FROM organizations o
  WHERE o.status = 'active'
  GROUP BY o.country
),
sups AS (
  SELECT s.country, COUNT(*) AS suppliers, AVG(s.rating) AS avg_rating
  FROM suppliers s
  GROUP BY s.country
)
SELECT orgs.country, orgs.organizations, sups.suppliers, ROUND(sups.avg_rating, 2) AS avg_rating
FROM orgs
LEFT JOIN sups ON sups.country = orgs.country
ORDER BY orgs.organizations DESC
LIMIT 50;

Two things to notice. Every column inside the CTEs is qualified (o.status, s.country): a statement with several SELECTs is not single-table, and only qualified conjuncts are pushed to their index. And because the statement has several SELECTs, the two GROUP BYs run in the engine over scanned rows, bounded by the 50,000-row cap per index; the WHERE on organizations still travels to the cluster as a term filter. The pushdown page explains the single-table condition.

WITH RECURSIVE#

A recursive CTE builds rows from its own output. A month axis for a chart, so months without orders still appear:

WITH RECURSIVE months(m) AS (
  SELECT DATE '2026-01-01'
  UNION ALL
  SELECT DATEADD('month', 1, m) FROM months WHERE m < DATE '2026-10-01'
),
monthly AS (
  SELECT DATE_TRUNC('month', o.placed_at) AS m, SUM(o.amount) AS revenue
  FROM orders o
  WHERE o.currency = 'EUR' AND o.status = 'paid' AND o.placed_at >= '2026-01-01'
  GROUP BY 1
)
SELECT months.m, COALESCE(monthly.revenue, 0) AS revenue
FROM months
LEFT JOIN monthly ON monthly.m = months.m
ORDER BY months.m;

The anchor member supplies the first row, the recursive member adds one month at a time until the condition fails. The orders fetch carries three pushed filters.

START WITH ... CONNECT BY PRIOR#

Snowflake's hierarchy syntax is rewritten into a recursive CTE for you. It works over a single index, exposes LEVEL, and stops at a depth of 64:

SELECT id, parent_id, name, LEVEL
FROM <index>
START WITH parent_id IS NULL
CONNECT BY PRIOR id = parent_id;

CONNECT BY needs a condition with PRIOR; a join inside the same SELECT is refused with a message pointing you to WITH RECURSIVE, which handles anything more complex. The example indices on this site have no parent field, so the pattern is shown as a template.

UNION, EXCEPT, INTERSECT#

Set operations combine results from different indices as long as the column lists line up. One directory of organisations and suppliers in France:

SELECT o.name, o.country, 'organization' AS kind
FROM organizations o
WHERE o.country = 'FRA' AND o.status = 'active'
UNION ALL
SELECT s.name, s.country, 'supplier' AS kind
FROM suppliers s
WHERE s.country = 'FRA'
ORDER BY name;

UNION removes duplicate rows; UNION ALL keeps them and is faster. Supplier domains that are not any organisation's primary domain:

SELECT s.domain FROM suppliers s WHERE s.country = 'DEU'
EXCEPT
SELECT o.primary_domain FROM organizations o WHERE o.country = 'DEU';

INTERSECT returns the rows present on both sides. Each SELECT in a set operation is planned on its own, so each index receives its own filters.

HAVING#

HAVING filters groups after aggregation:

SELECT country, COUNT(*) AS orgs, MIN(founded_year) AS oldest
FROM organizations
WHERE status = 'active'
GROUP BY country
HAVING COUNT(*) >= 100
ORDER BY orgs DESC;

This statement is single-table with a plain grouping key and pushable aggregates, so the cluster computes the groups as a composite aggregation and the engine applies HAVING to the buckets. The execution line reads GROUP BY was computed by Elasticsearch aggregations. A HAVING on an aggregate the cluster cannot compute, such as MEDIAN, moves the whole GROUP BY into the engine.

Subqueries#

IN, EXISTS and scalar subqueries are supported. Organisations whose domain belongs to a highly rated supplier:

SELECT o.name, o.country, o.primary_domain
FROM organizations o
WHERE o.status = 'active'
  AND o.primary_domain IN (SELECT s.domain FROM suppliers s WHERE s.rating >= 4.5)
ORDER BY o.name
LIMIT 100;

An IN with a subquery is not a filter the cluster can apply, so only o.status = 'active' is pushed to organizations and s.rating >= 4.5 to suppliers; the membership test runs in the engine. When the subquery result is small and static, an explicit IN ('a', 'b') list pushes as a terms filter instead. A scalar subquery compares against one value:

SELECT s.name, s.rating
FROM suppliers s
WHERE s.category = 'logistics'
  AND s.rating > (SELECT AVG(x.rating) FROM suppliers x WHERE x.category = 'logistics')
ORDER BY s.rating DESC;

ROLLUP, CUBE and GROUPING SETS#

Subtotals in one statement:

SELECT currency, status, COUNT(*) AS orders, SUM(amount) AS revenue
FROM orders
WHERE placed_at >= '2026-10-01'
GROUP BY ROLLUP (currency, status)
ORDER BY currency NULLS LAST, status NULLS LAST;

ROLLUP adds a subtotal per currency and a grand total, shown with nulls in the rolled-up columns. CUBE adds every combination of subtotals; GROUPING SETS ((currency), (status), ()) names the exact sets you want. GROUP BY ALL groups by every non-aggregated column in the select list. A rolled-up grouping key is not a plain field, so these statements group in the engine over the filtered scan; the range on placed_at is still pushed.

PIVOT and UNPIVOT#

PIVOT turns the values of one column into columns:

SELECT *
FROM (
  SELECT o.status, o.currency, o.amount
  FROM orders o
  WHERE o.placed_at >= '2026-10-01'
)
PIVOT (SUM(amount) FOR currency IN ('EUR', 'USD', 'GBP'))
ORDER BY status;

The result has one row per status and a column per currency with the summed amount. UNPIVOT (value FOR metric IN (col_a, col_b)) does the reverse, folding several columns into name and value pairs; it is useful before charting a wide result.

Keeping these statements fast#

• Qualify every column with its alias in any statement that has more than one SELECT.
• Put the selective filter in the CTE or subquery that touches the index, not in the outer query; the outer query runs after the fetch.
• Check the DSL tab: each index should show its filters and, ideally, a row count well under 50,000.
• Aggregate first, then join or window over the aggregate, when an index is large.

For GROUP BY on its own, including how the cluster executes it, see Elasticsearch GROUP BY in SQL. Window functions and joins cover the two features most often combined with the clauses here.