Joins | Atoll Book

Join model and derived-table rows with checked conditions and qualified columns.

A join adds a second row source to a read. Name the models, state the condition, and project the columns you want out of the combined row.

schema shop

model Customer {
    @id
    id: int
    name: string
}

model Order {
    @id
    id: int
    customer_id: int
    total: float
}

fn orders_with_names(): []{ order_id: int, customer_name: string } ! QueryError {
    return FROM Order
        JOIN Customer ON Order.customer_id == Customer.id
        SELECT { order_id: Order.id, customer_name: Customer.name }?
}

fn main(): void {
    match orders_with_names() {
        Ok(rows) => {
            for row in rows {
                println("order ${row.order_id} — ${row.customer_name}")
            }
        }
        Err(e) => println("query failed")
    }
}

Three rules do most of the work here:

The ON expression is checked in a scope containing both sources, so Order.customer_id and Customer.id are columns, not Atoll locals.
Qualify every field once two sources are in scope. id alone is ambiguous when both models have one.
The row type is the projection. []{ order_id: int, customer_name: string } is an anonymous record list — write it out to pin the contract, or let it infer.

A join needs a projection

A joined row is neither an Order nor a Customer, so there is no carrier for it unless you say what the result row looks like. Omitting SELECT is a compile error, not a silent SELECT *:

schema shop

model Customer {
    @id
    id: int
    name: string
}

model Order {
    @id
    id: int
    customer_id: int
}

fn broken(): void ! QueryError {
    rows := FROM Order
        JOIN Customer ON Order.customer_id == Customer.id?
    println("${rows.len()}")
}

That reports ATOLL3244: this FROM query cannot be lowered to SQL. Add a SELECT { ... } naming the columns and it compiles.

Qualified columns need an explicit name

Inside a SELECT { ... }, bare-field shorthand accepts only a plain identifier. Order.id is a path, not a name, so the projection field has to be spelled alias: Qualifier.column:

schema shop

model Customer {
    @id
    id: int
    name: string
}

model Order {
    @id
    id: int
    customer_id: int
}

fn broken(): void ! QueryError {
    // `Order.id` and `Customer.name` need names: `id: Order.id`, ...
    rows := FROM Order
        JOIN Customer ON Order.customer_id == Customer.id
        SELECT { Order.id, Customer.name }?
    println("${rows.len()}")
}

The parser reads Order as the field name and then trips on the ., so you get a cascade — ATOLL1006 Expected '}' followed by resolution errors. Naming each field fixes all of them at once.

Join kinds

Three condition-bearing spellings exist, plus the unconditioned cross join:

Atoll	Meaning
`JOIN Model ON cond`	inner join — matching pairs only
`LEFT JOIN Model ON cond`	keeps every left row
`RIGHT JOIN Model ON cond`	keeps every right row
`CROSS JOIN Model`	every pair, no condition

There is no INNER JOIN keyword. JOIN already means inner, and INNER parses as an ordinary identifier — which then fails to resolve:

schema shop

model Customer {
    @id
    id: int
    name: string
}

model Order {
    @id
    id: int
    customer_id: int
}

fn broken(): void ! QueryError {
    rows := FROM Order
        INNER JOIN Customer ON Order.customer_id == Customer.id
        SELECT { id: Order.id }?
    println("${rows.len()}")
}

Here are all four forms side by side:

schema shop

model Customer {
    @id
    id: int
    name: string
    region: string
}

model Order {
    @id
    id: int
    customer_id: int
    total: float
}

// Only orders that have a customer row.
fn matched(): []{ id: int, name: string } ! QueryError {
    return FROM Order
        JOIN Customer ON Order.customer_id == Customer.id
        SELECT { id: Order.id, name: Customer.name }?
}

// Every order, even one whose customer row is missing.
fn all_orders(): []{ id: int, name: string } ! QueryError {
    return FROM Order
        LEFT JOIN Customer ON Order.customer_id == Customer.id
        SELECT { id: Order.id, name: Customer.name }?
}

// Every customer, even one with no orders.
fn all_customers(): []{ id: int, name: string } ! QueryError {
    return FROM Order
        RIGHT JOIN Customer ON Order.customer_id == Customer.id
        SELECT { id: Customer.id, name: Customer.name }?
}

// Every (order, customer) pair — a deliberate cartesian product.
fn every_pair(): []{ id: int, region: string } ! QueryError {
    return FROM Order
        CROSS JOIN Customer
        SELECT { id: Order.id, region: Customer.region }?
}

