# sql/selectable.py

# Copyright (C) 2005-2020 the SQLAlchemy authors and contributors

# <see AUTHORS file>

#

# This module is part of SQLAlchemy and is released under

# the MIT License: http://www.opensource.org/licenses/mit-license.php

"""The :class:`_expression.FromClause` class of SQL expression elements,

representing

SQL tables and derived rowsets.

"""

import collections

import itertools

from operator import attrgetter

from . import coercions

from . import operators

from . import roles

from . import type_api

from .annotation import Annotated

from .annotation import SupportsCloneAnnotations

from .base import _clone

from .base import _cloned_difference

from .base import _cloned_intersection

from .base import _expand_cloned

from .base import _from_objects

from .base import _generative

from .base import _select_iterables

from .base import CacheableOptions

from .base import ColumnCollection

from .base import ColumnSet

from .base import CompileState

from .base import DedupeColumnCollection

from .base import Executable

from .base import Generative

from .base import HasCompileState

from .base import HasMemoized

from .base import Immutable

from .base import prefix_anon_map

from .coercions import _document_text_coercion

from .elements import _anonymous_label

from .elements import and_

from .elements import BindParameter

from .elements import BooleanClauseList

from .elements import ClauseElement

from .elements import ClauseList

from .elements import ColumnClause

from .elements import GroupedElement

from .elements import Grouping

from .elements import literal_column

from .elements import UnaryExpression

from .visitors import InternalTraversal

from .. import exc

from .. import util

if util.TYPE_CHECKING:

from typing import Any

from typing import Optional

class _OffsetLimitParam(BindParameter):

@property

def _limit_offset_value(self):

return self.effective_value

@util.deprecated(

"1.4",

"The standalone :func:`.subquery` function is deprecated "

"and will be removed in a future release. Use select().subquery().",

)

def subquery(alias, *args, **kwargs):

r"""Return an :class:`.Subquery` object derived

from a :class:`_expression.Select`.

:param name: the alias name for the subquery

:param \*args, \**kwargs: all other arguments are passed through to the

:func:`_expression.select` function.

"""

return Select(*args, **kwargs).subquery(alias)

class ReturnsRows(roles.ReturnsRowsRole, ClauseElement):

"""The basemost class for Core constructs that have some concept of

columns that can represent rows.

While the SELECT statement and TABLE are the primary things we think

of in this category, DML like INSERT, UPDATE and DELETE can also specify

RETURNING which means they can be used in CTEs and other forms, and

PostgreSQL has functions that return rows also.

.. versionadded:: 1.4

"""

_is_returns_rows = True

# sub-elements of returns_rows

_is_from_clause = False

_is_select_statement = False

_is_lateral = False

@property

def selectable(self):

raise NotImplementedError()

def _exported_columns_iterator(self):

"""An iterator of column objects that represents the "exported"

columns of this :class:`_expression.ReturnsRows`.

This is the same set of columns as are returned by

:meth:`_expression.ReturnsRows.exported_columns`

except they are returned

as a simple iterator or sequence, rather than as a

:class:`_expression.ColumnCollection` namespace.

Subclasses should re-implement this method to bypass the interim

creation of the :class:`_expression.ColumnCollection` if appropriate.

"""

return iter(self.exported_columns)

@property

def exported_columns(self):

"""A :class:`_expression.ColumnCollection`

that represents the "exported"

columns of this :class:`_expression.ReturnsRows`.

The "exported" columns represent the collection of

:class:`_expression.ColumnElement`

expressions that are rendered by this SQL

construct. There are primary varieties which are the

"FROM clause columns" of a FROM clause, such as a table, join,

or subquery, the "SELECTed columns", which are the columns in

the "columns clause" of a SELECT statement, and the RETURNING

columns in a DML statement..

.. versionadded:: 1.4

.. seealso:

:attr:`_expression.FromClause.exported_columns`

:attr:`_expression.SelectBase.exported_columns`

"""

raise NotImplementedError()

class Selectable(ReturnsRows):

"""mark a class as being selectable.

"""

__visit_name__ = "selectable"

is_selectable = True

@property

def selectable(self):

return self

def _refresh_for_new_column(self, column):

raise NotImplementedError()

def lateral(self, name=None):

