Joins

ASTQL supports all standard SQL join types with type-safe ON conditions.

Join Types

Basic Join

users := instance.T("users", "u")
posts := instance.T("posts", "p")

result, _ := astql.Select(users).
    Fields(
        instance.WithTable(instance.F("username"), "u"),
        instance.WithTable(instance.F("title"), "p"),
    ).
    Join(posts, astql.CF(
        instance.WithTable(instance.F("id"), "u"),
        astql.EQ,
        instance.WithTable(instance.F("user_id"), "p"),
    )).
    Render()

// SELECT u."username", p."title"
// FROM "users" u
// INNER JOIN "posts" p ON u."id" = p."user_id"

Table Aliases

Aliases are required when the same field name exists in multiple tables:

users := instance.T("users", "u")     // Alias: u
orders := instance.T("orders", "o")   // Alias: o

// Disambiguate "id" field
userID := instance.WithTable(instance.F("id"), "u")      // u."id"
orderUserID := instance.WithTable(instance.F("user_id"), "o") // o."user_id"

LEFT JOIN

Returns all rows from the left table, with NULL for non-matching right rows:

result, _ := astql.Select(instance.T("users", "u")).
    Fields(
        instance.WithTable(instance.F("username"), "u"),
        instance.WithTable(instance.F("title"), "p"),
    ).
    LeftJoin(instance.T("posts", "p"), astql.CF(
        instance.WithTable(instance.F("id"), "u"),
        astql.EQ,
        instance.WithTable(instance.F("user_id"), "p"),
    )).
    Render()

// SELECT u."username", p."title"
// FROM "users" u
// LEFT JOIN "posts" p ON u."id" = p."user_id"

RIGHT JOIN

Returns all rows from the right table, with NULL for non-matching left rows:

result, _ := astql.Select(instance.T("users", "u")).
    RightJoin(instance.T("posts", "p"), joinCondition).
    Render()

// ... RIGHT JOIN "posts" p ON ...

FULL OUTER JOIN

Returns all rows from both tables, with NULL for non-matches:

result, _ := astql.Select(instance.T("users", "u")).
    FullOuterJoin(instance.T("posts", "p"), joinCondition).
    Render()

// ... FULL OUTER JOIN "posts" p ON ...

CROSS JOIN

Returns the Cartesian product (no ON clause):

result, _ := astql.Select(instance.T("sizes", "s")).
    Fields(
        instance.WithTable(instance.F("name"), "c"),
        instance.WithTable(instance.F("size"), "s"),
    ).
    CrossJoin(instance.T("colors", "c")).
    Render()

// SELECT c."name", s."size"
// FROM "sizes" s
// CROSS JOIN "colors" c

Multiple Joins

Chain multiple joins:

result, _ := astql.Select(instance.T("orders", "o")).
    Fields(
        instance.WithTable(instance.F("id"), "o"),
        instance.WithTable(instance.F("username"), "u"),
        instance.WithTable(instance.F("name"), "p"),
    ).
    Join(instance.T("users", "u"), astql.CF(
        instance.WithTable(instance.F("user_id"), "o"),
        astql.EQ,
        instance.WithTable(instance.F("id"), "u"),
    )).
    Join(instance.T("products", "p"), astql.CF(
        instance.WithTable(instance.F("product_id"), "o"),
        astql.EQ,
        instance.WithTable(instance.F("id"), "p"),
    )).
    Render()

// SELECT o."id", u."username", p."name"
// FROM "orders" o
// INNER JOIN "users" u ON o."user_id" = u."id"
// INNER JOIN "products" p ON o."product_id" = p."id"

Complex ON Conditions

Join conditions can use AND/OR logic:

onCondition := instance.And(
    astql.CF(
        instance.WithTable(instance.F("id"), "u"),
        astql.EQ,
        instance.WithTable(instance.F("user_id"), "p"),
    ),
    instance.C(
        instance.WithTable(instance.F("published"), "p"),
        astql.EQ,
        instance.P("is_published"),
    ),
)

result, _ := astql.Select(instance.T("users", "u")).
    LeftJoin(instance.T("posts", "p"), onCondition).
    Render()

// LEFT JOIN "posts" p ON (u."id" = p."user_id" AND p."published" = :is_published)

Self Joins

Join a table to itself using different aliases:

employees := instance.T("employees", "e")
managers := instance.T("employees", "m")

result, _ := astql.Select(employees).
    Fields(
        instance.WithTable(instance.F("name"), "e"),
        instance.WithTable(instance.F("name"), "m"),
    ).
    LeftJoin(managers, astql.CF(
        instance.WithTable(instance.F("manager_id"), "e"),
        astql.EQ,
        instance.WithTable(instance.F("id"), "m"),
    )).
    Render()

// SELECT e."name", m."name"
// FROM "employees" e
// LEFT JOIN "employees" m ON e."manager_id" = m."id"

Joins with COUNT

Joins work with COUNT queries:

result, _ := astql.Count(instance.T("users", "u")).
    Join(instance.T("orders", "o"), astql.CF(
        instance.WithTable(instance.F("id"), "u"),
        astql.EQ,
        instance.WithTable(instance.F("user_id"), "o"),
    )).
    Where(instance.C(
        instance.WithTable(instance.F("status"), "o"),
        astql.EQ,
        instance.P("status"),
    )).
    Render()

// SELECT COUNT(*) FROM "users" u
// INNER JOIN "orders" o ON u."id" = o."user_id"
// WHERE o."status" = :status

Join Validation

Tables in joins are validated against the schema:

// Valid: both tables exist in schema
astql.Select(instance.T("users", "u")).
    Join(instance.T("posts", "p"), condition)

// Invalid: unknown table
astql.Select(instance.T("users", "u")).
    Join(instance.T("nonexistent", "n"), condition)  // Panics