fn main(): void {
    match matched() { Ok(rows) => println("matched ${rows.len()}") Err(e) => println("failed") }
    match all_orders() { Ok(rows) => println("orders ${rows.len()}") Err(e) => println("failed") }
    match all_customers() { Ok(rows) => println("customers ${rows.len()}") Err(e) => println("failed") }
    match every_pair() { Ok(rows) => println("pairs ${rows.len()}") Err(e) => println("failed") }
}

CROSS JOIN takes no ON clause; its result is left_rows * right_rows rows. Reach for it only when that is what you want.

Projection types follow the column, not the join

A projection field has exactly the type its source column was declared with. A LEFT JOIN does not widen the right-hand fields to T?, so annotating the result that way is a type error:

schema shop

model Customer {
    @id
    id: int
    name: string
}

model Order {
    @id
    id: int
    customer_id: int
}

// `Customer.name` is `string`; the join does not make it `string?`.
fn broken(): []{ id: int, name: string? } ! QueryError {
    return FROM Order
        LEFT JOIN Customer ON Order.customer_id == Customer.id
        SELECT { id: Order.id, name: Customer.name }?
}

This is a real gap, not a design choice: an unmatched left row genuinely has no right-hand value at runtime, but the type system does not yet model it. Today the only way to get an optional projection field is to declare the column optional on the model. Then the projection is optional too, and ?? reads naturally:

schema shop

model Customer {
    @id
    id: int
    name: string?
}

model Order {
    @id
    id: int
    customer_id: int
}

fn with_optional_name(): []{ id: int, name: string? } ! QueryError {
    return FROM Order
        LEFT JOIN Customer ON Order.customer_id == Customer.id
        SELECT { id: Order.id, name: Customer.name }?
}

fn main(): void {
    match with_optional_name() {
        Ok(rows) => {
            for row in rows {
                println("${row.id}: ${row.name ?? "(unknown customer)"}")
            }
        }
        Err(e) => println("query failed")
    }
}

Filtering, grouping, and ordering a join

Every other read clause works over the joined row. Qualify the columns exactly as in ON, and compare booleans explicitly — a bare qualified boolean column is not currently recognised as a WHERE predicate on its own.

schema shop

model Customer {
    @id
    id: int
    name: string
    active: bool
}

model Order {
    @id
    id: int
    customer_id: int
    total: float
}

fn big_active_orders(
    floor: float,
): []{ id: int, name: string, total: float } ! QueryError {
    return FROM Order
        JOIN Customer ON Order.customer_id == Customer.id
        WHERE Customer.active == true && Order.total >= floor
        SELECT { id: Order.id, name: Customer.name, total: Order.total }
        ORDER BY Order.total DESC
        LIMIT 25?
}

fn main(): void {
    match big_active_orders(250.0) {
        Ok(rows) => {
            for row in rows {
                println("${row.name} spent ${row.total}")
            }
        }
        Err(e) => println("query failed")
    }
}

Note the clause order: SELECT comes before ORDER BY and LIMIT, matching the order the query grammar reads them in.

Grouping works over qualified columns too, which is how you collapse a one-to-many join back down to one row per parent:

schema shop

model Customer {
    @id
    id: int
    name: string
    region: string
}

model Order {
    @id
    id: int
    customer_id: int
    total: float
}

fn revenue_by_region(): []{ region: string, revenue: float } ! QueryError {
    return FROM Order
        JOIN Customer ON Order.customer_id == Customer.id
        GROUP BY Customer.region
        SELECT { region: Customer.region, revenue: sum(Order.total) }?
}

fn main(): void {
    match revenue_by_region() {
        Ok(rows) => {
            for row in rows {
                println("${row.region}: ${row.revenue}")
            }
        }
        Err(e) => println("query failed")
    }
}

Cardinality

A join changes the row count, and the compiler will not warn you about it. If each order has exactly one customer, the inner join above returns one row per order. If each customer has many orders, joining from Customer duplicates the customer’s columns once per order — so a sum() over a customer column would count it several times.

Form	Unmatched left rows	Unmatched right rows
`JOIN`	dropped	dropped
`LEFT JOIN`	kept, right columns null	dropped
`RIGHT JOIN`	dropped	kept, left columns null
`CROSS JOIN`	every pair	every pair

Predicate placement matters for outer joins. A right-side condition written in ON decides which right rows match while keeping every left row; the same condition written in WHERE runs after the join and discards the null-extended rows, quietly turning a left join into an inner one.

