Class Union

Namespace

YndigoBlue.Velocity.Model

Assembly

YndigoBlue.Velocity.dll

Represents a UNION operation that combines the result sets of two or more queries.

public class Union

Inheritance

object

Union

Examples

Simple UNION of two queries:

// SELECT username FROM active_users
Query activeQuery = new Query();
activeQuery.AddFromItem(activeUsersTable);
activeQuery.AddSelectItem(activeUsersTable["username"]);

// SELECT username FROM archived_users
Query archivedQuery = new Query();
archivedQuery.AddFromItem(archivedUsersTable);
archivedQuery.AddSelectItem(archivedUsersTable["username"]);

// Combine with UNION (removes duplicates)
activeQuery.AddUnion(new Union(archivedQuery, unionAll: false));

using (Manager manager = new Manager(connection))
{
    manager.LoadSchema("my_schema");
    Result result = manager.Retrieve(activeQuery);
}

// SQL: SELECT username FROM active_users
//      UNION
//      SELECT username FROM archived_users

UNION ALL to keep duplicates:

// Get all order totals from both current and historical orders
Query currentOrders = new Query();
currentOrders.AddFromItem(ordersTable);
currentOrders.AddSelectItem(ordersTable["total"]);

Query historicalOrders = new Query();
historicalOrders.AddFromItem(orderHistoryTable);
historicalOrders.AddSelectItem(orderHistoryTable["total"]);

// Combine with UNION ALL (keeps duplicates, faster)
currentOrders.AddUnion(new Union(historicalOrders, unionAll: true));

using (Manager manager = new Manager(connection))
{
    Result result = manager.Retrieve(currentOrders);
}

// SQL: SELECT total FROM orders
//      UNION ALL
//      SELECT total FROM order_history

Multiple unions:

// Combine data from three different tables
Query emailsQuery = new Query();
emailsQuery.AddFromItem(emailsTable);
emailsQuery.AddSelectItem(emailsTable["email_address"]);

Query customersQuery = new Query();
customersQuery.AddFromItem(customersTable);
customersQuery.AddSelectItem(customersTable["email"]);

Query vendorsQuery = new Query();
vendorsQuery.AddFromItem(vendorsTable);
vendorsQuery.AddSelectItem(vendorsTable["contact_email"]);

// Chain multiple unions
emailsQuery.AddUnion(new Union(customersQuery, false));
emailsQuery.AddUnion(new Union(vendorsQuery, false));

// SQL: SELECT email_address FROM emails
//      UNION
//      SELECT email FROM customers
//      UNION
//      SELECT contact_email FROM vendors

UNION with WHERE clauses:

// Get active users from both tables
Query activeUsers = new Query();
activeUsers.AddFromItem(usersTable);
activeUsers.AddSelectItem(usersTable["username"]);
activeUsers.AddSelectItem(usersTable["email"]);
activeUsers.SetWhereFilter(
    new Filter(new Criterion<string>(usersTable["status"], "active"))
);

Query activeAdmins = new Query();
activeAdmins.AddFromItem(adminsTable);
activeAdmins.AddSelectItem(adminsTable["username"]);
activeAdmins.AddSelectItem(adminsTable["email"]);
activeAdmins.SetWhereFilter(
    new Filter(new Criterion<string>(adminsTable["status"], "active"))
);

activeUsers.AddUnion(new Union(activeAdmins, false));

// SQL: SELECT username, email FROM users WHERE status = 'active'
//      UNION
//      SELECT username, email FROM admins WHERE status = 'active'

Remarks

UNION combines rows from multiple Query objects into a single result set. All queries in a union must return the same number of columns with compatible data types.

There are two types of UNION operations:

• UNIONRemoves duplicate rows from the combined result set
• UNION ALLIncludes all rows, even duplicates (faster performance)

Unions are added to a base query using AddUnion(Union). Multiple unions can be chained together to combine many queries.

Constructors

Union(Query, bool)

Creates a new UNION operation.

Properties

Query

Gets the query to be combined with the base query.

UnionAll

Gets whether this is a UNION ALL operation (true) or a UNION operation (false).