Mise Joins

Joins

Joins combine rows from SQL sources. Choose the join kind, supply a fragment containing the target and predicate, and select the exact columns your result row needs.

Join mapped relationships

var query = new PostgreSqlQueryBuilder()
    .Select($"{CourseTbl.Course.CourseIdCol:raw}")
    .Select($"{CourseTbl.Course.TitleCol:raw} AS course_title")
    .Select($"{CampusTbl.Campus.NameCol:raw} AS campus_name")
    .From($"{CourseTbl.Course.Table:raw}")
    .LeftJoin($"{CourseTbl.Course.CampusIdJoinCampus:raw}");

var courses = await reader.ListAsync<CourseCampusRow>(query, cancellationToken);

The aliases and relationship come from Aliases and Relationships. CourseCampusRow is defined in Result rows. Its nullable CampusName accepts unmatched rows from the left join.

Choose the join kind

Method	Rows retained
InnerJoin	Matching rows on both sides
LeftJoin	All left rows, plus matching right rows
RightJoin	All right rows, plus matching left rows
FullJoin	All rows from either side
CrossJoin	Every combination of the two sources

Relationship joins require FROM and a fragment containing an ON predicate. Cross joins take only a source and reject an ON predicate. MySQL and MariaDB do not support full joins through these builders. SQLite right and full joins require SQLite 3.39 or later.

Write an explicit predicate

var query = new PostgreSqlQueryBuilder()
    .Select($"{CourseTbl.Course.CourseIdCol:raw}")
    .From($"{CourseTbl.Course.Table:raw}")
    .InnerJoin(
        $"{CampusTbl.Campus.Table:raw} ON {CourseTbl.Course.CampusIdCol:raw} = {CampusTbl.Campus.CampusIdCol:raw}"
    );

Use an explicit fragment for a condition not represented by a mapped relationship. You can also append an additional condition to a generated fragment:

.LeftJoin(
    $"{CourseTbl.Course.CampusIdJoinCampus:raw} AND {CampusTbl.Campus.NameCol:raw} = {campusName}"
)

Putting a right-side condition in ON limits matching right rows while preserving unmatched left rows. Putting that same condition in WHERE can remove the unmatched rows. Choose the clause based on the result you want.

Cross join a derived table

var marker = new PostgreSqlQueryBuilder().Select($"{42} AS marker");

query.CrossJoin(marker, "marker_source");

The builder encloses the child query and quotes the alias. All child builders must use the same engine as the parent.

Result cardinality

A join can return several rows for one course when several related rows match. Mise materializes each SQL row; it does not deduplicate entities or assemble child collections. Use distinct projections, grouped queries, or separate queries when those match your required result shape.

See Aggregation, Subqueries, and Result rows.