Query Building: SELECT

Basic example
SELECT DISTINCT
SELECT DISTINCT ON (Postgres only)
SELECT EXISTS
Clause manipulation

Basic example

contents

SQL

SELECT u.user_id, u.name, u.email
FROM public.users AS u
WHERE u.name = 'bob';

u := tables.USERS().As("u")
var user User
var users []User

// ...if you use ToSQL
query, args := sq.Select(u.USER_ID, u.NAME, u.EMAIL).
    From(u).
    Where(u.USER_ID.EqString("bob")).
    ToSQL()
rows, err := DB.Query(query, args...)
if err != nil {
    /* handle error */
}
defer rows.Close()
for rows.Next() {
    err = rows.Scan(&user.UserID, &user.Name, &user.Email)
    if err != nil {
        /* handle error */
    }
    users = append(users, user)
}

// ...if you use Selectx (see [Struct Mapping](/sq/basics/struct-mapping.html#select-one-vs-many))
err := sq.Selectx(func(row *sq.Row) {
        user.UserID = row.Int(u.USER_ID)
        user.Name = row.String(u.NAME)
        user.Email = row.String(u.EMAIL)
    }, func() {
        users = append(users, user)
    }).
    From(u).
    Where(u.USER_ID.EqString("bob")).
    Fetch(DB)
if err != nil {
    /* handle error */
}

// ...if you use SelectRowx (see [Struct Mapping](/sq/basics/struct-mapping.html#select-one-vs-many))
// SelectRowx only selects one row
err := sq.SelectRowx(func(row *sq.Row) {
        user.UserID = row.Int(u.USER_ID)
        user.Name = row.String(u.NAME)
        user.Email = row.String(u.EMAIL)
    }).
    From(u).
    Where(u.USER_ID.EqString("bob")).
    Fetch(DB)
if err != nil {
    /* handle error */
}

SELECT DISTINCT

contents

SQL

SELECT DISTINCT u.user_id, u.name
FROM public.users AS u
WHERE u.name = 'bob';

u := tables.USERS().As("u")
var user User
var users []User

// ...if you use ToSQL
query, args := sq.SelectDistinct(u.USER_ID, u.NAME).
    From(u).
    Where(u.USER_ID.EqString("bob")).
    ToSQL()
rows, err := DB.Query(query, args...)
if err != nil {
    /* handle error */
}
defer rows.Close()
for rows.Next() {
    err = rows.Scan(&user.UserID, &user.Name)
    if err != nil {
        /* handle error */
    }
    users = append(users, user)
}

// ...if you use Selectx (see [Struct Mapping](/sq/basics/struct-mapping.html#select-one-vs-many))
err := sq.SelectDistinct().
    Selectx(func(row *sq.Row) {
        user.UserID = row.Int(u.USER_ID)
        user.Name = row.String(u.NAME)
    }, func() {
        users = append(users, user)
    }).
    From(u).
    Where(u.USER_ID.EqString("string")).
    Fetch(DB)
if err != nil {
    /* handle error */
}

// ...if you use SelectRowx (see [Struct Mapping](/sq/basics/struct-mapping.html#select-one-vs-many))
// SelectRowx only selects one row
err := sq.SelectDistinct().
    SelectRowx(func(row *sq.Row) {
        user.UserID = row.Int(u.USER_ID)
        user.Name = row.String(u.NAME)
    }).
    From(u).
    Where(u.USER_ID.EqString("bob")).
    Fetch(DB)
if err != nil {
    /* handle error */
}

SELECT DISTINCT ON

contents

Note: SELECT DISTINCT ON is a postgres-only feature.

SQL

SELECT DISTINCT ON (u.email, u.user_id) u.user_id, u.name
FROM public.users AS u
WHERE u.name = 'bob';

u := tables.USERS().As("u")
var user User
var users []User

// ...if you use ToSQL
query, args := sq.SelectDistinctOn(u.EMAIL, u.USER_ID)(u.USER_ID, u.NAME).
    From(u).
    Where(u.USER_ID.EqString("bob")).
    ToSQL()
rows, err := DB.Query(query, args...)
if err != nil {
    /* handle error */
}
defer rows.Close()
for rows.Next() {
    err = rows.Scan(&user.UserID, &user.Name)
    if err != nil {
        /* handle error */
    }
    users = append(users, user)
}

// ...if you use Selectx (see [Struct Mapping](/sq/basics/struct-mapping.html#select-one-vs-many))
err := sq.SelectDistinctOn(u.EMAIL, u.USER_ID)().
    Selectx(func(row *sq.Row) {
        user.UserID = row.Int(u.USER_ID)
        user.Name = row.String(u.NAME)
    }, func() {
        users = append(users, user)
    }).
    From(u).
    Where(u.USER_ID.EqString("bob")).
    Fetch(DB)
if err != nil {
    /* handle error */
}

// ...if you use SelectRowx (see [Struct Mapping](/sq/basics/struct-mapping.html#select-one-vs-many))
// SelectRowx only selects one row
err := sq.SelectDistinctOn(u.EMAIL, u.USER_ID)().
    SelectRowx(func(row *sq.Row) {
        user.UserID = row.Int(u.USER_ID)
        user.Name = row.String(u.NAME)
    }).
    From(u).
    Where(u.USER_ID.EqString("bob")).
    Fetch(DB)
