Qirava DMS docs

Reading: joins (nested-loop with index probing)

QQL supports inner joins with JOIN <table> ON <left> = <right>. Joins use a nested-loop strategy: for each left row, the engine probes the right table — using the right side's index on the join key when one exists (O(matches) per left row), otherwise scanning the right side — and re-checks the exact value. Matching right rows are merged into the left row with their fields prefixed by <table>.. Multiple right matches produce cartesian (one-to-many) result rows. This page covers the syntax, the probe strategy, and the merged-row field naming.

Join syntax#

JOIN <table> ON <left_field> = <right_field> joins the FROM table (left) to another table (right) on equality of two fields. The join is an inner join: only left rows with at least one matching right row survive. Multiple JOIN clauses are applied in order, each joining onto the running result. The join key uses a single =.

text

SELECT id FROM a JOIN b ON aid = bid WHERE id = 1

Probe strategy (index or scan)#

For each left row, the engine reads the left join value and probes the right table. If the right table has an equality index on the right join field, it looks up candidate positions through that index (O(matches) per left row); otherwise it scans the right rows. Either way the join value is re-compared exactly (values_eq) before a match is accepted, so an index-superset never produces a wrong row.

  for each LEFT row l:
      lv = l[left_field]
      if RIGHT has index on right_field:  candidates = index.lookup(lv)
      else:                               candidates = scan RIGHT
      for each r in candidates where r[right_field] == lv:
          emit merge(l, r)   # right fields prefixed "<right_table>."

Nested-loop join with index probing

Merged rows and table.-prefixed fields#

A matched right row's fields are appended to a clone of the left row, each renamed to <right_table>.<field> so left and right fields never collide. The left row keeps its original (unprefixed) field names, including its own _id.

Request

-- a: { k: 1, label: "x" } ; b: { k: 1, extra: "y" }
SELECT * FROM a JOIN b ON k = k WHERE k = 1

Response

{
  "error": null,
  "data": [ {
    "_id": 1, "k": 1, "label": "x",
    "b._id": 1, "b.k": 1, "b.extra": "y"
  } ],
  "root": { "count": 1 }
}

Cartesian matches and ordering#

If a left row matches N right rows, it produces N result rows (one-to-many fan-out).
A left row with zero right matches is dropped (inner-join semantics).
WHERE on the left FROM table narrows the left rows BEFORE the join is performed (the join runs on the already-filtered candidate set).
Joined queries use the materializing read path, so subsequent SORT/LIMIT/aggregate/RETURN apply to the merged result rows.