JoinedTable

A JoinedTable is the result of joining two or more tables on one or more JoinCondition rows. The JoinType of each condition controls how unmatched rows are handled.

Matching joins (LEFT, RIGHT, INNER, FULL) require both a left_field and a right_field to match rows between the two tables. A CROSS join instead pairs every left row with every right row (the Cartesian product) and takes no match fields — supplying left_field or right_field on a CROSS condition raises ValueError.

Basic setup

Suppose we want to know the customer name behind each order. The example below joins an Orders table to a Customers table on Customer Id so that customer details are available alongside every order:

from daitum_model import ModelBuilder, DataType, JoinCondition, JoinType

model = ModelBuilder()

orders = model.add_data_table("Orders")
orders.set_key_column("Order Id")
orders.add_data_field("Order Id", DataType.STRING)
customer_id = orders.add_data_field("Customer Id", DataType.STRING)

customers = model.add_data_table("Customers")
customers.set_key_column("Id")
customer_key = customers.add_data_field("Id", DataType.STRING)
customers.add_data_field("Name", DataType.STRING)

condition = JoinCondition(
    orders, customers, JoinType.LEFT, left_field=customer_id, right_field=customer_key
)
orders_with_customers = model.add_joined_table("OrdersWithCustomers", [condition])
orders_with_customers.add_table_reference(orders)
orders_with_customers.add_table_reference(customers)

A JoinCondition pairs a left_table with a right_table and, for a matching join type such as LEFT, requires the fields to match rows between them — here, Orders.Customer Id and Customers.Id. add_joined_table then builds the joined table from one or more conditions. add_table_reference adds a field on the joined table that references a source table’s row, so its fields can be looked up from the joined table.

API Reference

class JoinedTable(id, join_conditions)

Bases: Table

Represents a table resulting from a join operation.

A JoinedTable is a table that is constructed by joining two or more other tables based on specific join conditions. A join operation combines rows from different tables based on a related field (column) between them. The join_conditions list defines how these tables are connected and which fields from the tables are used for the join.

Joins can be of different types, such as INNER, LEFT, RIGHT, FULL, and CROSS, and they dictate how the rows from the tables are combined and which rows are included in the final result.

A JoinCondition consists of the following:

• left_table: The left table involved in the join.

• right_table: The right table involved in the join.

• join_type: The type of join (e.g., INNER, LEFT, RIGHT, FULL, CROSS).

• left_field: The field in the left table that is used to match with the right table. Omitted for a CROSS join.

• right_field: The field in the right table that is used to match with the left table. Omitted for a CROSS join.

A CROSS join takes no match fields and pairs every left row with every right row.

Multiple JoinCondition objects can be specified to represent more complex join scenarios. Each condition defines how a pair of tables are joined, and having multiple conditions allows for joining more than two tables at once, with each pair of tables joined based on the specific conditions defined. When there are multiple JoinCondition objects, the joins are performed in sequence, and the resulting table combines the results of all the joins.

add_table_reference(source_table)

Adds a reference to a table in the JoinedTable.

This method allows for adding a reference field for a source table that is part of the join conditions. If the source table is not part of the join conditions, a ValueError will be raised.

Parameters:

source_table (Table) – The table to be referenced.

Returns:

The reference field for the source table.

Return type:

Field

Raises:

ValueError – If the source table is not present in any of the join conditions.

class JoinCondition(left_table, right_table, join_type, left_field=None, right_field=None)

Bases: Buildable

Represents a condition for joining two tables.

For matching joins (JoinType.LEFT, JoinType.RIGHT, JoinType.INNER, JoinType.FULL) both left_field and right_field must be supplied — they are the fields matched between the two tables. A JoinType.CROSS join pairs every left row with every right row and takes no match fields: left_field and right_field must both be omitted.

property left_table: Table

property left_field: Field | None

property right_table: Table

property right_field: Field | None

class JoinType(*values)

The type of join used by a JoinCondition to combine rows from two tables.

LEFT = 'LEFT'

Keep every row from the left table; attach matching right-table values where they exist, and leave the right-side cells blank otherwise.

Example — joining Orders (3 rows) to Customers (2 rows) on Orders.customer_id == Customers.id:

Orders               Customers
order_id  cust_id    cust_id  name
--------  -------    -------  -----
1         A          A        Alice
2         B          C        Carol
3         D

LEFT result (3 rows -- one per Orders row):
order_id  cust_id  name
--------  -------  -------
1         A        Alice
2         B        <blank>
3         D        <blank>

RIGHT = 'RIGHT'

Keep every row from the right table; attach matching left-table values where they exist, and leave the left-side cells blank otherwise. Mirror image of LEFT.

Same source tables as the LEFT example:

RIGHT result (2 rows -- one per Customers row):
order_id  cust_id  name
--------  -------  -----
1         A        Alice
<blank>   C        Carol

INNER = 'INNER'

Keep only rows where the join condition matches on both sides. Unmatched rows from either table are dropped.

Same source tables as the LEFT example:

INNER result (1 row -- only A matches on both sides):
order_id  cust_id  name
--------  -------  -----
1         A        Alice

FULL = 'FULL'

Keep every row from both tables. Where a row has no match on the other side, the missing cells are blank. Equivalent to a LEFT join unioned with a RIGHT join.

Same source tables as the LEFT example:

FULL result (4 rows -- 3 from Orders + 1 unmatched Customer):
order_id  cust_id  name
--------  -------  -------
1         A        Alice
2         B        <blank>
3         D        <blank>
<blank>   C        Carol

CROSS = 'CROSS'

Pair every row from the left table with every row from the right table (the Cartesian product). A CROSS join has no match fields: pass neither left_field nor right_field to the JoinCondition (supplying either raises ValueError).

Same source tables as the LEFT example:

CROSS result (6 rows -- 3 Orders x 2 Customers):
order_id  cust_id  name
--------  -------  -----
1         A        Alice
1         A        Carol
2         B        Alice
2         B        Carol
3         D        Alice
3         D        Carol