Constructor Join

Namespace

YndigoBlue.Velocity.Model

Assembly

YndigoBlue.Velocity.dll

Join()

Creates an empty Join that can be populated via AddJoinItem(IFromItem, string, string, IFromItem, string, string, JoinType, JoinOperatorType) calls.

public Join()

Join(IFromItem, IFromItem, string, JoinType, JoinOperatorType)

Creates a join between two items on a column that exists in both, with optional join type and operator. Defaults to an INNER JOIN with equality comparison.

public Join(IFromItem fromItem1, IFromItem fromItem2, string columnName, JoinType joinType = JoinType.Inner, JoinOperatorType operatorType = JoinOperatorType.Equals)

Parameters

fromItem1 IFromItem

The first (left) table or join.

fromItem2 IFromItem

The second (right) table or join.

columnName string

The column name that is present in both items.

joinType JoinType

The type of join to perform. Defaults to Inner.

operatorType JoinOperatorType

The comparison operator for the ON condition. Defaults to Equals.

Examples

// Default INNER JOIN
var join = new Join(ordersTable, customersTable, "customer_id");
// SQL: FROM orders INNER JOIN customers ON orders.customer_id = customers.customer_id

// LEFT JOIN with equality
var join = new Join(ordersTable, customersTable, "customer_id", JoinType.LeftOuter);
// SQL: FROM orders LEFT JOIN customers ON orders.customer_id = customers.customer_id

Join(IFromItem, string, IFromItem, string, JoinType, JoinOperatorType)

Creates a join between two items where the column names differ on each side, with optional join type and operator. Defaults to an INNER JOIN with equality comparison.

public Join(IFromItem fromItem1, string columnName1, IFromItem fromItem2, string columnName2, JoinType joinType = JoinType.Inner, JoinOperatorType operatorType = JoinOperatorType.Equals)

Parameters

fromItem1 IFromItem

The first (left) table or join.

columnName1 string

Column name on the first item.

fromItem2 IFromItem

The second (right) table or join.

columnName2 string

Column name on the second item.

joinType JoinType

The type of join to perform. Defaults to Inner.

operatorType JoinOperatorType

The comparison operator for the ON condition. Defaults to Equals.

Examples

// Join where the FK column name differs from the PK column name
var join = new Join(ordersTable, "customer_id", customersTable, "id");
// SQL: FROM orders INNER JOIN customers ON orders.customer_id = customers.id

// Non-equi join with different column names
var join = new Join(ordersTable, "order_date", pricesTable, "effective_date",
    JoinType.Inner, JoinOperatorType.GreaterThanOrEqualTo);
// SQL: FROM orders INNER JOIN prices ON orders.order_date >= prices.effective_date

Join(IFromItem, IFromItem, Function, JoinType)

Creates a function-based join between two items using a predicate such as a geospatial function.

public Join(IFromItem fromItem1, IFromItem fromItem2, Function function, JoinType joinType = JoinType.Inner)

Parameters

fromItem1 IFromItem

The first (left) table or join.

fromItem2 IFromItem

The second (right) table or join.

function Function

The predicate function that forms the ON condition (e.g. SpatialIntersects).

joinType JoinType

The type of join to perform. Defaults to Inner.

Examples

// Spatial inner join — find which locations fall inside which regions
var join = new Join(
    regionsTable,
    locationsTable,
    new Function(FunctionType.SpatialIntersects, regionGeomCol, locationGeomCol));
// SQL: FROM regions INNER JOIN locations ON ST_Intersects(regions.geom, locations.geom)

// Spatial left join — include regions even when no location matches
var join = new Join(
    regionsTable,
    locationsTable,
    new Function(FunctionType.SpatialContains, regionGeomCol, locationGeomCol),
    JoinType.LeftOuter);
// SQL: FROM regions LEFT JOIN locations ON ST_Contains(regions.geom, locations.geom)

Join(IFromItem, string, IFromItem, string, string)

Creates an INNER JOIN between two items with explicit aliases, on a shared column name. Aliases are required when joining a table to itself (self-join).

public Join(IFromItem fromItem1, string alias1, IFromItem fromItem2, string alias2, string columnName)

Parameters

fromItem1 IFromItem

The first (left) table or join.

alias1 string

Alias for the first item.

fromItem2 IFromItem

The second (right) table or join.

alias2 string

Alias for the second item.

columnName string

The column name that is present in both items.

Examples

// Self-join: find employees and their managers
var join = new Join(employeesTable, "emp", employeesTable, "mgr", "manager_id");
// SQL: FROM employees emp INNER JOIN employees mgr ON emp.manager_id = mgr.manager_id

Join(IList<(IFromItem, IFromItem, string)>)

Creates a multi-table INNER JOIN from a list of (parent, child, columnName) tuples. Each tuple joins the child to the parent on a column that exists in both.

public Join(IList<(IFromItem, IFromItem, string)> items)

Parameters

items IList<(IFromItem, IFromItem, string)>

