SQL Expressions: UNION, INTERSECT, EXCEPT

Queries can be chained together with sq.Union/sq.Intersect/sq.Except the same way predicates can be chained with sq.And/sq.Or. To SELECT from a UNION-ed query, convert it into a subquery or Common Table Expression (CTE) and you will be able to use it like any other table.

Subquery

-- SQL: UNION into a Subquery
SELECT subquery.name FROM (
    SELECT u.name FROM public.users AS u WHERE u.name = u.email
    UNION
    SELECT u.name FROM public.users AS u WHERE u.email IS NOT NULL
    UNION
    SELECT 'aaa'
) AS subquery;

// Go: UNION into a Subquery
u := tables.USERS().As("u")
subquery := sq.
    Union(
        sq.Select(u.NAME).From(u).Where(u.Name.Eq(u.EMAIL)),
        sq.Select(u.NAME).From(u).Where(u.Name.IsNotNull()),
        sq.Select(sq.String("aaaa")),
    ).
    Subquery("subquery")
var name string
var names []string

// ...if you use ToSQL
query, args := sq.Select(subquery["name"]).From(subquery).ToSQL()
rows, err := DB.Query(query, args...)
if err != nil {
    /* handle error */
}
defer rows.Close()
for rows.Next() {
    err = rows.Scan(&name)
    if err != nil {
        /* handle error */
    }
    names = append(names, name)
}

// ...if you use Selectx (see [Struct Mapping](/sq/basics/struct-mapping.html))
err := sq.
    Selectx(func(row *sq.Row) {
        row.ScanInto(&name, subquery["name"])
    }, func() {
        names = append(names, name)
    }).
    From(subquery)
if err != nil {
    /* handle error */
}

CTE

-- SQL: UNION into a CTE
WITH cte AS (
    SELECT u.name FROM public.users AS u WHERE u.name = u.email
    UNION
    SELECT u.name FROM public.users AS u WHERE u.email IS NOT NULL
    UNION
    SELECT 'aaa'
)
SELECT cte.name FROM cte;

// Go: UNION into a CTE
u := tables.USERS().As("u")
subquery := sq.
    Union(
        sq.Select(u.NAME).From(u).Where(u.Name.Eq(u.EMAIL)),
        sq.Select(u.NAME).From(u).Where(u.Name.IsNotNull()),
        sq.Select(sq.String("aaaa")),
    ).
    CTE("cte")
var name string
var names []string

// ...if you use ToSQL
query, args := sq.With(cte).Select(cte["name"]).From(cte).ToSQL()
rows, err := DB.Query(query, args...)
if err != nil {
    /* handle error */
}
defer rows.Close()
for rows.Next() {
    err = rows.Scan(&name)
    if err != nil {
        /* handle error */
    }
    names = append(names, name)
}

// ...if you use Selectx (see [Struct Mapping](/sq/basics/struct-mapping.html))
err := sq.
    With(cte).
    Selectx(func(row *sq.Row) {
        row.ScanInto(∓name, cte["name"])
    }, func() {
        names = append(names, name)
    }).
    From(cte)
if err != nil {
    /* handle error */
}

< Prev: IN

Up: SQL Expressions

Next: CASE >