"""Return a LATERAL alias of this :class:`expression.Selectable`.

The return value is the :class:`_expression.Lateral` construct also

provided by the top-level :func:`_expression.lateral` function.

.. versionadded:: 1.1

.. seealso::

:ref:`lateral_selects` - overview of usage.

"""

return Lateral._construct(self, name)

@util.deprecated(

"1.4",

message="The :meth:`.Selectable.replace_selectable` method is "

"deprecated, and will be removed in a future release. Similar "

"functionality is available via the sqlalchemy.sql.visitors module.",

)

@util.preload_module("sqlalchemy.sql.util")

def replace_selectable(self, old, alias):

"""replace all occurrences of FromClause 'old' with the given Alias

object, returning a copy of this :class:`_expression.FromClause`.

"""

return util.preloaded.sql_util.ClauseAdapter(alias).traverse(self)

def corresponding_column(self, column, require_embedded=False):

"""Given a :class:`_expression.ColumnElement`, return the exported

:class:`_expression.ColumnElement` object from the

:attr:`expression.Selectable.exported_columns`

collection of this :class:`expression.Selectable`

which corresponds to that

original :class:`_expression.ColumnElement` via a common ancestor

column.

:param column: the target :class:`_expression.ColumnElement`

to be matched

:param require_embedded: only return corresponding columns for

the given :class:`_expression.ColumnElement`, if the given

:class:`_expression.ColumnElement`

is actually present within a sub-element

of this :class:`expression.Selectable`.

Normally the column will match if

it merely shares a common ancestor with one of the exported

columns of this :class:`expression.Selectable`.

.. seealso::

:attr:`expression.Selectable.exported_columns` - the

:class:`_expression.ColumnCollection`

that is used for the operation

:meth:`_expression.ColumnCollection.corresponding_column`

- implementation

method.

"""

return self.exported_columns.corresponding_column(

column, require_embedded

)

class HasPrefixes(object):

_prefixes = ()

_has_prefixes_traverse_internals = [

("_prefixes", InternalTraversal.dp_prefix_sequence)

]

@_generative

@_document_text_coercion(

"expr",

":meth:`_expression.HasPrefixes.prefix_with`",

":paramref:`.HasPrefixes.prefix_with.*expr`",

)

def prefix_with(self, *expr, **kw):

r"""Add one or more expressions following the statement keyword, i.e.

SELECT, INSERT, UPDATE, or DELETE. Generative.

This is used to support backend-specific prefix keywords such as those

provided by MySQL.

E.g.::

stmt = table.insert().prefix_with("LOW_PRIORITY", dialect="mysql")

# MySQL 5.7 optimizer hints

stmt = select([table]).prefix_with(

"/*+ BKA(t1) */", dialect="mysql")

Multiple prefixes can be specified by multiple calls

to :meth:`_expression.HasPrefixes.prefix_with`.

:param \*expr: textual or :class:`_expression.ClauseElement`

construct which

will be rendered following the INSERT, UPDATE, or DELETE

keyword.

:param \**kw: A single keyword 'dialect' is accepted. This is an

optional string dialect name which will

limit rendering of this prefix to only that dialect.

"""

dialect = kw.pop("dialect", None)

if kw:

raise exc.ArgumentError(

"Unsupported argument(s): %s" % ",".join(kw)

)

self._setup_prefixes(expr, dialect)

def _setup_prefixes(self, prefixes, dialect=None):

self._prefixes = self._prefixes + tuple(

[

(coercions.expect(roles.StatementOptionRole, p), dialect)

for p in prefixes

]

)

class HasSuffixes(object):

_suffixes = ()

_has_suffixes_traverse_internals = [

("_suffixes", InternalTraversal.dp_prefix_sequence)

]

@_generative

@_document_text_coercion(

"expr",

":meth:`_expression.HasSuffixes.suffix_with`",

":paramref:`.HasSuffixes.suffix_with.*expr`",

)

def suffix_with(self, *expr, **kw):

r"""Add one or more expressions following the statement as a whole.

This is used to support backend-specific suffix keywords on

certain constructs.

E.g.::

stmt = select([col1, col2]).cte().suffix_with(

"cycle empno set y_cycle to 1 default 0", dialect="oracle")

Multiple suffixes can be specified by multiple calls

to :meth:`_expression.HasSuffixes.suffix_with`.

:param \*expr: textual or :class:`_expression.ClauseElement`

construct which

will be rendered following the target clause.

:param \**kw: A single keyword 'dialect' is accepted. This is an

optional string dialect name which will

limit rendering of this suffix to only that dialect.

"""

