Mise Set Operations

Set operations

Set operations combine SELECT results. Each child must project compatible columns in the same order. SQL decides type compatibility and duplicate handling.

Combine two sources

var campusCourses = new PostgreSqlQueryBuilder()
    .Select($"{CourseTbl.CourseIdCol:raw}")
    .Select($"{CourseTbl.TitleCol:raw}")
    .Select($"{CourseTbl.CapacityCol:raw}")
    .From($"{CourseTbl.Table:raw}")
    .Where($"{CourseTbl.CampusIdCol:raw} = {campusId}");

var query = new PostgreSqlQueryBuilder()
    .Select($"{CourseTbl.CourseIdCol:raw}")
    .Select($"{CourseTbl.TitleCol:raw}")
    .Select($"{CourseTbl.CapacityCol:raw}")
    .From($"{CourseTbl.Table:raw}")
    .Where($"{CourseTbl.CapacityCol:raw} >= {100}")
    .Union(campusCourses)
    .OrderBy($"course_id");

This returns large courses and courses on the chosen campus. Union removes duplicate selected rows, including courses matching both branches. The result maps to CourseRow.

Choose an operation

Method	Result
Union	Rows in either branch, with duplicates removed
UnionAll	Rows in either branch, preserving duplicates
Intersect	Rows appearing in both branches
Except	Rows in the left branch absent from the right branch

The database must support the chosen operation and syntax. In particular, available set operations depend on the MySQL or MariaDB server version; see Database dialects.

Ordering and precedence

Set operations are emitted in call order, followed by the outer builder's ORDER BY and paging. Use result column names for compound-query ordering when the database requires them, rather than table-qualified columns belonging to one branch.

The fluent builder emits child SELECT statements without automatically adding parentheses around every branch. Operator precedence, branch-local ORDER BY, and LIMIT therefore follow the database's SQL grammar. Use a derived table or Custom SQL when your statement needs explicit grouping.

Each branch uses the same database engine and shares parent parameter numbering. See Subqueries for composition rules.