Relation joins

A BELONGS TO ... VIA relation on the model supplies the condition, so ON can be omitted:

schema shop

model Customer {
    @id
    id: int
    name: string
}

model Order {
    @id
    id: int
    customer_id: int
    total: float
    BELONGS TO customer: Customer VIA customer_id
}

fn orders_with_names(): []{ id: int, name: string } ! QueryError {
    return FROM Order
        JOIN Customer
        SELECT { id: Order.id, name: Customer.name }?
}

fn main(): void {
    match orders_with_names() {
        Ok(rows) => println("${rows.len()} orders")
        Err(e) => println("query failed")
    }
}

Drop the BELONGS TO line and there is nothing left to synthesise the condition from. The diagnostic is the generic backstop rather than anything that mentions relations, so recognise it: a bare JOIN Model that reports ATOLL3244 almost always means the relation is missing.

schema shop

model Customer {
    @id
    id: int
    name: string
}

model Order {
    @id
    id: int
    customer_id: int
}

// No `BELONGS TO` on `Order`, so `JOIN Customer` has no condition to build.
fn broken(): []{ id: int, name: string } ! QueryError {
    return FROM Order
        JOIN Customer
        SELECT { id: Order.id, name: Customer.name }?
}

Write the ON clause explicitly whenever the relation is absent, or whenever two declared relations could connect the same pair of models. A relation is metadata for condition synthesis and DDL — declaring one never loads rows on its own.

Derived tables

A parenthesised read can be joined as a source of its own. Give it a name with AS, and its projected columns become that name’s columns:

schema shop

model Customer {
    @id
    id: int
    name: string
}

model Order {
    @id
    id: int
    customer_id: int
    total: float
}

fn customers_with_totals(): []{ name: string, paid: float } ! QueryError {
    return FROM Customer
        JOIN (
            FROM Order
            GROUP BY customer_id
            SELECT { customer_id, paid: sum(total) }
        ) AS totals
        ON Customer.id == totals.customer_id
        SELECT { name: Customer.name, paid: totals.paid }?
}

fn main(): void {
    match customers_with_totals() {
        Ok(rows) => {
            for row in rows {
                println("${row.name} paid ${row.paid}")
            }
        }
        Err(e) => println("query failed")
    }
}

This is the standard fix for the duplication problem above: aggregate the many side first, then join one row per parent. Only the derived query’s projected names escape — a column the inner SELECT does not name is invisible outside the parentheses.

The AS alias is required on a derived-table join. Plain model joins do not take an alias; a model is referred to by its own name.

What the compiler rejects

A conditional join is rejected, because including or excluding a join changes row cardinality and cannot be expressed as a bound parameter:

schema shop

model Customer {
    @id
    id: int
    name: string
}

model Order {
    @id
    id: int
    customer_id: int
}

fn broken(with_names: bool): void ! QueryError {
    rows := FROM Order
        JOIN Customer ON Order.customer_id == Customer.id if with_names
        SELECT { id: Order.id }?
    println("${rows.len()}")
}

That is ATOLL3242. When a caller genuinely needs two different row shapes, write two queries and pick between them with an ordinary if.

Join order and index selection stay with the database planner. The compiler checks the shape of the join and the types flowing out of it; it makes no promise about the physical plan.

A worked example

schema shop

model Customer {
    @id
    id: int
    name: string
    region: string
}

model Order {
    @id
    id: int
    customer_id: int
    total: float
    status: string
    BELONGS TO customer: Customer VIA customer_id
}

/// One row per customer in `region`, with their settled lifetime spend.
/// Customers with no settled orders still appear.
fn spend_report(region: string): []{ name: string, spend: float } ! QueryError {
    return FROM Customer
        LEFT JOIN (
            FROM Order
            WHERE status == "settled"
            GROUP BY customer_id
            SELECT { customer_id, spend: sum(total) }
        ) AS settled
        ON Customer.id == settled.customer_id
        WHERE Customer.region == region
        SELECT { name: Customer.name, spend: settled.spend }
        ORDER BY Customer.name?
}

fn main(): void {
    match spend_report("emea") {
        Ok(rows) => {
            mut total := 0.0
            for row in rows {
                println("${row.name}: ${row.spend}")
                total = total + row.spend
            }
            println("region total ${total}")
        }
        Err(e) => println("spend report failed")
    }
}

Continue with Aggregates for GROUP BY and HAVING in depth, and Projections for the row shapes a SELECT can produce.