Skip to content

Joins

tablite.joins

Classes

Functions

tablite.joins.join(T: BaseTable, other: BaseTable, left_keys: List[str], right_keys: List[str], left_columns: Union[List[str], None], right_columns: Union[List[str], None], kind: str = 'inner', merge_keys: bool = False, tqdm=_tqdm, pbar=None)

short-cut for all join functions.

PARAMETER DESCRIPTION
T

left table

TYPE: Table

other

right table

TYPE: Table

left_keys

list of keys for the join from left table.

TYPE: list

right_keys

list of keys for the join from right table.

TYPE: list

left_columns

list of columns names to retain from left table. If None, all are retained.

TYPE: list

right_columns

list of columns names to retain from right table. If None, all are retained.

TYPE: list

kind

'inner', 'left', 'outer', 'cross'. Defaults to "inner".

TYPE: str DEFAULT: 'inner'

tqdm

tqdm progress counter. Defaults to _tqdm.

TYPE: tqdm DEFAULT: tqdm

pbar

tqdm.progressbar. Defaults to None.

TYPE: pbar DEFAULT: None

RAISES DESCRIPTION
ValueError

if join type is unknown.
RETURNS DESCRIPTION
Table

joined table.

Example: "inner"

SQL:   SELECT number, letter FROM numbers JOIN letters ON numbers.colour == letters.color

Tablite: 

>>> inner_join = numbers.inner_join(
    letters, 
    left_keys=['colour'], 
    right_keys=['color'], 
    left_columns=['number'], 
    right_columns=['letter']
)

Example: "left" 

SQL:   SELECT number, letter FROM numbers LEFT JOIN letters ON numbers.colour == letters.color

Tablite: 

>>> left_join = numbers.left_join(
    letters, 
    left_keys=['colour'], 
    right_keys=['color'], 
    left_columns=['number'], 
    right_columns=['letter']
)

Example: "outer"

SQL:   SELECT number, letter FROM numbers OUTER JOIN letters ON numbers.colour == letters.color

Tablite: 

>>> outer_join = numbers.outer_join(
    letters, 
    left_keys=['colour'], 
    right_keys=['color'], 
    left_columns=['number'], 
    right_columns=['letter']
    )

Example: "cross"

CROSS JOIN returns the Cartesian product of rows from tables in the join. In other words, it will produce rows which combine each row from the first table with each row from the second table

Source code in tablite/joins.py 
 16
 17
 18
 19
 20
 21
 22
 23
 24
 25
 26
 27
 28
 29
 30
 31
 32
 33
 34
 35
 36
 37
 38
 39
 40
 41
 42
 43
 44
 45
 46
 47
 48
 49
 50
 51
 52
 53
 54
 55
 56
 57
 58
 59
 60
 61
 62
 63
 64
 65
 66
 67
 68
 69
 70
 71
 72
 73
 74
 75
 76
 77
 78
 79
 80
 81
 82
 83
 84
 85
 86
 87
 88
 89
 90
 91
 92
 93
 94
 95
 96
 97
 98
 99
