Skip to content

RonSQL Join Queries#

RonSQL executes join queries as pushed joins: the entire join tree is evaluated inside the RonDB data nodes, close to the data, and only the final result rows are returned to the client. Two families of join queries are supported:

  • Aggregate join queries compute COUNT, SUM, MIN, MAX and AVG over the join result, with optional GROUP BY and HAVING. The aggregation is also pushed into the data nodes, so only the (typically small) aggregated result leaves the cluster.

  • Projection-only join queries return plain column values from the joined tables, without aggregation. Result rows stream back in batches, so large results are supported.

Both families support WHERE, ORDER BY and LIMIT. This chapter covers joins between stored tables. Aggregating common table expressions (CTEs) can participate in joins as if they were tables; the rules specific to CTEs are described in the CTE chapter.

All tables of a join query are read from the database given in the request; cross-database joins are not supported. A table name can be qualified with that database, but a qualifier naming another database is rejected.

The examples in this chapter use the following schema:

CREATE TABLE nation (
  n_id        INT NOT NULL,
  n_name      CHAR(25) NOT NULL,
  PRIMARY KEY (n_id)
) ENGINE=NDB;

CREATE TABLE customer (
  c_id        INT NOT NULL,
  c_nationkey INT NOT NULL,
  c_segment   CHAR(10) NOT NULL,
  c_acctbal   DECIMAL(12,2) NOT NULL,
  PRIMARY KEY (c_id),
  INDEX idx_c_nationkey (c_nationkey)
) ENGINE=NDB;

CREATE TABLE orders (
  o_id        INT NOT NULL,
  o_custkey   INT NOT NULL,
  o_clerk     INT,
  o_total     DECIMAL(12,2) NOT NULL,
  o_priority  INT NOT NULL,
  PRIMARY KEY (o_id),
  INDEX idx_o_custkey (o_custkey),
  INDEX idx_o_cust_prio (o_custkey, o_priority)
) ENGINE=NDB;

Join syntax#

A join query names a first table in the FROM clause and then joins further tables one at a time, each with an ON clause:

SELECT o.o_id, c.c_segment, n.n_name
FROM orders AS o
JOIN customer AS c ON c.c_id = o.o_custkey
JOIN nation AS n ON n.n_id = c.c_nationkey
WHERE o.o_id < 1000;

The supported join forms are:

  • JOIN ... ON ... -- an inner join. Note that unlike MySQL, the spelling INNER JOIN is not accepted; write plain JOIN, which is always an inner join.

  • LEFT JOIN ... ON ... and LEFT OUTER JOIN ... ON ... -- a left outer join.

Each ON clause consists of one or more equality conditions combined with AND. Every condition must be an equality between two table-qualified column references, where the left-hand side is a column of the table being joined and the right-hand side is a column of a table named earlier in the query:

JOIN customer AS c ON c.c_id = o.o_custkey

Note that RonSQL is stricter than MySQL here: the joined table’s column must be written on the left-hand side of each equality, both sides must be qualified with a table name or alias, and nothing other than these column-to-column equalities is accepted in ON (no constants, non-equality comparisons, OR, or expressions). Conditions that filter individual tables belong in the WHERE clause instead.

Multiple equalities in one ON clause form a multi-column join key, with at most 8 key columns per join. The earlier table referenced by an ON clause can be any previously named table, not just the immediately preceding one; this is what allows both chain- and star-shaped joins (see below). Referencing a table that appears later in the query, or writing the columns of an equality in the other order, is rejected. For aggregate queries the error is:

Join condition references unknown table 't'. Tables must be
joined in order, each referencing a previously defined table.

and for projection-only queries it is the general message beginning “This query has no aggregate expression, so it is not an aggregate query”.

Tables can be given aliases with AS; the AS keyword is required (FROM orders o is a syntax error). The same table may appear several times under different aliases (for example a self-join), but every alias must be unique; a duplicate alias is rejected with Not unique table/alias: '...'. Outside the ON clauses, column names need not be qualified when they are unambiguous among all tables of the query; an ambiguous name is rejected with Ambiguous column '...' found in multiple tables or CTEs. Use 'table.column' syntax.