A list of tuples where each entry is (parent, child, sharedColumnName).

Examples

var join = new Join([
    (ordersTable, customersTable, "customer_id"),
    (ordersTable, productsTable, "product_id"),
]);
// SQL: FROM orders
//      INNER JOIN customers ON orders.customer_id = customers.customer_id
//      INNER JOIN products  ON orders.product_id  = products.product_id

Join(IList<(IFromItem, string, string, IFromItem, string, string)>)

Creates a multi-table INNER JOIN from a list of tuples with explicit aliases and separate column names for each side of the join.

public Join(IList<(IFromItem, string, string, IFromItem, string, string)> items)

Parameters

items IList<(IFromItem, string, string, IFromItem, string, string)>

A list of tuples where each entry is (parent, parentAlias, parentColumnName, child, childAlias, childColumnName).

Examples

var join = new Join([
    (ordersTable, "o", "customer_id", customersTable, "c", "id"),
    (ordersTable, "o", "product_id", productsTable, "p", "id"),
]);
// SQL: FROM orders o
//      INNER JOIN customers c ON o.customer_id = c.id
//      INNER JOIN products  p ON o.product_id  = p.id

Join(IList<(IFromItem, IFromItem, Function)>)

Creates a multi-table INNER JOIN from a list of (parent, child, function) tuples. Each tuple joins the child to the parent using a function-based predicate as the ON condition.

public Join(IList<(IFromItem, IFromItem, Function)> items)

Parameters

items IList<(IFromItem, IFromItem, Function)>

A list of tuples where each entry is (parent, child, predicateFunction).

Examples

// Multi-table spatial join: find all (region, location, route) combinations
// where the location and route both intersect the region
var join = new Join([
    (regionsTable, locationsTable, new SpatialIntersects(regionGeomCol, locationGeomCol)),
    (regionsTable, routesTable, new SpatialIntersects(regionGeomCol, routeGeomCol)),
]);
// SQL: FROM regions
//      INNER JOIN locations ON ST_Intersects(regions.geom, locations.geom)
//      INNER JOIN routes    ON ST_Intersects(regions.geom, routes.geom)

Join(IFromItem, IFromItem, JoinType)

Creates a CROSS JOIN between two items, returning the Cartesian product (all combinations). No join condition is required. The result will have table1_rows × table2_rows rows.

Note: joinType must be Cross; any other value throws an exception. Use a constructor that accepts a column name or Filter for conditional joins.

public Join(IFromItem fromItem1, IFromItem fromItem2, JoinType joinType = JoinType.Inner)

Parameters

fromItem1 IFromItem

The first (left) table or join.

fromItem2 IFromItem

The second (right) table or join.

joinType JoinType

Must be Cross.

Examples

var join = new Join(colorsTable, sizesTable, JoinType.Cross);
// SQL: FROM colors CROSS JOIN sizes
// If colors has 5 rows and sizes has 3 rows, result will have 15 rows

Join(IFromItem, IFromItem, Filter, JoinType)

Creates a join between two items with a complex multi-condition ON clause using a Filter.

public Join(IFromItem fromItem1, IFromItem fromItem2, Filter filter, JoinType joinType = JoinType.Inner)

Parameters

fromItem1 IFromItem

The first (left) table or join.

fromItem2 IFromItem

The second (right) table or join.

filter Filter

The filter containing compound join conditions with AND/OR logic.

joinType JoinType

The type of join to perform. Defaults to Inner.

Examples

// Multi-column join: ON (customers.id = orders.customer_id AND customers.region = orders.region)
var filter = new Filter(new List<IFilterItem> {
    new Criterion<Column>(customersTable["id"], ConditionalType.Equals, ordersTable["customer_id"]),
    new BooleanItem(BooleanType.And),
    new Criterion<Column>(customersTable["region"], ConditionalType.Equals, ordersTable["region"])
});

var join = new Join(customersTable, ordersTable, filter);
// SQL: FROM customers
//      INNER JOIN orders ON (customers.id = orders.customer_id AND customers.region = orders.region)

Join(IFromItem, string, IFromItem, string, Filter, JoinType)

Creates a join between two items with explicit aliases and a complex multi-condition ON clause.

public Join(IFromItem fromItem1, string alias1, IFromItem fromItem2, string alias2, Filter filter, JoinType joinType = JoinType.Inner)

Parameters

fromItem1 IFromItem

The first (left) table or join.

alias1 string

Alias for the first item.

fromItem2 IFromItem

The second (right) table or join.

alias2 string

Alias for the second item.

filter Filter

The filter containing compound join conditions with AND/OR logic.

joinType JoinType

The type of join to perform. Defaults to Inner.

Examples

// Self-join with multiple conditions
var filter = new Filter(new List<IFilterItem> {
    new Criterion<Column>(employeesTable["manager_id"], ConditionalType.Equals, employeesTable["employee_id"]),
    new BooleanItem(BooleanType.And),
    new Criterion<Column>(employeesTable["department"], ConditionalType.Equals, employeesTable["department"])
});

