Fluent SQL

UNION and Set Operations

Two queries can be concatenated with Union() or UnionAll(). UNION ALL includes all rows; UNION removes duplicates and can be slower.

To concatenate two queries, they must have matching select expression lists. The number of columns and their data types must match. Fluent SQL doesn’t detect this kind of error, so the generated query could fail at run-time.

var selectStatement = Select
    .All("ID", "Name")
    .From("Customer")
    .Union(
        Select
            .All("ID", "Name")
            .From("Vendor")
    );

SELECT [ID], [Name]
FROM [Customer]
UNION
SELECT [ID], [Name]
FROM [Vendor];

Other set operations

Fluent SQL also supports the less common set operations: Except(), which returns all the distinct rows in the first query that aren’t in the second, and Intersect(), which returns all the distinct rows that appear in both. The same rules about the number and data types of the columns apply.