Mise Subqueries

Subqueries

A child query compiles as part of its parent statement. Its parameters join the parent's parameter list in SQL order. Use a child for derived sources, scalar expressions, membership tests, or existence checks.

Read a derived table

var largeCourses = new PostgreSqlQueryBuilder()
    .Select($"{CourseTbl.CourseIdCol:raw}")
    .Select($"{CourseTbl.TitleCol:raw}")
    .Select($"{CourseTbl.CapacityCol:raw}")
    .From($"{CourseTbl.Table:raw}")
    .Where($"{CourseTbl.CapacityCol:raw} >= {minimumCapacity}");

var query = new PostgreSqlQueryBuilder()
    .Select($"large_courses.course_id")
    .Select($"large_courses.title")
    .Select($"large_courses.capacity")
    .From(largeCourses, "large_courses")
    .OrderBy($"large_courses.course_id");

From(query, alias) encloses the child and quotes the alias. Here, the lowercase alias is written literally in trusted projection fragments. The result names remain course_id, title, and capacity, so the parent still maps to CourseRow.

Select a scalar subquery

var countQuery = new PostgreSqlQueryBuilder()
    .Select($"COUNT(*)")
    .From($"{CourseTbl.Table:raw}");

var query = new PostgreSqlQueryBuilder().Select(countQuery);

var count = await reader.ScalarAsync<long?>(query, cancellationToken);

Select(IQueryBuilder) encloses the child as a scalar expression. SQL requires an appropriate scalar result; Mise does not reduce a multirow child to one value. This overload adds no named result alias, so it fits scalar reads. Use explicit custom SQL when a more complex projection needs syntax not exposed by the fluent API.

Filter with a child

WhereIn(expression, query) embeds the child as the membership source. WhereExists(query) checks whether a child produces any row. Correlated children can refer to the parent's table aliases through trusted constants.

See Filtering for both forms and Updates for Set(column, query).

Composition rules

Children must be Mise query builders using the same engine as the parent. A custom IQueryBuilder implementation is not accepted as a child by the fluent composition methods. Different engines fail when compiled together.

Do not attach a builder to itself or create a cycle through several builders. Compilation detects cycles. Children are compiled from their current state, so changing a child before compiling the parent changes the parent's SQL.

See Common table expressions for naming reusable sources and Set operations for combining result sets.