var join = new Join(employeesTable, "e", employeesTable, "m", filter);
// SQL: FROM employees e
//      INNER JOIN employees m ON (e.manager_id = m.employee_id AND e.department = m.department)

Join(IFromItem, ISelectItem, IFromItem, ISelectItem, JoinType, JoinOperatorType)

Creates a join between two items using explicit column references and a comparison operator. Supports both equi-joins and non-equi joins (range joins, inequality joins).

public Join(IFromItem fromItem1, ISelectItem column1, IFromItem fromItem2, ISelectItem column2, JoinType joinType = JoinType.Inner, JoinOperatorType joinOperatorType = JoinOperatorType.Equals)

Parameters

fromItem1 IFromItem

The first (left) table or join.

column1 ISelectItem

Column from the first table.

fromItem2 IFromItem

The second (right) table or join.

column2 ISelectItem

Column from the second table.

joinType JoinType

The type of join to perform. Defaults to Inner.

joinOperatorType JoinOperatorType

The comparison operator for the ON condition. Defaults to Equals.

Examples

// Range join: find which price tier applies to each order
var join = new Join(
    ordersTable, ordersTable["order_date"],
    pricesTable, pricesTable["effective_date"],
    JoinType.Inner,
    JoinOperatorType.GreaterThanOrEqualTo
);
// SQL: FROM orders
//      INNER JOIN prices ON orders.order_date >= prices.effective_date

Join(IFromItem, string, ISelectItem, IFromItem, string, ISelectItem, JoinType, JoinOperatorType)

Creates a join between two items with explicit aliases, column references, and a comparison operator. Supports both equi-joins and non-equi joins (range joins, inequality joins). Aliases are required when joining a table to itself or when joining subqueries that need explicit names.

public Join(IFromItem fromItem1, string alias1, ISelectItem column1, IFromItem fromItem2, string alias2, ISelectItem column2, JoinType joinType = JoinType.Inner, JoinOperatorType joinOperatorType = JoinOperatorType.Equals)

Parameters

fromItem1 IFromItem

The first (left) table or join.

alias1 string

Alias for the first item.

column1 ISelectItem

Column from the first table.

fromItem2 IFromItem

The second (right) table or join.

alias2 string

Alias for the second item.

column2 ISelectItem

Column from the second table.

joinType JoinType

The type of join to perform. Defaults to Inner.

joinOperatorType JoinOperatorType

The comparison operator for the ON condition. Defaults to Equals.

Examples

// Range join with aliases: find orders where price exceeds the base price tier
var join = new Join(
    ordersTable, "o", ordersTable["price"],
    pricesTable, "p", pricesTable["base_price"],
    JoinType.Inner,
    JoinOperatorType.GreaterThan
);
// SQL: FROM orders o
//      INNER JOIN prices p ON o.price > p.base_price

Join(IFromItem, string, string, IFromItem, string, string, JoinType, JoinOperatorType)

Creates a join between two items with explicit aliases, separate column names, and a comparison operator. This is the most flexible constructor, essential for outer joins with subqueries where explicit aliasing is required.

public Join(IFromItem fromItem1, string alias1, string columnName1, IFromItem fromItem2, string alias2, string columnName2, JoinType joinType = JoinType.Inner, JoinOperatorType joinOperatorType = JoinOperatorType.Equals)

Parameters

fromItem1 IFromItem

The first (left) table or join.

alias1 string

Alias for the first item.

columnName1 string

Column name on the first item.

fromItem2 IFromItem

The second (right) table or join.

alias2 string

Alias for the second item.

columnName2 string

Column name on the second item.

joinType JoinType

The type of join to perform. Defaults to Inner.

joinOperatorType JoinOperatorType

Examples

// LEFT OUTER JOIN with subquery - requires explicit aliases
Query subquery = Query.Create()
    .From(ordersTable)
    .Select(ordersTable["customer_id"])
    .Select(new Expression("order_count", new Aggregate(AggregateType.Count, new ArithmeticOperator(ArithmeticType.All))))
    .Where(new Criterion<string>(ordersTable["status"], ConditionalType.Equals, "completed"))
    .GroupBy(ordersTable["customer_id"]);

var join = new Join(
    customersTable, "c", "customer_id",
    subquery, "o", "customer_id",
    JoinOperatorType.Equals,
    JoinType.LeftOuter
);
// SQL: FROM customers c
//      LEFT JOIN (SELECT customer_id, COUNT(*) as order_count
//                 FROM orders WHERE status = 'completed'
//                 GROUP BY customer_id) o
//      ON c.customer_id = o.customer_id

// Non-equi join with different column names
var join = new Join(
    ordersTable, "o", "order_date",
    pricesTable, "p", "effective_date",
    JoinOperatorType.GreaterThanOrEqualTo
);
// SQL: FROM orders o
//      INNER JOIN prices p ON o.order_date >= p.effective_date