A comma-separated FROM list (FROM a, b) is accepted by the grammar, but such a cross join is only supported when the joined operand is a scalar aggregating CTE or a single-row key-lookup CTE (which produce at most one row); see the CTE chapter. Comma cross-joins of stored tables are not supported.

The following MySQL join syntax is not supported and results in an error: RIGHT JOIN, FULL joins, NATURAL joins, STRAIGHT_JOIN, CROSS JOIN, the USING (...) clause, parenthesized join expressions, and subqueries (derived tables) in the FROM clause. A flat join list is interpreted with the standard SQL left-associative semantics.

Requirements on join columns#

Pushed joins execute each joined table as a lookup or an index scan driven by the join key. This places three requirements on the join columns, each enforced with a clear error at query preparation time.

The joined table must be accessible by the join key#

The columns of the joined table that appear in the ON clause must match one of the following, which RonSQL checks in order:

  1. The complete primary key of the joined table. The join then executes as a primary key lookup per parent row.

  2. The complete column set of a unique index. The join executes as a unique index lookup.

  3. The leading columns (prefix) of an ordered index. The join executes as an index scan per parent row, so one parent row can match many rows in the joined table.

The ON equalities may be written in any order; RonSQL matches them against the index regardless of order. If no primary key, unique index or ordered index matches, the query is rejected:

Cannot push join: no suitable index on join columns for table
'orders'. Create a primary key, unique index, or ordered
index on the join columns.

Join column types must be identical#

The two columns of each ON equality must have identical declarations: the same type, signedness, precision, scale, length and character set. Pushed joins compare key values inside the data nodes and do not perform implicit type conversion. For example, joining an INT column to a TINYINT or BIGINT column is rejected:

Join columns 'c_id' and 'r_key' must have identical type,
precision, scale, length and character set: NDB pushed joins
do not convert linked values.

Additionally, BLOB and TEXT columns can never be join columns, on either side of the equality:

BLOB/TEXT columns cannot be used as join columns.

Join keys from several tables must lie on one ancestor chain#

The equalities of a single ON clause may reference different earlier tables. This is supported as long as all referenced tables lie on a single ancestor chain in the join tree -- that is, each referenced table is joined (directly or indirectly) to another referenced table. For example:

SELECT ...
FROM t1
JOIN t2 ON t2.a = t1.a
JOIN t3 ON t3.a = t1.x AND t3.b = t2.y;

Here t3’s join key combines columns from t1 and t2, which is fine because t2 is joined to t1. If the referenced tables sit on unrelated branches of a star-shaped join, the query is rejected:

ON conditions reference tables from unrelated join branches.
Multi-table ON conditions are supported only when the
referenced tables lie on a single ancestor chain.

Supported join topologies#

Joins can form chains, stars, and combinations of the two:

  • Chains: each table joins to the previously joined table, as in the orders to customer to nation example above. Chains may mix lookups and index scans at any position.

  • Stars: several tables join to the same parent table, including several joins on the same parent column, and self-joins where one table joins the same parent twice under different aliases via different key columns.

  • Combinations: a star branch can continue as a chain, and a chain can fan out into a star at any table.

Projection-only join queries support all of these topologies. Aggregate join queries are more restricted, since the aggregation is performed at the last table of the join; see Aggregate join queries.

-- Star: two customer lookups from the same orders row
SELECT o.o_id, c1.c_segment, c2.c_segment
FROM orders AS o
JOIN customer AS c1 ON c1.c_id = o.o_custkey
JOIN customer AS c2 ON c2.c_id = o.o_clerk
WHERE o.o_id < 100;

When two index-scan joins share the same parent, the result contains the cross product of the matching rows per parent row, exactly as in MySQL for the same query. Keep this in mind when both branches can match many rows.

The size of a pushed join is limited to 32 operations per query, where each table counts as one operation except a unique index lookup, which counts as two. Exceeding the limit is rejected with:

Pushed join too large: 34 internal operations exceed the NDB
limit of 32 (a unique-index lookup counts as 2).

