Set operations

Combine two result sets with Union, UnionAll, Intersect, IntersectAll, Except and ExceptAll, and chain them left to right.

Prerequisites: Querying and projections · Subqueries · SELECT DISTINCT

Overview

Every QueryCommand<TResult> exposes six set-operation methods that take another command and return a new command:

Method	SQL keyword	Keeps duplicates	Right side by selector
`Union(query)` / `Union<TEntity>(selector)`	`union`	no	`Union<TEntity>(selector)`
`UnionAll(query)` / `UnionAll<TEntity>(selector)`	`union all`	yes	`UnionAll<TEntity>(selector)`
`Intersect(query)` / `Intersect<TEntity>(selector)`	`intersect`	no	`Intersect<TEntity>(selector)`
`IntersectAll(query)` / `IntersectAll<TEntity>(selector)`	`intersect all`	yes	`IntersectAll<TEntity>(selector)`
`Except(query)` / `Except<TEntity>(selector)`	`except`	no	`Except<TEntity>(selector)`
`ExceptAll(query)` / `ExceptAll<TEntity>(selector)`	`except all`	yes	`ExceptAll<TEntity>(selector)`

The right-hand command may project a different element type: the method is generic over the other side (Union<T>(QueryCommand<T>)), so SimpleEntity.Select(it => it.Id).Union(ComplexEntity.Select(it => (int)it.Id)) is valid as long as the shapes line up. The selector form (Union<TEntity>(selector) and friends) fixes the element type of the right side to the left side's TResult and projects TEntity to TResult through the selector; use the explicit Union<T>(QueryCommand<T>) form when the right side needs a different CLR type.

The result is itself a QueryCommand, so it can be executed through From(result) or have another set operation chained onto it. Chaining applies left to right: each new operation combines the accumulated left side with the next command, and standard operator precedence applies within the chain ((A except B) intersect C, not A except (B intersect C)).

The biggest portability caveat is *ALL. PostgreSQL implements intersect all and except all; SQLite and SQL Server do not, and nextorm throws NotSupportedException when such a query is prepared. union, union all, intersect and except are supported everywhere.

Union and UnionAll

var distinct = dataContext.From<ISimpleEntity>().Select(it => it.Id)
    .Union(dataContext.From<ISimpleEntity>().Select(it => it.Id));

var all = dataContext.From<ISimpleEntity>().Select(it => it.Id)
    .UnionAll(dataContext.From<ISimpleEntity>().Select(it => it.Id));

select id
 from simple_entity
 union 
select id
 from simple_entity

select id
 from simple_entity
 union all 
select id
 from simple_entity

The two sides may also be different entities as long as the element types line up. In the integration suite SimpleEntity (ids 1..10) is unioned with ComplexEntity (ids 1..3) cast to int, so the deduplicated union has 10 rows and the UnionAll of the same pair has 13:

var distinctCount = dataContext.From(
    dataContext.From<ISimpleEntity>().Select(it => it.Id)
        .Union<IComplexEntity>(it => (int)it.Id)).Count(); // 10

var allCount = dataContext.From(
    dataContext.From<ISimpleEntity>().Select(it => it.Id)
        .UnionAll<IComplexEntity>(it => (int)it.Id)).Count(); // 13

Intersect and Except

var common = dataContext.From<ISimpleEntity>().Select(it => it.Id)
    .Intersect(dataContext.From<ISimpleEntity>().Select(it => it.Id));

var onlyLeft = dataContext.From<ISimpleEntity>().Select(it => it.Id)
    .Except(dataContext.From<ISimpleEntity>().Select(it => it.Id));

select id
 from simple_entity
 intersect 
select id
 from simple_entity

select id
 from simple_entity
 except 
select id
 from simple_entity

Cross-entity: because simple_entity holds ids 1..10 and complex_entity holds 1..3, INTERSECT keeps 1, 2, 3 and EXCEPT leaves 4..10.

Chaining is left to right

// (simple EXCEPT complex) INTERSECT simple = {4..10} INTERSECT {1..10} = {4..10}.
var cmd = dataContext.From<ISimpleEntity>().Select(it => it.Id)
    .Except(dataContext.From<IComplexEntity>().Select(it => (int)it.Id))
    .Intersect(dataContext.From<ISimpleEntity>().Select(it => it.Id));

var count = dataContext.From(cmd).Count(); // 7

IntersectAll and ExceptAll

var cmd = dataContext.From<ISimpleEntity>().Select(it => it.Id)
    .IntersectAll(dataContext.From<IComplexEntity>().Select(it => (int)it.Id));

dataContext.From(cmd).Count();

-- PostgreSQL only
select id
 from simple_entity
 intersect all 
select id
 from simple_entity

var cmd = dataContext.From<ISimpleEntity>().Select(it => it.Id)
    .ExceptAll(dataContext.From<IComplexEntity>().Select(it => (int)it.Id));

dataContext.From(cmd).Count();

When the dialect does not support the operation, preparing the command throws NotSupportedException with the operation name in the message ("The IntersectAll set operation is not supported by this SQL dialect").

Querying a set-operation result

A set operation returns a command, so you query it through From:

var cmd = dataContext.From<IComplexEntity>().Select(it => it.Int)
    .Distinct()
    .Union(dataContext.From<IComplexEntity>().Select(it => it.Int));

var count = dataContext.From(cmd).Count(); // 2

An entity-typed result can be projected the same way:

var count = dataContext.From(
    dataContext.From<ISimpleEntity>().Select(it => new { it.Id })
        .Union(dataContext.From<IComplexEntity>().Select(it => new { it.Id })))
    .Count();

Reading one scalar column directly

When both sides project a single scalar column, From(cmd) materialises the rows directly instead of returning objects, and UnionAll keeps duplicates:

var left = dataContext.From<ISimpleEntity>().Where(it => it.Id <= 2).Select(it => it.Id);
var right = dataContext.From<ISimpleEntity>().Where(it => it.Id >= 2 && it.Id <= 3).Select(it => it.Id);

var rows = dataContext.From(left.UnionAll(right)).ToList(); // 1, 2, 2, 3

This result is the same whether the type's process-wide select-list cache is cold or has already been populated by an earlier scalar read: the derived read builds its own one-column shape and does not reuse or overwrite that cache entry. A derived source with more than one column still needs an explicit outer Select (see Subqueries).

Provider differences

Provider	`union` / `union all`	`intersect` / `except`	`intersect all` / `except all`
SQLite	supported	supported	`NotSupportedException` when prepared
SQL Server	supported	supported	`NotSupportedException` when prepared
PostgreSQL	supported	supported	supported
MySQL	supported	supported (MySQL 8.0.31+)	`NotSupportedException` when prepared (no `ALL` variants)
MariaDB	supported	supported (MariaDB 10.4+)	supported
ClickHouse	supported	supported	supported
In-memory	not covered by the in-memory test suite	not covered	not covered

This matches the SupportsIntersectExceptAll capability: PostgreSQL, MariaDB and ClickHouse are the shipped providers that return true; SQLite and SQL Server return false and the SQL builder refuses to render an *ALL set operation for them.

Table of Contents