Skip to content

SQL joins ​

db.query<T> supports JOIN, INNER JOIN, LEFT JOIN, RIGHT JOIN, and FULL JOIN. LEFT OUTER JOIN, RIGHT OUTER JOIN, and FULL OUTER JOIN are also accepted. Each ON clause compares one column from the newly joined table with one from a previous table using =. Qualify a column when its name appears in more than one table.

JoinColumns that may be absent from a result row
JOIN or INNER JOINNewly joined table remains present; previous tables retain their earlier nullability
LEFT JOINNewly joined table
RIGHT JOINPrevious tables
FULL JOINPrevious tables and newly joined table

Declare a field from a side that may be absent as T?, even when that column is nonnullable in the table schema. With several joins, a later RIGHT JOIN or FULL JOIN can make earlier tables optional. A schema nullable column also requires T?. The checker does not infer a nonnull result from a WHERE predicate, so retain the optional field type when filtering joined rows.

Example ​

This example assumes the local schema snapshot contains db_examples(id: i64, label: string) and db_example_roles(id: i64, title: string).

tyrus
import { DbError, db } from "tyrus:db";

interface ExampleWithRole {
    label: string;
    title: string?;
}

async function listExamplesWithRoles(): Promise<Result<ExampleWithRole[], DbError>> {
    const rows = await db.query<ExampleWithRole>("""
        SELECT e.label AS label, r.title AS title 
        FROM db_examples AS e 
        LEFT JOIN db_example_roles AS r ON e.id = r.id 
        ORDER BY e.id
    """, []) onfail {
        return Err(data);
    };
    
    return Ok(rows);
}

SELECT * is accepted only when every requested result field maps to a unique column; use explicit projections and aliases when tables share names. CROSS JOIN and arbitrary expressions in ON are not supported yet. See SQL safety for binding rules.