EXPLAIN prints the join plan: one line per operation showing the access type (PK_LOOKUP, UNIQUE_LOOKUP, INDEX_SCAN, TABLE_SCAN, and CTE_LOOKUP or CTE_SCAN for CTEs), the join type ([ROOT], [INNER], [LEFT JOIN], and [SEMI] or [ANTI] for rewritten subqueries), the parent table each operation is joined to, the chosen index, the join key equalities, and the routing of WHERE conditions into index bounds and pushed filters. This is the easiest way to confirm how a join query will execute.

LEFT JOIN semantics#

LEFT JOIN follows MySQL semantics. A row from the left side that has no match in the joined table is kept, with the joined table’s columns returned as NULL. In aggregate queries, aggregates over the joined table’s columns see NULL for such rows (so COUNT(col) does not count them), while COUNT(*) counts the row itself.

If the join key on the left side is nullable, rows with a NULL key never match: an inner join drops them and a LEFT JOIN keeps them NULL-extended, as in MySQL.

-- Orders keep their row even when o_clerk is NULL
-- or names no customer:
SELECT o.o_id, o.o_clerk, c.c_segment
FROM orders AS o
LEFT JOIN customer AS c ON c.c_id = o.o_clerk
WHERE o.o_id < 100;

A WHERE condition on a column of the left-joined table that can never be true for NULL (for example c.c_acctbal > 100) reduces the LEFT JOIN to an inner join, since it rejects every NULL-extended row. RonSQL applies this standard rewrite automatically; the results match MySQL.

This also applies to LIKE conditions, and to NOT over a comparison, a LIKE or an IS NULL condition. Other WHERE conditions on the columns of a left-joined stored table, that is conditions that can be true for NULL such as c.c_segment IS NULL or c.c_acctbal > 100 OR c.c_acctbal IS NULL, are not supported and are rejected with WHERE on a LEFT JOIN's right side that holds for NULL is not supported. In particular, the anti-join pattern LEFT JOIN t ... WHERE t.col IS NULL is only supported when the left-joined operand is a CTE; see the CTE chapter.

RonSQL parses a join list left-associatively, as standard SQL requires. Consequently, when an inner join’s ON clause references a table that was left-joined earlier in the list, the earlier LEFT JOIN effectively becomes an inner join: the inner join’s equality condition rejects the NULL-extended rows. RonSQL implements this by rewriting such LEFT JOINs to inner joins before execution (visible in EXPLAIN as [INNER]), again matching MySQL results:

-- The trailing inner join makes the LEFT JOIN effectively
-- inner: rows where c has no match cannot satisfy
-- n.n_id = c.c_nationkey.
SELECT o.o_id, c.c_id, n.n_name
FROM orders AS o
LEFT JOIN customer AS c ON c.c_id = o.o_clerk
JOIN nation AS n ON n.n_id = c.c_nationkey;

Chains of only LEFT JOINs are unaffected by this rewrite and keep their outer-join semantics.

WHERE clauses on join queries#

WHERE conditions on join queries execute inside the data nodes. How a condition executes depends on which tables it references; EXPLAIN shows the outcome for each condition.

Conditions on the first table#

Conditions on columns of the first (root) table are used to choose the access type of the root: in a projection-only query, a full primary key equality executes the root as a single primary key lookup; equalities and ranges on the leading columns of an ordered index execute as index bounds; an IN list on an index column executes as a scan of several ranges, one per distinct value; everything else becomes a pushed filter evaluated during the scan. Index bounds avoid reading non-matching rows altogether, while filters discard rows after reading them, so an index matching the WHERE conditions on the first table is valuable.

MySQL-style index hints -- FORCE INDEX (...), USE INDEX (...) and IGNORE INDEX (...) -- are honored on the first table of a join query. Index hints on joined tables are rejected:

Index hints (FORCE/USE/IGNORE INDEX) are only supported on the
root table of a query or CTE body, not on a joined table.

Conditions on a joined table#

A condition that only references columns of one joined table is pushed to that table’s operation. When the condition is an equality or range on a NOT NULL column that continues the joined table’s chosen index directly after the join key columns, it becomes an additional index bound; RonSQL may also switch the joined table to a different matching index when that allows more conditions to become bounds. Other conditions execute as a pushed filter on that table (shown as Residual filter: yes in EXPLAIN).

