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.

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, and FULL, 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.

• left_field: The field in the left table that is used to match with the right table.

• right_table: The right table involved in the join.

• right_field: The field in the right table that is used to match with the left table.

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

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, left_field, right_table, right_field, join_type)

Bases: Buildable

Represents a condition for joining two tables.

property left_table: Table

property left_field: Field

property right_table: Table

property right_field: Field

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