Function join

lib/sqlalchemy/orm/_orm_constructors.py:2590–2645

r"""Produce an inner join between left and right clauses. :func:`_orm.join` is an extension to the core join interface provided by :func:`_expression.join()`, where the left and right selectable may be not only core selectable objects such as :class:`_schema.Table`, but also mapped

(
    left: _FromClauseArgument,
    right: _FromClauseArgument,
    onclause: Optional[_OnClauseArgument] = None,
    isouter: bool = False,
    full: bool = False,
)

def join(
    left: _FromClauseArgument,
    right: _FromClauseArgument,
    onclause: Optional[_OnClauseArgument] = None,
    isouter: bool = False,
    full: bool = False,
) -> _ORMJoin:
    r"""Produce an inner join between left and right clauses.

    :func:`_orm.join` is an extension to the core join interface
    provided by :func:`_expression.join()`, where the
    left and right selectable may be not only core selectable
    objects such as :class:`_schema.Table`, but also mapped classes or
    :class:`.AliasedClass` instances.   The "on" clause can
    be a SQL expression or an ORM mapped attribute
    referencing a configured :func:`_orm.relationship`.

    :func:`_orm.join` is not commonly needed in modern usage,
    as its functionality is encapsulated within that of the
    :meth:`_sql.Select.join` and :meth:`_query.Query.join`
    methods. which feature a
    significant amount of automation beyond :func:`_orm.join`
    by itself.  Explicit use of :func:`_orm.join`
    with ORM-enabled SELECT statements involves use of the
    :meth:`_sql.Select.select_from` method, as in::

        from sqlalchemy.orm import join

        stmt = (
            select(User)
            .select_from(join(User, Address, User.addresses))
            .filter(Address.email_address == "foo@bar.com")
        )

    In modern SQLAlchemy the above join can be written more
    succinctly as::

        stmt = (
            select(User)
            .join(User.addresses)
            .filter(Address.email_address == "foo@bar.com")
        )

    .. warning:: using :func:`_orm.join` directly may not work properly
       with modern ORM options such as :func:`_orm.with_loader_criteria`.
       It is strongly recommended to use the idiomatic join patterns
       provided by methods such as :meth:`.Select.join` and
       :meth:`.Select.join_from` when creating ORM joins.

    .. seealso::

        :ref:`orm_queryguide_joins` - in the :ref:`queryguide_toplevel` for
        background on idiomatic ORM join patterns

    """
    return _ORMJoin(left, right, onclause, isouter, full)