-- o_priority >= 3 continues idx_o_cust_prio after the join
-- key o_custkey, so it executes as an index bound:
SELECT c.c_id, o.o_id
FROM customer AS c
JOIN orders AS o ON o.o_custkey = c.c_id
WHERE c.c_id < 50 AND o.o_priority >= 3;

Conditions spanning tables#

Conditions comparing columns of two different tables are supported in two cases.

First, when a condition compares a column of a joined table that continues the table’s join key in its index with a column of an earlier table, it becomes an index bound of the join, in the same way as a condition with a constant (see above). This works in all join queries.

Second, in aggregate join queries, other conditions spanning two tables are evaluated inside the data nodes while aggregating. Each such condition must be a simple comparison (=, !=, <, <=, >, >=) where each side references at most one table, referencing at most two distinct tables overall; the sides can use +, - and * with integer literals. OR combinations of such comparisons are supported when every branch compares the same two tables. The column of the table that is not the last table of the join must have an integer type; otherwise the query is rejected with Unsupported linked column type in cross-table WHERE filter (integer columns only).

SELECT c.c_nationkey, COUNT(*)
FROM customer AS c
JOIN orders AS o ON o.o_custkey = c.c_id
WHERE o.o_clerk <> c.c_id
GROUP BY c.c_nationkey;

In projection-only join queries, other conditions spanning tables are not supported and are rejected:

Cross-table WHERE conditions (e.g., a.x > b.y) are only
supported in queries with aggregation (GROUP BY / aggregate
functions).  Split into separate conditions per table, or add
aggregation.

NULL semantics#

Filters follow SQL three-valued logic: a comparison involving a NULL value is unknown, and rows for which the WHERE clause does not evaluate to true are excluded. This matches MySQL, including for conditions pushed down as index bounds or filters on nullable columns.

Aggregate join queries#

Aggregate join queries compute COUNT(*), COUNT, SUM, MIN, MAX and AVG over the join result, entirely inside the data nodes. HAVING is supported on aggregate join queries, using aggregate expressions as described in the single-table chapter.

The aggregation is performed at the last table in the join list (marked ** Aggregation leaf ** in EXPLAIN), and the values of the other tables are passed down the join tree to it. Therefore, aggregate arguments and GROUP BY columns can reference columns of the last table and of the tables on its path back to the first table, but not of tables on other branches of a star. One query can mix aggregates over several tables of that path, and at most 16 columns of the tables other than the last one can be used in this way. Referencing a table on another branch is rejected with an error starting with Aggregation references a column from a join branch that is not an ancestor of the aggregation leaf. In practice, an aggregate join query should be a chain of stored tables, ordered so that the last table is the one with the most rows, possibly combined with CTEs joined to the same parent (see the CTE chapter). Star shapes with several joined stored tables are supported in projection-only queries.

SELECT c.c_nationkey, COUNT(*), SUM(o.o_total),
       MAX(c.c_acctbal)
FROM customer AS c
JOIN orders AS o ON o.o_custkey = c.c_id
WHERE o.o_priority >= 3
GROUP BY c.c_nationkey
HAVING SUM(o.o_total) > 1000
ORDER BY c_nationkey
LIMIT 10;

ORDER BY on an aggregate query can name GROUP BY columns (bare or table-qualified) and aliases of aggregate outputs, each with optional ASC or DESC. Ordering directly by an aggregate expression is a syntax error; give the aggregate an alias in the SELECT list and order by the alias:

SELECT c.c_nationkey, SUM(o.o_total) AS total
FROM customer AS c
JOIN orders AS o ON o.o_custkey = c.c_id
GROUP BY c.c_nationkey
ORDER BY total DESC, c_nationkey ASC
LIMIT 5;

Ordering by a column that is neither a GROUP BY column nor an aggregate alias is rejected (ORDER BY refers to column ... which is not in the GROUP BY clause). String ordering is collation-aware, and NULL values order first in ascending order, as in MySQL. LIMIT takes a single non-negative integer; the MySQL LIMIT offset, count form and OFFSET are not supported. LIMIT 0 returns an empty result.