dialect = kw.pop("dialect", None)

if kw:

raise exc.ArgumentError(

"Unsupported argument(s): %s" % ",".join(kw)

)

self._setup_suffixes(expr, dialect)

def _setup_suffixes(self, suffixes, dialect=None):

self._suffixes = self._suffixes + tuple(

[

(coercions.expect(roles.StatementOptionRole, p), dialect)

for p in suffixes

]

)

class HasHints(object):

_hints = util.immutabledict()

_statement_hints = ()

_has_hints_traverse_internals = [

("_statement_hints", InternalTraversal.dp_statement_hint_list),

("_hints", InternalTraversal.dp_table_hint_list),

]

def with_statement_hint(self, text, dialect_name="*"):

"""add a statement hint to this :class:`_expression.Select` or

other selectable object.

This method is similar to :meth:`_expression.Select.with_hint`

except that

it does not require an individual table, and instead applies to the

statement as a whole.

Hints here are specific to the backend database and may include

directives such as isolation levels, file directives, fetch directives,

etc.

.. versionadded:: 1.0.0

.. seealso::

:meth:`_expression.Select.with_hint`

:meth:.`.Select.prefix_with` - generic SELECT prefixing which also

can suit some database-specific HINT syntaxes such as MySQL

optimizer hints

"""

return self.with_hint(None, text, dialect_name)

@_generative

def with_hint(self, selectable, text, dialect_name="*"):

r"""Add an indexing or other executional context hint for the given

selectable to this :class:`_expression.Select` or other selectable

object.

The text of the hint is rendered in the appropriate

location for the database backend in use, relative

to the given :class:`_schema.Table` or :class:`_expression.Alias`

passed as the

``selectable`` argument. The dialect implementation

typically uses Python string substitution syntax

with the token ``%(name)s`` to render the name of

the table or alias. E.g. when using Oracle, the

following::

select([mytable]).\

with_hint(mytable, "index(%(name)s ix_mytable)")

Would render SQL as::

select /*+ index(mytable ix_mytable) */ ... from mytable

The ``dialect_name`` option will limit the rendering of a particular

hint to a particular backend. Such as, to add hints for both Oracle

and Sybase simultaneously::

select([mytable]).\

with_hint(mytable, "index(%(name)s ix_mytable)", 'oracle').\

with_hint(mytable, "WITH INDEX ix_mytable", 'sybase')

.. seealso::

:meth:`_expression.Select.with_statement_hint`

"""

if selectable is None:

self._statement_hints += ((dialect_name, text),)

else:

self._hints = self._hints.union(

{

(

coercions.expect(roles.FromClauseRole, selectable),

dialect_name,

): text

}

)

class FromClause(roles.AnonymizedFromClauseRole, Selectable):

"""Represent an element that can be used within the ``FROM``

clause of a ``SELECT`` statement.

The most common forms of :class:`_expression.FromClause` are the

:class:`_schema.Table` and the :func:`_expression.select` constructs. Key

features common to all :class:`_expression.FromClause` objects include:

* a :attr:`.c` collection, which provides per-name access to a collection

of :class:`_expression.ColumnElement` objects.

* a :attr:`.primary_key` attribute, which is a collection of all those

:class:`_expression.ColumnElement`

objects that indicate the ``primary_key`` flag.

* Methods to generate various derivations of a "from" clause, including

:meth:`_expression.FromClause.alias`,

:meth:`_expression.FromClause.join`,

:meth:`_expression.FromClause.select`.

"""

__visit_name__ = "fromclause"

named_with_column = False

_hide_froms = []

schema = None

"""Define the 'schema' attribute for this :class:`_expression.FromClause`.

This is typically ``None`` for most objects except that of

:class:`_schema.Table`, where it is taken as the value of the

:paramref:`_schema.Table.schema` argument.

"""

is_selectable = True

_is_from_clause = True

_is_join = False

_use_schema_map = False

def select(self, whereclause=None, **params):

"""return a SELECT of this :class:`_expression.FromClause`.

.. seealso::

:func:`_expression.select` - general purpose

method which allows for arbitrary column lists.

"""

return Select([self], whereclause, **params)

def join(self, right, onclause=None, isouter=False, full=False):

