Where Clauses

The Where* family is shared by select, update and delete builders (and by the WhereClause passed to group callbacks).

On this page: Basic forms · Allowed operators · Grouping · IN, BETWEEN, NULL, LIKE · Several columns, rows, existence · Date and time parts · Every Or variant

Basic forms

Where("votes", 100)               // "votes" = $1
Where("votes", ">", 100)          // "votes" > $1
Where("deleted_at", nil)          // "deleted_at" IS NULL
Where("role", "!=", nil)          // "role" IS NOT NULL
OrWhere("name", "john")           // ... OR "name" = $n
WhereNot("active", true)          // ... AND NOT "active" = $n
WhereRaw("lower(name) = ?", "ann")
WhereColumn("updated_at", ">", "created_at")   // "updated_at" > "created_at"

Consecutive calls are joined with AND. The Or... variants use OR.

A nil value is valid only with =, !=, <>, is and is not.

Allowed operators

=  <  >  <=  >=  <>  !=  <=>
like  not like  ilike  not ilike  like binary
&  |  ^  <<  >>  &~
is  is not
rlike  not rlike  regexp  not regexp
~  ~*  !~  !~*  similar to  not similar to  ~~*  !~~*

Anything else returns db.ErrInvalidOperator. The allow-list is what makes it safe to pass an operator that came from input, but check the dialect differences because not every database supports every operator.

Grouping

Use WhereGroup to add parentheses:

conn.Select().From("users").
    Where("active", true).
    WhereGroup(func(q *db.WhereClause) {
        q.Where("role", "admin").OrWhereNull("deleted_at")
    })
// WHERE "active" = $1 AND ("role" = $2 OR "deleted_at" IS NULL)

• OrWhereGroup joins the group with OR.
• An empty group is skipped.
• The callback receives a *db.WhereClause with every Where* method, including nested groups. HavingGroup receives a *db.HavingClause.

WhereMap builds a group from a map. Keys are sorted, so the SQL is deterministic:

WhereMap(map[string]any{"b": 2, "a": 1})   // WHERE ("a" = $1 AND "b" = $2)

IN, BETWEEN, NULL, LIKE

WhereIn("id", []int{1, 2, 3})                                  // "id" IN ($1, $2, $3)
WhereIn("id", conn.Select().From("bans").Select("user_id"))    // "id" IN (SELECT "user_id" FROM "bans")
WhereNotIn("status", []string{"a", "b"})
WhereIntegerInRaw("id", []int{1, 2})                           // "id" IN (1, 2)  integers inlined

WhereNull("deleted_at")          // WhereNotNull, OrWhereNull, OrWhereNotNull

WhereBetween("age", 18, 65)                  // "age" BETWEEN $1 AND $2   (WhereNotBetween)
WhereBetweenColumns("age", "min_age", "max_age")
WhereValueBetween(10, "low", "high")         // $1 BETWEEN "low" AND "high"

WhereLike("name", "a%")          // case-insensitive
WhereLike("name", "A%", true)    // case-sensitive
WhereNotLike("name", "%x")
WhereNullSafeEquals("a", nil)    // NULL-safe equality

Good to know:

• Empty slices are safe. WhereIn with an empty slice compiles to 0 = 1 (matches nothing). WhereNotIn compiles to 1 = 1 (matches everything). An empty filter list never produces invalid SQL.
• WhereIntegerInRaw is for very large id lists that would exceed a driver's parameter limit. It rejects anything that is not an integer.
• WhereLike renders per dialect: ILIKE/LIKE (PostgreSQL), LIKE/LIKE BINARY (MySQL), LIKE/GLOB (SQLite, with the pattern converted).

Several columns, rows, existence

WhereAll([]string{"first", "last"}, "like", "a%")    // ("first" LIKE $1 AND "last" LIKE $2)
WhereAny([]string{"name", "email"}, "like", "b%")    // ("name" LIKE $1 OR "email" LIKE $2)
WhereNone([]string{"name", "email"}, "like", "z%")   // NOT ("name" LIKE $1 OR "email" LIKE $2)

WhereRowValues([]string{"id", "age"}, "=", []any{1, 30})   // ("id", "age") = ($1, $2)

posts := conn.Select().From("posts").WhereColumn("posts.user_id", "users.id")
conn.Select().From("users").WhereExists(posts)       // EXISTS (SELECT * FROM "posts" WHERE ...)
conn.Select().From("users").WhereNotExists(posts)

A subquery can also be used as a value: Where("score", ">", conn.Select().From("scores").SelectRaw("avg(v)")). See Subqueries, Unions and CTEs.

Date and time parts

WhereDate("created_at", "2024-03-07")        // or a time.Time
WhereTime("created_at", ">=", "09:00:00")
WhereDay("created_at", 7)                    // numeric day and month are zero-padded
WhereMonth("created_at", 3)
WhereYear("created_at", ">", 2023)

A time.Time is formatted for the part (2006-01-02, 15:04:05, 02, 01, 2006).

Dialect	Rendered with
PostgreSQL	::DATE, ::TIME, EXTRACT(...)
MySQL / MariaDB	DATE(), TIME(), DAY() and friends
SQLite	STRFTIME(...)

Or variants

Almost every method has an Or twin: OrWhere, OrWhereIn, OrWhereBetween, OrWhereNull, OrWhereDate, OrWhereExists, OrWhereJsonContains, and so on. The full list lives in the API Overview.

JSON conditions (WhereJsonContains, ...) are covered in JSON Support.