JOINs & Aggregations

JOINs

-- Inner join
SELECT u.name, o.total FROM users u JOIN orders o ON u.id = o.user_id;

-- Left join
SELECT u.name, o.total FROM users u LEFT JOIN orders o ON u.id = o.user_id;

-- Cross join
SELECT * FROM sizes CROSS JOIN colors;

-- Semi join (EXISTS)
SELECT * FROM users u WHERE EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.id);

-- Anti join (NOT EXISTS)
SELECT * FROM users u WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.id);

JOINs work across collections of different storage types — you can join a KV collection with a strict document collection.

Unqualified Column Names

An unqualified column in a join resolves to the one relation that has it. This applies to the SELECT list, WHERE, ON, GROUP BY, HAVING, ORDER BY, and aggregate arguments.

The column is…	Result
Declared by exactly one relation	Resolves to that relation
Declared by two or more relations	Refused: SQLSTATE `42702`
Declared by none, and exactly one relation is schemaless	Resolves to the schemaless one
Declared by none, and two or more relations are schemaless	Refused: SQLSTATE `42702`
Accepted by no relation	Refused: SQLSTATE `42703`

A declared column wins over a schemaless relation. A schemaless collection accepts any field name, so when two schemaless collections are joined, NodeDB cannot know which one holds a field. The query is refused rather than reading NULL from the wrong side:

ERROR:  column reference "region" is ambiguous
SQLSTATE: 42702

Qualify the column with its table name or alias:

-- Refused when both events and devices are schemaless
SELECT region, COUNT(*) FROM events e JOIN devices d ON e.device_id = d.id GROUP BY region;

-- Qualified: reads region from devices
SELECT d.region, COUNT(*) FROM events e JOIN devices d ON e.device_id = d.id GROUP BY d.region;

USING (col) names the column on both sides, so it is never ambiguous. Both sides must have the column.

Derived Tables

A subquery can be a join side. It works on either side and in every join type: inner, LEFT, RIGHT, FULL, and CROSS. It can also be any side of a multi-way join.

-- Join each customer to the total of their orders
SELECT c.region, t.total
FROM customers c
JOIN (SELECT cust, SUM(amount) AS total FROM orders GROUP BY cust) t ON c.id = t.cust;

-- A derived table on the FROM side, with its own WHERE, ORDER BY and LIMIT
SELECT c.id, o.id
FROM (SELECT id FROM customers WHERE region = 'east' ORDER BY id LIMIT 10) c
JOIN orders o ON o.cust = c.id;

-- Aggregate over a join with a derived side
SELECT c.region, SUM(t.total)
FROM customers c
JOIN (SELECT cust, SUM(amount) AS total FROM orders GROUP BY cust) t ON c.id = t.cust
GROUP BY c.region;

Alias: a derived side must have one. The alias qualifies its columns, so t.total reads the column the subquery projects. An unqualified column resolves to the derived side when that side is the only relation that projects it, as described above.
Access rules: row-level security and redaction apply to the collections the subquery reads.
No correlation: a derived side cannot refer to another relation of the same FROM clause. Such a query is refused with SQLSTATE 42601. Use JOIN LATERAL, described below, to correlate a subquery with the rows before it:

ERROR:  invalid reference to FROM-clause entry for table "c": a subquery in FROM cannot refer to other relations of the same query level; write JOIN LATERAL (SELECT ...) to correlate it
SQLSTATE: 42601

LATERAL Joins

LATERAL allows the right-hand subquery to reference columns from the outer table. See Lateral Subqueries for the full reference. Quick example:

-- Top 3 most recent orders for each user
SELECT u.id, u.name, o.id AS order_id, o.created_at
FROM users u
JOIN LATERAL (
    SELECT id, created_at FROM orders WHERE user_id = u.id
    ORDER BY created_at DESC LIMIT 3
) AS o ON true;

-- LEFT JOIN LATERAL — preserve users with no orders
SELECT u.id, o.last_order_at
FROM users u
LEFT JOIN LATERAL (
    SELECT MAX(created_at) AS last_order_at FROM orders WHERE user_id = u.id
) AS o ON true;

Aggregate Functions

Function	Description
`COUNT(*)`	Row count
`COUNT(DISTINCT col)`	Distinct count
`SUM(col)`	Sum
`AVG(col)`	Average
`MIN(col)` / `MAX(col)`	Minimum / Maximum

Approximate Aggregates

Mergeable across shards, usable in continuous aggregation:

Function	Description
`approx_count_distinct(col)`	HyperLogLog
`approx_percentile(col, p)`	t-digest
`approx_topk(col, k)`	SpaceSaving

Weighted Random Selection

SELECT * FROM WEIGHTED_PICK('loot_table', weight => 'drop_rate', count => 1);
SELECT * FROM WEIGHTED_PICK('gacha_pool', weight => 'probability', count => 10, SEED => 'player:pull');