"""Return a :class:`_expression.Join` from this

:class:`_expression.FromClause`

to another :class:`FromClause`.

E.g.::

from sqlalchemy import join

j = user_table.join(address_table,

user_table.c.id == address_table.c.user_id)

stmt = select([user_table]).select_from(j)

would emit SQL along the lines of::

SELECT user.id, user.name FROM user

JOIN address ON user.id = address.user_id

:param right: the right side of the join; this is any

:class:`_expression.FromClause` object such as a

:class:`_schema.Table` object, and

may also be a selectable-compatible object such as an ORM-mapped

class.

:param onclause: a SQL expression representing the ON clause of the

join. If left at ``None``, :meth:`_expression.FromClause.join`

will attempt to

join the two tables based on a foreign key relationship.

:param isouter: if True, render a LEFT OUTER JOIN, instead of JOIN.

:param full: if True, render a FULL OUTER JOIN, instead of LEFT OUTER

JOIN. Implies :paramref:`.FromClause.join.isouter`.

.. versionadded:: 1.1

.. seealso::

:func:`_expression.join` - standalone function

:class:`_expression.Join` - the type of object produced

"""

return Join(self, right, onclause, isouter, full)

def outerjoin(self, right, onclause=None, full=False):

"""Return a :class:`_expression.Join` from this

:class:`_expression.FromClause`

to another :class:`FromClause`, with the "isouter" flag set to

True.

E.g.::

from sqlalchemy import outerjoin

j = user_table.outerjoin(address_table,

user_table.c.id == address_table.c.user_id)

The above is equivalent to::

j = user_table.join(

address_table,

user_table.c.id == address_table.c.user_id,

isouter=True)

:param right: the right side of the join; this is any

:class:`_expression.FromClause` object such as a

:class:`_schema.Table` object, and

may also be a selectable-compatible object such as an ORM-mapped

class.

:param onclause: a SQL expression representing the ON clause of the

join. If left at ``None``, :meth:`_expression.FromClause.join`

will attempt to

join the two tables based on a foreign key relationship.

:param full: if True, render a FULL OUTER JOIN, instead of

LEFT OUTER JOIN.

.. versionadded:: 1.1

.. seealso::

:meth:`_expression.FromClause.join`

:class:`_expression.Join`

"""

return Join(self, right, onclause, True, full)

def alias(self, name=None, flat=False):

"""return an alias of this :class:`_expression.FromClause`.

E.g.::

a2 = some_table.alias('a2')

The above code creates an :class:`_expression.Alias`

object which can be used

as a FROM clause in any SELECT statement.

.. seealso::

:ref:`core_tutorial_aliases`

:func:`_expression.alias`

"""

return Alias._construct(self, name)

def tablesample(self, sampling, name=None, seed=None):

"""Return a TABLESAMPLE alias of this :class:`_expression.FromClause`.

The return value is the :class:`_expression.TableSample`

construct also

provided by the top-level :func:`_expression.tablesample` function.

.. versionadded:: 1.1

.. seealso::

:func:`_expression.tablesample` - usage guidelines and parameters

"""

return TableSample._construct(self, sampling, name, seed)

def is_derived_from(self, fromclause):

"""Return True if this FromClause is 'derived' from the given

FromClause.

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

"""

# this is essentially an "identity" check in the base class.

# Other constructs override this to traverse through

# contained elements.

return fromclause in self._cloned_set

def _is_lexical_equivalent(self, other):

"""Return True if this FromClause and the other represent

the same lexical identity.

This tests if either one is a copy of the other, or

if they are the same via annotation identity.

"""

return self._cloned_set.intersection(other._cloned_set)

@property

def description(self):

"""a brief description of this FromClause.

Used primarily for error message formatting.

"""

return getattr(self, "name", self.__class__.__name__ + " object")

def _generate_fromclause_column_proxies(self, fromclause):

fromclause._columns._populate_separate_keys(

col._make_proxy(fromclause) for col in self.c

)

@property

def exported_columns(self):

"""A :class:`_expression.ColumnCollection`

that represents the "exported"

columns of this :class:`expression.Selectable`.

The "exported" columns for a :class:`_expression.FromClause`

object are synonymous

with the :attr:`_expression.FromClause.columns` collection.

.. versionadded:: 1.4

.. seealso:

:attr:`expression.Selectable.exported_columns`

:attr:`_expression.SelectBase.exported_columns`

"""

