Joins

Joins add another row source to a read:

rows := FROM Booking
    JOIN Customer
        ON Booking.customer_id == Customer.id
    SELECT {
        booking_id: Booking.id
        customer_name: Customer.name
    }?

Qualify fields when both sources expose the same name.

The ON expression is checked in a scope containing both sources. Ordinary locals remain prepared parameters, while qualified members identify columns.

Join forms

The grammar supports:

• JOIN or INNER JOIN;
• LEFT JOIN;
• RIGHT JOIN;
• CROSS JOIN.

Inner, left, and cross forms have end-to-end coverage. Right joins render but have less runtime coverage and remain dialect-dependent. Cross joins have no ON condition and should be used deliberately.

An inner join keeps matching pairs. A left join keeps every left row and makes right-side values nullable when no match exists. Reflect that optionality in an explicit projection type.

Relation joins

A relation with an explicit VIA field can supply the condition:

model Booking {
    id: int
    customer_id: int
    BELONGS TO customer: Customer VIA customer_id
}

rows := FROM Booking
    JOIN Customer
    SELECT {
        booking_id: Booking.id
        customer_name: Customer.name
    }?

Implicit relation lookup must be unambiguous. Use an explicit ON clause when several relations could connect the same models.

Relations do not cause implicit loading outside a join. They supply metadata for condition synthesis and DDL where the supported relation shape is complete.

Derived tables

A braced or parenthesized read can be joined as a derived source. Its projection defines the columns visible outside:

rows := FROM Booking
    JOIN (
        FROM Payment
        GROUP BY booking_id
        SELECT {
            booking_id
            paid: sum(amount)
        }
    ) AS totals
    ON Booking.id == totals.booking_id
    SELECT { Booking.id, totals.paid }?

The alias is required to qualify derived columns outside the subquery. Only its projected names are visible; source-only fields inside the derived query do not escape.

Result shapes

Always project joined rows when the combined shape is not a declared model. This also makes optionality from a left join explicit in the result contract.

record BookingCustomer {
    booking_id: int
    customer_name: string?
}

Use a non-optional field only when the join form and database constraints guarantee a value.

Limits

Conditional JOIN ... if condition is rejected because it changes row cardinality and cannot be represented by a bound parameter. Many-to-many relation synthesis and schema-boundary validation are incomplete. Engine gates still apply to each form.

Join order and index selection remain database planning concerns. The compiler checks semantic shape but does not promise a particular physical plan.