Projection-only join queries#

Projection-only join queries select plain columns -- no aggregates and no expressions -- from any of the joined tables:

SELECT o.o_id, c.c_segment, n.n_name
FROM orders AS o
JOIN customer AS c ON c.c_id = o.o_custkey
JOIN nation AS n ON n.n_id = c.c_nationkey
WHERE o.o_priority = 5
LIMIT 100;

Result rows stream back in batches as the pushed join produces them, so results larger than memory are not a problem. With LIMIT and no ORDER BY, RonSQL stops as soon as the limit is reached and closes the scan early, even in the middle of a large result.

ORDER BY is supported on projection-only join queries and can name columns of any joined table, bare or table-qualified, with optional ASC/DESC -- including columns that are not in the SELECT list. Since the join result has no inherent order, RonSQL buffers the result rows on the client side, sorts them (collation-aware, NULL first in ascending order; columns of NULL-extended LEFT JOIN rows sort as NULL), and then applies any LIMIT after the sort. The sort buffer is capped at 1000000 rows or 256 MB; a larger result fails with an error suggesting to tighten the WHERE clause or use an aggregate query. For top-N queries over large tables, prefer an aggregate formulation or a selective WHERE. (Single-table queries can instead stream in index order without buffering; see the single-table chapter. This optimization does not apply to join queries.)

Joins involving CTEs#

An aggregating CTE defined in a WITH clause can be joined into the main query like a table, on both the aggregate and the projection-only path, and can also be the first table of the query. This enables two-level aggregations such as joining per-customer totals back to the customer table. The join key of a CTE is its GROUP BY column list, which acts as the CTE’s primary key. See the CTE chapter for the full rules.

Unsupported joins and restrictions#

The following are not supported in RonSQL join queries. Unless noted otherwise, each is rejected with a descriptive error message.

Syntax:

  • The INNER JOIN spelling (write JOIN), as well as RIGHT JOIN, FULL joins, NATURAL joins, STRAIGHT_JOIN and CROSS JOIN (all syntax errors).

  • The USING (...) clause; every join needs an ON clause.

  • Parenthesized join expressions; join lists are flat and left-associative.

  • Subqueries (derived tables) in the FROM clause; use a CTE instead.

  • ON conditions other than AND-combined equalities between table-qualified columns; the joined table’s column must be on the left-hand side.

  • Comma cross-joins, except of scalar aggregating CTEs and single-row key-lookup CTEs.

  • Table aliases without the AS keyword.

  • Tables in other databases than the one given in the request.

Join structure:

  • Joining to a table that has no primary key, unique index or ordered index matching the join columns.

  • Join columns with different type, signedness, precision, scale, length or character set; no implicit conversions are applied.

  • BLOB or TEXT columns as join columns.

  • ON clauses referencing tables named later in the query, or referencing tables on unrelated branches of the join tree.

  • Duplicate table aliases.

  • More than 32 operations per pushed join (a unique index lookup counts as 2), or more than 8 key columns in one ON clause.

  • In aggregate join queries: aggregates or GROUP BY columns referencing tables that are not on the path from the first to the last table of the join, and more than 16 such columns from tables other than the last one.

Query clauses:

  • WHERE conditions spanning two tables in projection-only join queries, unless they become index bounds (supported in aggregate join queries, with integer columns on the tables other than the last one).

  • WHERE conditions on a left-joined stored table that can be true for NULL, such as IS NULL.

  • Aliases in HAVING; use the aggregate expression.

  • Index hints on joined tables (honored only on the first table).

  • ORDER BY on expressions or column positions; only column names and output aliases are accepted. On aggregate queries, ordering is limited to GROUP BY columns and aggregate output aliases.

  • LIMIT with an offset (LIMIT offset, count or OFFSET).

  • ORDER BY on projection-only join queries whose result exceeds the client-side sort buffer (1000000 rows / 256 MB).

For general restrictions that apply to all RonSQL queries -- supported functions and operators, identifier rules, data type details -- see Restrictions and MySQL compatibility.