if err != nil {
    /* handle error */
}

SELECT EXISTS

contents

SQL

SELECT EXISTS (
    SELECT 1
    FROM public.users AS u
    WHERE u.name = 'bob'
);

u := tables.USERS().As("u")
var exists bool

// ...if you use ToSQL
query, args := sq.Select(sq.Exists(
    sq.SelectOne().From(u).Where(u.USER_ID.EqString("bob")),
)).ToSQL()
err := DB.Query(query, args...).Scan(&exists)
if err != nil {
    /* handle error */
}

// ...if you use SelectRowx (see [Struct Mapping](/sq/basics/struct-mapping.html#select-one-vs-many))
err := sq.SelectRowx(func(row *sq.Row) {
    exists = row.Bool(sq.Exists(
        sq.SelectOne().From(u).Where(u.USER_ID.EqString("bob")),
    ))
}).Fetch(DB)
if err != nil {
    /* handle error */
}

Clauses are just struct fields, you can either use method chaining to add clauses or manipulate the struct fields directly.
Clauses: WITH, SELECT, FROM, JOIN, WHERE, GROUP BY, HAVING, WINDOW, ORDER BY, LIMIT, OFFSET

WITH clauses

For more information see Common Table Expressions.

var q sq.SelectQuery

// Adding

q = q.With(cte1, cte2, cte3)
// is equivalent to //
q = q.With(cte1).With(cte2).With(cte3)
// is equivalent to //
q.CTEs = []sq.CTE{cte1, cte2, cte3}

// Removing

q.CTEs = nil

SELECT clauses

var q sq.SelectQuery

// Adding

q = q.Select(field1, field2, field3)
// is equivalent to //
q = q.Select(field1).Select(field2).Select(field3)
// is equivalent to //
q.SelectFields = []sq.Field{field1, field2, field3}

// Removing

q.SelectFields = nil

FROM clauses

var q sq.SelectQuery

// Adding

q = q.From(table)
// is equivalent to //
q.FromTable = table

// Removing

q.FromTable = nil

JOIN clauses

var q sq.SelectQuery

// Adding

q = q.Join(table1, predicate1).
    LeftJoin(table2, predicate2).
    FullJoin(table3, predicate3)
// is equivalent to //
q.JoinTables = []sq.JoinTable{
    sq.Join(table1, predicate1),
    sq.LeftJoin(table2, predicate2),
    sq.FullJoin(table3, predicate3),
}

// Removing

q.JoinTables = nil

WHERE clauses

var q sq.SelectQuery

// Adding

q = q.Where(predicate1, predicate2, predicate3)
// is equivalent to //
q = q.Where(predicate1).Where(predicate2).Where(predicate3)
// is equivalent to //
q.WherePredicate.Predicates = []sq.Predicate{
    predicate1,
    predicate2,
    predicate3,
}

// Removing

q.WherePredicate.Predicates = nil

GROUP BY clauses

var q sq.SelectQuery

// Adding

q = q.GroupBy(field1, field2, field3)
// is equivalent to //
q = q.GroupBy(field1).GroupBy(field2).GroupBy(field3)
// is equivalent to //
q.GroupByFields = []sq.Field{field1, field2, field3}

// Removing

q.GroupByFields = nil

HAVING clauses

var q sq.SelectQuery

// Adding

q = q.Having(predicate1, predicate2, predicate3)
// is equivalent to //
q = q.Having(predicate1).Having(predicate2).Having(predicate3)
// is equivalent to //
q.HavingPredicate.Predicates = []sq.Predicate{
    predicate1,
    predicate2,
    predicate3,
}

// Removing

q.HavingPredicate.Predicates = nil

WINDOW clauses

var q sq.SelectQuery

// Adding

q = q.Window(window1, window2, window3)
// is equivalent to //
q = q.Window(window1).Window(window2).Window(window3)
// is equivalent to //
q.Windows = []sq.Window{window1 window2, window3}

// Removing

q.Windows = nil

ORDER BY clauses

var q sq.SelectQuery

// Adding

q = q.OrderBy(field1, field2, field3)
// is equivalent to //
q = q.OrderBy(field1).OrderBy(field2).OrderBy(field3)
// is equivalent to //
q.OrderByFields = []sq.Field{field1, field2, field3}

// Removing

q.OrderByFields = nil

LIMIT clauses

var q sq.SelectQuery

// Adding

q = q.Limit(10)
// is equivalent to //
limit := int64(10)
q.LimitValue = &limit

// Removing

q.LimitValue = nil

OFFSET clauses

var q sq.SelectQuery

// Adding

q = q.Offset(10)
// is equivalent to //
offset := int64(10)
q.OffsetValue = &offset

// Removing

q.OffsetValue = nil

Up: Query Building

Next: INSERT >

Query Building: SELECT

Contents

Basic example

SELECT DISTINCT

SELECT DISTINCT ON

SELECT EXISTS

Clause manipulation