return self.columns

@util.memoized_property

def columns(self):

"""A named-based collection of :class:`_expression.ColumnElement`

objects

maintained by this :class:`_expression.FromClause`.

The :attr:`.columns`, or :attr:`.c` collection, is the gateway

to the construction of SQL expressions using table-bound or

other selectable-bound columns::

select([mytable]).where(mytable.c.somecolumn == 5)

"""

if "_columns" not in self.__dict__:

self._init_collections()

self._populate_column_collection()

return self._columns.as_immutable()

@property

def entity_namespace(self):

"""Return a namespace used for name-based access in SQL expressions.

This is the namespace that is used to resolve "filter_by()" type

expressions, such as::

stmt.filter_by(address='some address')

It defaults to the .c collection, however internally it can

be overridden using the "entity_namespace" annotation to deliver

alternative results.

"""

return self.columns

@util.memoized_property

def primary_key(self):

"""Return the collection of Column objects which comprise the

primary key of this FromClause."""

self._init_collections()

self._populate_column_collection()

return self.primary_key

@util.memoized_property

def foreign_keys(self):

"""Return the collection of ForeignKey objects which this

FromClause references."""

self._init_collections()

self._populate_column_collection()

return self.foreign_keys

def _reset_column_collection(self):

"""Reset the attributes linked to the FromClause.c attribute.

This collection is separate from all the other memoized things

as it has shown to be sensitive to being cleared out in situations

where enclosing code, typically in a replacement traversal scenario,

has already established strong relationships

with the exported columns.

The collection is cleared for the case where a table is having a

column added to it as well as within a Join during copy internals.

"""

for key in ["_columns", "columns", "primary_key", "foreign_keys"]:

self.__dict__.pop(key, None)

c = property(

attrgetter("columns"),

doc="An alias for the :attr:`.columns` attribute.",

)

_select_iterable = property(attrgetter("columns"))

def _init_collections(self):

assert "_columns" not in self.__dict__

assert "primary_key" not in self.__dict__

assert "foreign_keys" not in self.__dict__

self._columns = ColumnCollection()

self.primary_key = ColumnSet()

self.foreign_keys = set()

@property

def _cols_populated(self):

return "_columns" in self.__dict__

def _populate_column_collection(self):

"""Called on subclasses to establish the .c collection.

Each implementation has a different way of establishing

this collection.

"""

def _refresh_for_new_column(self, column):

"""Given a column added to the .c collection of an underlying

selectable, produce the local version of that column, assuming this

selectable ultimately should proxy this column.

this is used to "ping" a derived selectable to add a new column

to its .c. collection when a Column has been added to one of the

Table objects it ultimtely derives from.

If the given selectable hasn't populated its .c. collection yet,

it should at least pass on the message to the contained selectables,

but it will return None.

This method is currently used by Declarative to allow Table

columns to be added to a partially constructed inheritance

mapping that may have already produced joins. The method

isn't public right now, as the full span of implications

and/or caveats aren't yet clear.

It's also possible that this functionality could be invoked by

default via an event, which would require that

selectables maintain a weak referencing collection of all

derivations.

"""

self._reset_column_collection()

class Join(FromClause):

"""represent a ``JOIN`` construct between two

:class:`_expression.FromClause`

elements.

The public constructor function for :class:`_expression.Join`

is the module-level

:func:`_expression.join()` function, as well as the

:meth:`_expression.FromClause.join` method

of any :class:`_expression.FromClause` (e.g. such as

:class:`_schema.Table`).

.. seealso::

:func:`_expression.join`

:meth:`_expression.FromClause.join`

"""

__visit_name__ = "join"

_traverse_internals = [

("left", InternalTraversal.dp_clauseelement),

("right", InternalTraversal.dp_clauseelement),

("onclause", InternalTraversal.dp_clauseelement),

("isouter", InternalTraversal.dp_boolean),

("full", InternalTraversal.dp_boolean),

]

_is_join = True

def __init__(self, left, right, onclause=None, isouter=False, full=False):

"""Construct a new :class:`_expression.Join`.

The usual entrypoint here is the :func:`_expression.join`

function or the :meth:`_expression.FromClause.join` method of any

:class:`_expression.FromClause` object.

"""

self.left = coercions.expect(

roles.FromClauseRole, left, deannotate=True

)

