Joins

left.join(right, { on }) returns a new table with the columns of both, one row for every pair of rows whose keys match. It is a hash join: the right table’s keys go into a hash table, then every left row looks its key up. Both steps run on every core.

import { Engine } from 'vectrojs';

const engine = new Engine();
const orders = engine.table({
  order_id: [1, 2, 3, 4, 5, 6],
  cust:     [10, 20, 10, 30, 40, 20],
  price:    [120, 80, 45, 300, 99, 15],
});
const customers = engine.table({
  cust: [10, 20, 30, 50],
  name: ['Ada', 'Bo', 'Cy', 'Di'],
});

console.log(orders.join(customers, { on: 'cust' }).toRows());
await engine.close();

Order 5 has no customer 40, so an inner join leaves it out. Customer 50 has no orders and doesn’t appear either.

Options

Option	Values	Default
on	a column both tables have ('cust'), several (['region', 'day']), or different names ({ left: 'cust', right: 'id' })	required
how	'inner' or 'left'	'inner'
suffix	added to a right column whose name the result already has	'_right'

joinAsync takes the same arguments and runs without blocking the calling thread.

Left joins

With how: 'left' every left row is kept. A left row with no match appears once, with NaN in the right table’s number columns and '' in its text columns. Those are Vectro’s missing values (see missing values).

import { Engine } from 'vectrojs';

const engine = new Engine();
const orders = engine.table({ order_id: [1, 2, 3], cust: [10, 40, 20], price: [120, 99, 80] });
const customers = engine.table({ cust: [10, 20], name: ['Ada', 'Bo'], price: [1, 2] });

const joined = orders.join(customers, { on: 'cust', how: 'left', suffix: '_list' });
console.log(joined.columnNames);
console.log(joined.toRows());
await engine.close();

Both tables have a price column, so the right one comes back as price_list. Without suffix it would be price_right.

Which columns come back

• All the left table’s columns first, in their order, then the right table’s.
• A right key column with the same name as its left key is left out, since it holds the same values. With on: { left: 'cust', right: 'id' } both cust and id are kept.
• Any other right column whose name is taken gets the suffix (added again until the name is free).

Several keys, text keys, repeated keys

import { Engine } from 'vectrojs';

const engine = new Engine();
const sales = engine.table({
  region: ['EU', 'US', 'EU', 'APAC', 'US'],
  day:    [1, 1, 2, 2, 2],
  units:  [5, 3, 7, 2, 4],
});
const targets = engine.table({ region: ['US', 'EU', 'EU'], day: [2, 1, 2], goal: [5, 4, 6] });
console.log(sales.join(targets, { on: ['region', 'day'] }).toRows());

// one region, two managers: each EU sale appears twice
const managers = engine.table({ region: ['EU', 'EU', 'US'], manager: ['Ana', 'Ben', 'Cal'] });
console.log(sales.join(managers, { on: 'region' }).count());
await engine.close();

• Text keys match by label. Each table has its own dictionary of labels, so the codes differ; the join compares the text. A key must be text on both sides or numbers on both sides. Free-text columns (long unique strings loaded as string) can’t be join keys; load them as categories.
• Repeated right keys give one result row per match. Rows come in left-table order, each left row followed by its matches in right-table order.
• Missing keys never match, as NULL doesn’t in SQL. A left join keeps such a row with the right columns empty.

Put the smaller table on the right

The right table is the one that gets hashed, so the hash table stays small when the right table is small (customers, products, a lookup list). The left table is only read. Joining 10M orders to 100K customers, measured against DuckDB on the same machine (compare/bench-join.mjs, joined table built in full, answers checked):

Join	Vectro	DuckDB
× 100K customers	65 ms	222 ms
× 1M accounts	135 ms	255 ms
left join, half the keys match	76 ms	191 ms
two keys: region + day (2,920 rows)	64 ms	167 ms

Both tables must belong to the same engine and use the same number type (both Float64Array or both Float32Array).

Filters before and after

A filtered table can be joined directly; only its matching rows take part. If you join first and filter later, the join builds every row first. A lazy query fixes that for you: it moves each filter into the side of the join whose columns it uses, and carries only the columns used later.

import { Engine } from 'vectrojs';

const engine = new Engine();
const sales = engine.table({ region: ['EU', 'US', 'EU', 'APAC'], units: [5, 3, 7, 2], cust: [1, 2, 2, 3] });
const customers = engine.table({ cust: [1, 2, 3], tier: [1, 2, 1] });

const q = sales.lazy()
  .join(customers, { on: 'cust' })
  .filter('tier == 1 && units > 2')
  .select('region', 'units');
console.log(q.explain());
console.log(q.toRows());
await engine.close();

The plan shows units > 2 applied to sales and tier == 1 applied to customers before the join. In the README’s benchmark, a join followed by a filter over 10M orders went from 144 ms step by step to 22 ms as a lazy query.

Joins in SQL

sql() supports [INNER] JOIN, LEFT [OUTER] JOIN, RIGHT [OUTER] JOIN, FULL [OUTER] JOIN and CROSS JOIN, with ON a.x = b.y [AND ...] or USING (x, ...). Tables are given aliases in the usual way. See SQL for the rest.

import { Engine, sql } from 'vectrojs';

const engine = new Engine();
const orders = engine.table({ order_id: [1, 2, 3, 4], cust: [10, 20, 10, 40], price: [120, 80, 45, 99] });
const customers = engine.table({ cust: [10, 20, 30], name: ['Ada', 'Bo', 'Cy'] });

console.log(sql(`
  SELECT c.name, COUNT(o.order_id) AS orders, SUM(o.price) AS spend
  FROM orders o RIGHT JOIN customers c ON o.cust = c.cust
  GROUP BY c.name ORDER BY c.name`, { orders, customers }).toRows());

console.log(sql(`
  SELECT cust, order_id, name
  FROM orders FULL JOIN customers USING (cust)
  ORDER BY cust`, { orders, customers }).toRows());
await engine.close();

RIGHT and FULL joins are worked out as the matching rows (for FULL, plus the left rows without a match), followed by the right rows whose keys no left row has. The join marks which right rows it matched while it probes, so the unmatched ones come from the same pass, with no second join. On 10M rows the README measures a RIGHT JOIN with a group-by at 27 ms (DuckDB 54) and a FULL JOIN with a count at 17 ms (DuckDB 26).

Commas between tables (FROM a, b) are refused; write JOIN ... ON or CROSS JOIN.

Limits

• table.join does inner and left joins; right and full joins are SQL only.
• Joins work on normal tables. Split tables and streams can’t be joined yet.
• On a dataset a join on text keys needs its other side to fit in memory.