Joins

There are two ways to reach a related record here, and they answer different questions.

A traversal follows an address somebody wrote down — an edge, a record link. See Graphs.

A join matches on a value where nobody wrote anything down. The value is the only thing the two sides share.

DEFINE COLLECTION users;
DEFINE COLLECTION orders;
CREATE users = { name: 'ada', city: 'london' };
CREATE users = { name: 'grace', city: 'new york' };
CREATE orders = { who: 'ada', total: 3 };
CREATE orders = { who: 'ada', total: 7 };
CREATE orders = { who: 'nobody', total: 1 };

One row per matching pair

SELECT * FROM users JOIN orders ON users.name = orders.who;

Two rows — ada has two orders. The two sides of ON may be written either way round.

A bare JOIN is inner

SELECT * FROM users JOIN orders ON users.city = orders.who;

No rows. grace has no order and nobody has no user, and neither appears. That is decided now rather than left open, so that adding LEFT later changes no answer anything already relies on.

A left record with nothing at the key matches nothing rather than matching everything:

SELECT * FROM users JOIN orders ON users.missing = orders.who;

A row is a record with two named sides

So nothing collides, however the two tables name their fields:

SELECT users.name AS who, orders.total AS spent
FROM users JOIN orders ON users.name = orders.who;

WHERE, ORDER BY and LIMIT all work over the composite row:

SELECT * FROM users JOIN orders ON users.name = orders.who WHERE orders.total > 5;

SELECT * FROM users JOIN orders ON users.name = orders.who
ORDER BY orders.total DESC LIMIT 1;

The join matches exactly what = matches

Including across number kinds:

CREATE users = { name: 'exact', qty: 3 };
CREATE orders = { who: 'exact', qty: 3.0 };
SELECT * FROM users JOIN orders ON users.qty = orders.qty;

One row. An integer 3 matches a float 3.0, because a number's equality here is its order — and a comparison that disagreed with the order its own index is stored in is the failure this store keeps refusing.

That has a real consequence for how a join may be implemented. A hash-keyed join would put 3 and 3.0 in different buckets and miss the pair with no error at all — the read would simply return one row fewer than it should, and nothing would say so. So the join agrees with = by construction rather than by coincidence.

An index changes the cost and not the answer

The whole assertion is the pair of statements: identical text, identical answer, and the access path is the only thing that moved.

SELECT * FROM users JOIN orders ON users.name = orders.who;
-- 3 rows, by scan

DEFINE INDEX by_who ON orders FIELDS who;

SELECT * FROM users JOIN orders ON users.name = orders.who;
-- 3 rows, through the index

And the cross-kind rule still holds through the index:

DEFINE INDEX by_qty ON orders FIELDS qty;
SELECT * FROM users JOIN orders ON users.qty = orders.qty;

Still one row. An index that ordered numbers differently from the comparison would break this, which is why the two are the same order.