sqlalchemy.sql.expression.Join

Construct a new _expression.Join.

The usual entrypoint here is the _expression.join function or the _expression.FromClause.join method of any _expression.FromClause object.

Return an alias of this _expression.Join.

The default behavior here is to first produce a SELECT construct from this _expression.Join, then to produce an _expression.Alias from that. So given a join of the form:

j = table_a.join(table_b, table_a.c.id == table_b.c.a_id)

The JOIN by itself would look like:

table_a JOIN table_b ON table_a.id = table_b.a_id

Whereas the alias of the above, j.alias(), would in a SELECT context look like:

(SELECT table_a.id AS table_a_id, table_b.id AS table_b_id,
    table_b.a_id AS table_b_a_id
    FROM table_a
    JOIN table_b ON table_a.id = table_b.a_id) AS anon_1

The equivalent long-hand form, given a _expression.Join object j, is:

from sqlalchemy import select, alias
j = alias(
    select(j.left, j.right).\
        select_from(j).\
        set_label_style(LABEL_STYLE_TABLENAME_PLUS_COL).\
        correlate(False),
    name=name
)

The selectable produced by _expression.Join.alias features the same columns as that of the two individual selectables presented under a single name - the individual columns are "auto-labeled", meaning the .c. collection of the resulting _expression.Alias represents the names of the individual columns using a <tablename>_<columname> scheme:

j.c.table_a_id
j.c.table_b_a_id

_expression.Join.alias also features an alternate option for aliasing joins which produces no enclosing SELECT and does not normally apply labels to the column names. The flat=True option will call _expression.FromClause.alias against the left and right sides individually. Using this option, no new SELECT is produced; we instead, from a construct as below:

j = table_a.join(table_b, table_a.c.id == table_b.c.a_id)
j = j.alias(flat=True)

we get a result like this:

table_a AS table_a_1 JOIN table_b AS table_b_1 ON
table_a_1.id = table_b_1.a_id

The flat=True argument is also propagated to the contained selectables, so that a composite join such as:

j = table_a.join(
        table_b.join(table_c,
                table_b.c.id == table_c.c.b_id),
        table_b.c.a_id == table_a.c.id
    ).alias(flat=True)

Will produce an expression like:

table_a AS table_a_1 JOIN (
        table_b AS table_b_1 JOIN table_c AS table_c_1
        ON table_b_1.id = table_c_1.b_id
) ON table_a_1.id = table_b_1.a_id

The standalone _expression.alias function as well as the base _expression.FromClause.alias method also support the flat=True argument as a no-op, so that the argument can be passed to the alias() method of any selectable.

Return True if this _expression.FromClause is 'derived' from the given FromClause.

An example would be an Alias of a Table is derived from that Table.

Create a _expression.Select from this _expression.Join.

E.g.:

stmt = table_a.join(table_b, table_a.c.id == table_b.c.a_id)

stmt = stmt.select()

The above will produce a SQL string resembling:

SELECT table_a.id, table_a.col, table_b.id, table_b.a_id
FROM table_a JOIN table_b ON table_a.id = table_b.a_id

Apply a 'grouping' to this _expression.ClauseElement.

This method is overridden by subclasses to return a "grouping" construct, i.e. parenthesis. In particular it's used by "binary" expressions to provide a grouping around themselves when placed into a larger expression, as well as by _expression.select constructs when placed into the FROM clause of another _expression.select. (Note that subqueries should be normally created using the _expression.Select.alias method, as many platforms require nested SELECT statements to be named).

As expressions are composed together, the application of self_group is automatic - end-user code should never need to use this method directly. Note that SQLAlchemy's clause constructs take operator precedence into account - so parenthesis might not be needed, for example, in an expression like x OR (y AND z) - AND takes precedence over OR.

The base self_group method of _expression.ClauseElement just returns self.