100
101
102
103
104
105
106
107
108
109
110
def join(
    T: BaseTable,
    other: BaseTable,
    left_keys: List[str],
    right_keys: List[str],
    left_columns: Union[List[str], None],
    right_columns: Union[List[str], None],
    kind: str = "inner",
    merge_keys: bool = False,
    tqdm=_tqdm,
    pbar=None,
):
    """short-cut for all join functions.

    Args:
        T (Table): left table
        other (Table): right table
        left_keys (list): list of keys for the join from left table.
        right_keys (list): list of keys for the join from right table.
        left_columns (list): list of columns names to retain from left table.
            If None, all are retained.
        right_columns (list): list of columns names to retain from right table.
            If None, all are retained.
        kind (str, optional): 'inner', 'left', 'outer', 'cross'. Defaults to "inner".
        tqdm (tqdm, optional): tqdm progress counter. Defaults to _tqdm.
        pbar (tqdm.pbar, optional): tqdm.progressbar. Defaults to None.

    Raises:
        ValueError: if join type is unknown.

    Returns:
        Table: joined table.

    Example: "inner"
    ```
    SQL:   SELECT number, letter FROM numbers JOIN letters ON numbers.colour == letters.color
    ```
    Tablite: 
    ```
    >>> inner_join = numbers.inner_join(
        letters, 
        left_keys=['colour'], 
        right_keys=['color'], 
        left_columns=['number'], 
        right_columns=['letter']
    )
    ```

    Example: "left" 
    ```
    SQL:   SELECT number, letter FROM numbers LEFT JOIN letters ON numbers.colour == letters.color
    ```
    Tablite: 
    ```
    >>> left_join = numbers.left_join(
        letters, 
        left_keys=['colour'], 
        right_keys=['color'], 
        left_columns=['number'], 
        right_columns=['letter']
    )
    ```

    Example: "outer"
    ```
    SQL:   SELECT number, letter FROM numbers OUTER JOIN letters ON numbers.colour == letters.color
    ```

    Tablite: 
    ```
    >>> outer_join = numbers.outer_join(
        letters, 
        left_keys=['colour'], 
        right_keys=['color'], 
        left_columns=['number'], 
        right_columns=['letter']
        )
    ```

    Example: "cross"

    CROSS JOIN returns the Cartesian product of rows from tables in the join.
    In other words, it will produce rows which combine each row from the first table
    with each row from the second table
    """
    if left_columns is None:
        left_columns = list(T.columns)
    if right_columns is None:
        right_columns = list(other.columns)
    assert merge_keys in {True,False}

    _jointype_check(T, other, left_keys, right_keys, left_columns, right_columns)

    return _join(kind, T,other,left_keys, right_keys, left_columns, right_columns, merge_keys=merge_keys,
             tqdm=tqdm, pbar=pbar)

tablite.joins.inner_join(T: BaseTable, other: BaseTable, left_keys: List[str], right_keys: List[str], left_columns: Union[List[str], None], right_columns: Union[List[str], None], merge_keys: bool = False, tqdm=_tqdm, pbar=None)

Source code in tablite/joins.py 
113
114
115
116
def inner_join(T: BaseTable, other: BaseTable, left_keys: List[str], right_keys: List[str], 
              left_columns: Union[List[str], None], right_columns: Union[List[str], None],
              merge_keys: bool = False, tqdm=_tqdm, pbar=None):
    return join(T, other, left_keys, right_keys, left_columns, right_columns, kind="inner", merge_keys=merge_keys, tqdm=tqdm,pbar=pbar)

tablite.joins.left_join(T: BaseTable, other: BaseTable, left_keys: List[str], right_keys: List[str], left_columns: Union[List[str], None], right_columns: Union[List[str], None], merge_keys: bool = False, tqdm=_tqdm, pbar=None)

Source code in tablite/joins.py 
118
119
120
121
def left_join(T: BaseTable, other: BaseTable, left_keys: List[str], right_keys: List[str], 
              left_columns: Union[List[str], None], right_columns: Union[List[str], None],
              merge_keys: bool = False, tqdm=_tqdm, pbar=None):
    return join(T, other, left_keys, right_keys, left_columns, right_columns, kind="left", merge_keys=merge_keys, tqdm=tqdm,pbar=pbar)

tablite.joins.outer_join(T: BaseTable, other: BaseTable, left_keys: List[str], right_keys: List[str], left_columns: Union[List[str], None], right_columns: Union[List[str], None], merge_keys: bool = False, tqdm=_tqdm, pbar=None)

Source code in tablite/joins.py 
123
124
125
126
def outer_join(T: BaseTable, other: BaseTable, left_keys: List[str], right_keys: List[str], 
              left_columns: Union[List[str], None], right_columns: Union[List[str], None],
              merge_keys: bool = False, tqdm=_tqdm, pbar=None):
    return join(T, other, left_keys, right_keys, left_columns, right_columns, kind="outer", merge_keys=merge_keys, tqdm=tqdm,pbar=pbar)

tablite.joins.cross_join(T: BaseTable, other: BaseTable, left_keys: List[str], right_keys: List[str], left_columns: Union[List[str], None], right_columns: Union[List[str], None], merge_keys: bool = False, tqdm=_tqdm, pbar=None)

Source code in tablite/joins.py 
128
129
130
131
def cross_join(T: BaseTable, other: BaseTable, left_keys: List[str], right_keys: List[str], 
              left_columns: Union[List[str], None], right_columns: Union[List[str], None],
              merge_keys: bool = False, tqdm=_tqdm, pbar=None):
    return join(T, other, left_keys, right_keys, left_columns, right_columns, kind="cross", merge_keys=merge_keys, tqdm=tqdm,pbar=pbar)