Joins

Joins are available on select, update and delete builders. Examples use conn.Select().

On this page: Simple joins · Complex conditions · Subquery joins · Lateral joins · Dialect notes

Simple joins

conn.Select().From("users as u").
    Join("posts as p", "p.user_id", "u.id").                  // INNER JOIN ... ON "p"."user_id" = "u"."id"
    LeftJoin("profiles", "profiles.user_id", "=", "u.id").
    RightJoin("teams", "teams.id", "<>", "u.team_id").
    CrossJoin("sizes")

Column-to-column joins take either (first, second) or (first, operator, second).

A join other than CrossJoin with no condition fails with db.ErrJoinNeedsOn.

Complex conditions

Use the closure forms when one ON is not enough: JoinFunc, LeftJoinFunc, RightJoinFunc.

conn.Select().From("users").JoinFunc("contacts", func(j *db.JoinClause) {
    j.On("users.id", "contacts.user_id").
        OrOn("users.alt_id", "contacts.user_id").
        Where("contacts.kind", "primary").          // a bound value, not a column
        WhereNull("contacts.deleted_at").
        OnGroup(func(g *db.JoinClause) {
            g.Where("contacts.score", ">", 5).OrWhereIn("contacts.tag", []string{"x", "y"})
        })
})

JoinClause offers: On, OrOn, OnGroup, OrOnGroup, Where, OrWhere, WhereIn, OrWhereIn, WhereNotIn, OrWhereNotIn, WhereNull, OrWhereNull, WhereNotNull, OrWhereNotNull, WhereRaw, OrWhereRaw.

On compares two columns. Where inside a join compares a column to a bound value.

To join on a bound value in one call: JoinWhere, LeftJoinWhere, RightJoinWhere.

Subquery joins

latest := conn.Select().From("posts").
    Select("user_id").SelectRaw("max(created_at) as last").
    GroupBy("user_id")

conn.Select().From("users").JoinSub(latest, "l", "l.user_id", "users.id")   // also LeftJoinSub, RightJoinSub
conn.Select().From("users").CrossJoinSub(latest, "l")
conn.Select().From("users").JoinSubFunc(latest, "l", func(j *db.JoinClause) {
    j.On("l.user_id", "users.id")
})

The subquery is copied when added, so later changes to latest do not affect the join.

Lateral joins

top := conn.Select().From("posts").
    WhereColumn("posts.user_id", "users.id").
    OrderByDesc("views").
    Limit(1)

conn.Select().From("users").JoinLateral(top, "p")       // INNER JOIN LATERAL (...) AS "p" ON TRUE
conn.Select().From("users").LeftJoinLateral(top, "p")

Dialect notes

Feature	PostgreSQL	MySQL	MariaDB	SQLite
JoinLateral, LeftJoinLateral	Yes	Yes (8.0.14+)	ErrUnsupported	ErrUnsupported
StraightJoin	ErrUnsupported	Yes	Yes	ErrUnsupported

Unsupported joins return db.ErrUnsupported instead of generating bad SQL. Joins on UPDATE and DELETE have extra rules, see Writing Data.