Method join

lib/sqlalchemy/sql/selectable.py:5781–5868

r"""Create a SQL JOIN against this :class:`_expression.Select` object's criterion and apply generatively, returning the newly resulting :class:`_expression.Select`. E.g.:: stmt = select(user_table).join( address_table, user_table.c.id ==

(
        self,
        target: _JoinTargetArgument,
        onclause: Optional[_OnClauseArgument] = None,
        *,
        isouter: bool = False,
        full: bool = False,
    )

    @_generative
    def join(
        self,
        target: _JoinTargetArgument,
        onclause: Optional[_OnClauseArgument] = None,
        *,
        isouter: bool = False,
        full: bool = False,
    ) -> Self:
        r"""Create a SQL JOIN against this :class:`_expression.Select`
        object's criterion
        and apply generatively, returning the newly resulting
        :class:`_expression.Select`.

        E.g.::

            stmt = select(user_table).join(
                address_table, user_table.c.id == address_table.c.user_id
            )

        The above statement generates SQL similar to:

        .. sourcecode:: sql

            SELECT user.id, user.name
            FROM user
            JOIN address ON user.id = address.user_id

        .. versionchanged:: 1.4 :meth:`_expression.Select.join` now creates
           a :class:`_sql.Join` object between a :class:`_sql.FromClause`
           source that is within the FROM clause of the existing SELECT,
           and a given target :class:`_sql.FromClause`, and then adds
           this :class:`_sql.Join` to the FROM clause of the newly generated
           SELECT statement.    This is completely reworked from the behavior
           in 1.3, which would instead create a subquery of the entire
           :class:`_expression.Select` and then join that subquery to the
           target.

           This is a **backwards incompatible change** as the previous behavior
           was mostly useless, producing an unnamed subquery rejected by
           most databases in any case.   The new behavior is modeled after
           that of the very successful :meth:`_orm.Query.join` method in the
           ORM, in order to support the functionality of :class:`_orm.Query`
           being available by using a :class:`_sql.Select` object with an
           :class:`_orm.Session`.

           See the notes for this change at :ref:`change_select_join`.


        :param target: target table to join towards

        :param onclause: ON clause of the join.  If omitted, an ON clause
         is generated automatically based on the :class:`_schema.ForeignKey`
         linkages between the two tables, if one can be unambiguously
         determined, otherwise an error is raised.

        :param isouter: if True, generate LEFT OUTER join.  Same as
         :meth:`_expression.Select.outerjoin`.