Set Operations

admins := Users.From(db).Select(Users.Name).Where(Users.Role.Eq("admin"))
mods := Users.From(db).Select(Users.Name).Where(Users.Role.Eq("mod"))

// UNION (deduplicated)
users, err := dew.Union[User](db, admins, mods).All()

// UNION ALL (with duplicates)
users, err := dew.UnionAll[User](db, admins, mods).All()

active := Users.From(db).Select(Users.Name).Where(Users.Active.IsTrue())
premium := Users.From(db).Select(Users.Name).Where(Users.Plan.Eq("premium"))

// Rows in both queries
users, err := dew.Intersect[User](db, active, premium).All()

// Rows in first but not second
users, err := dew.Except[User](db, active, premium).All()

admins := Users.From(db).Select(Users.Name).Where(Users.Role.Eq("admin"))
mods := Users.From(db).Select(Users.Name).Where(Users.Role.Eq("mod"))
editors := Users.From(db).Select(Users.Name).Where(Users.Role.Eq("editor"))

users, err := dew.Union[User](db, admins, mods).
    UnionAll(editors).
    All()
// (SELECT ... WHERE role = $1) UNION (SELECT ... WHERE role = $2) UNION ALL (SELECT ... WHERE role = $3)

users, err := dew.UnionAll[User](db, admins, mods).
    OrderBy(dew.Asc(dew.Raw("name"))).
    Limit(10).
    Offset(5).
    All()

combined := dew.Union[User](db, admins, mods)

// As CTE
users, err := dew.From[User](db, dew.TableRef("staff")).
    With(dew.CTE("staff", combined)).
    All()

// As subquery FROM
users, err := dew.FromSub[User](db, combined, "staff").
    Where(dew.Raw("staff.age > ?", 25)).
    All()

sql, args := dew.Union[User](db, admins, mods).ToSql()