self.right = coercions.expect(

roles.FromClauseRole, right, deannotate=True

).self_group()

if onclause is None:

self.onclause = self._match_primaries(self.left, self.right)

else:

# note: taken from If91f61527236fd4d7ae3cad1f24c38be921c90ba

# not merged yet

self.onclause = coercions.expect(

roles.WhereHavingRole, onclause

).self_group(against=operators._asbool)

self.isouter = isouter

self.full = full

@classmethod

def _create_outerjoin(cls, left, right, onclause=None, full=False):

"""Return an ``OUTER JOIN`` clause element.

The returned object is an instance of :class:`_expression.Join`.

Similar functionality is also available via the

:meth:`_expression.FromClause.outerjoin()` method on any

:class:`_expression.FromClause`.

:param left: The left side of the join.

:param right: The right side of the join.

:param onclause: Optional criterion for the ``ON`` clause, is

derived from foreign key relationships established between

left and right otherwise.

To chain joins together, use the :meth:`_expression.FromClause.join`

or

:meth:`_expression.FromClause.outerjoin` methods on the resulting

:class:`_expression.Join` object.

"""

return cls(left, right, onclause, isouter=True, full=full)

@classmethod

def _create_join(

cls, left, right, onclause=None, isouter=False, full=False

):

"""Produce a :class:`_expression.Join` object, given two

:class:`_expression.FromClause`

expressions.

E.g.::

j = join(user_table, address_table,

user_table.c.id == address_table.c.user_id)

stmt = select([user_table]).select_from(j)

would emit SQL along the lines of::

SELECT user.id, user.name FROM user

JOIN address ON user.id = address.user_id

Similar functionality is available given any

:class:`_expression.FromClause` object (e.g. such as a

:class:`_schema.Table`) using

the :meth:`_expression.FromClause.join` method.

:param left: The left side of the join.

:param right: the right side of the join; this is any

:class:`_expression.FromClause` object such as a

:class:`_schema.Table` object, and

may also be a selectable-compatible object such as an ORM-mapped

class.

:param onclause: a SQL expression representing the ON clause of the

join. If left at ``None``, :meth:`_expression.FromClause.join`

will attempt to

join the two tables based on a foreign key relationship.

:param isouter: if True, render a LEFT OUTER JOIN, instead of JOIN.

:param full: if True, render a FULL OUTER JOIN, instead of JOIN.

.. versionadded:: 1.1

.. seealso::

:meth:`_expression.FromClause.join` - method form,

based on a given left side

:class:`_expression.Join` - the type of object produced

"""

return cls(left, right, onclause, isouter, full)

@property

def description(self):

return "Join object on %s(%d) and %s(%d)" % (

self.left.description,

id(self.left),

self.right.description,

id(self.right),

)

def is_derived_from(self, fromclause):

return (

fromclause is self

or self.left.is_derived_from(fromclause)

or self.right.is_derived_from(fromclause)

)

def self_group(self, against=None):

return FromGrouping(self)

@util.preload_module("sqlalchemy.sql.util")

def _populate_column_collection(self):

sqlutil = util.preloaded.sql_util

columns = [c for c in self.left.columns] + [

c for c in self.right.columns

]

self.primary_key.extend(

sqlutil.reduce_columns(

(c for c in columns if c.primary_key), self.onclause

)

)

self._columns._populate_separate_keys(

(col._key_label, col) for col in columns

)

self.foreign_keys.update(

itertools.chain(*[col.foreign_keys for col in columns])

)

def _refresh_for_new_column(self, column):

super(Join, self)._refresh_for_new_column(column)

self.left._refresh_for_new_column(column)

self.right._refresh_for_new_column(column)

def _match_primaries(self, left, right):

if isinstance(left, Join):

left_right = left.right

else:

left_right = None

return self._join_condition(left, right, a_subset=left_right)

@classmethod

def _join_condition(

cls, a, b, a_subset=None, consider_as_foreign_keys=None

):

"""create a join condition between two tables or selectables.

e.g.::

join_condition(tablea, tableb)

would produce an expression along the lines of::

tablea.c.id==tableb.c.tablea_id

The join is determined based on the foreign key relationships

between the two selectables. If there are multiple ways

to join, or no way to join, an error is raised.

:param a_subset: An optional expression that is a sub-component

of ``a``. An attempt will be made to join to just this sub-component