Shortcuts on this page
r m x toggle line displays
j k next/prev highlighted chunk
0 (zero) top of page
1 (one) first highlighted chunk
1# sql/_selectable_constructors.py
2# Copyright (C) 2005-2025 the SQLAlchemy authors and contributors
3# <see AUTHORS file>
4#
5# This module is part of SQLAlchemy and is released under
6# the MIT License: https://www.opensource.org/licenses/mit-license.php
8from __future__ import annotations
10from typing import Any
11from typing import Optional
12from typing import overload
13from typing import TYPE_CHECKING
14from typing import Union
16from . import coercions
17from . import roles
18from ._typing import _ColumnsClauseArgument
19from ._typing import _no_kw
20from .elements import ColumnClause
21from .selectable import Alias
22from .selectable import CompoundSelect
23from .selectable import Exists
24from .selectable import FromClause
25from .selectable import Join
26from .selectable import Lateral
27from .selectable import LateralFromClause
28from .selectable import NamedFromClause
29from .selectable import Select
30from .selectable import TableClause
31from .selectable import TableSample
32from .selectable import Values
33from ..util.typing import TupleAny
34from ..util.typing import Unpack
36if TYPE_CHECKING:
37 from ._typing import _FromClauseArgument
38 from ._typing import _OnClauseArgument
39 from ._typing import _SelectStatementForCompoundArgument
40 from ._typing import _T0
41 from ._typing import _T1
42 from ._typing import _T2
43 from ._typing import _T3
44 from ._typing import _T4
45 from ._typing import _T5
46 from ._typing import _T6
47 from ._typing import _T7
48 from ._typing import _T8
49 from ._typing import _T9
50 from ._typing import _Ts
51 from ._typing import _TypedColumnClauseArgument as _TCCA
52 from .functions import Function
53 from .selectable import CTE
54 from .selectable import HasCTE
55 from .selectable import ScalarSelect
56 from .selectable import SelectBase
59def alias(
60 selectable: FromClause, name: Optional[str] = None, flat: bool = False
61) -> NamedFromClause:
62 """Return a named alias of the given :class:`.FromClause`.
64 For :class:`.Table` and :class:`.Join` objects, the return type is the
65 :class:`_expression.Alias` object. Other kinds of :class:`.NamedFromClause`
66 objects may be returned for other kinds of :class:`.FromClause` objects.
68 The named alias represents any :class:`_expression.FromClause` with an
69 alternate name assigned within SQL, typically using the ``AS`` clause when
70 generated, e.g. ``SELECT * FROM table AS aliasname``.
72 Equivalent functionality is available via the
73 :meth:`_expression.FromClause.alias`
74 method available on all :class:`_expression.FromClause` objects.
76 :param selectable: any :class:`_expression.FromClause` subclass,
77 such as a table, select statement, etc.
79 :param name: string name to be assigned as the alias.
80 If ``None``, a name will be deterministically generated at compile
81 time. Deterministic means the name is guaranteed to be unique against
82 other constructs used in the same statement, and will also be the same
83 name for each successive compilation of the same statement object.
85 :param flat: Will be passed through to if the given selectable
86 is an instance of :class:`_expression.Join` - see
87 :meth:`_expression.Join.alias` for details.
89 """
90 return Alias._factory(selectable, name=name, flat=flat)
93def cte(
94 selectable: HasCTE, name: Optional[str] = None, recursive: bool = False
95) -> CTE:
96 r"""Return a new :class:`_expression.CTE`,
97 or Common Table Expression instance.
99 Please see :meth:`_expression.HasCTE.cte` for detail on CTE usage.
101 """
102 return coercions.expect(roles.HasCTERole, selectable).cte(
103 name=name, recursive=recursive
104 )
107# TODO: mypy requires the _TypedSelectable overloads in all compound select
108# constructors since _SelectStatementForCompoundArgument includes
109# untyped args that make it return CompoundSelect[Unpack[tuple[Never, ...]]]
110# pyright does not have this issue
111_TypedSelectable = Union["Select[Unpack[_Ts]]", "CompoundSelect[Unpack[_Ts]]"]
114@overload
115def except_(
116 *selects: _TypedSelectable[Unpack[_Ts]],
117) -> CompoundSelect[Unpack[_Ts]]: ...
120@overload
121def except_(
122 *selects: _SelectStatementForCompoundArgument[Unpack[_Ts]],
123) -> CompoundSelect[Unpack[_Ts]]: ...
126def except_(
127 *selects: _SelectStatementForCompoundArgument[Unpack[_Ts]],
128) -> CompoundSelect[Unpack[_Ts]]:
129 r"""Return an ``EXCEPT`` of multiple selectables.
131 The returned object is an instance of
132 :class:`_expression.CompoundSelect`.
134 :param \*selects:
135 a list of :class:`_expression.Select` instances.
137 """
138 return CompoundSelect._create_except(*selects)
141@overload
142def except_all(
143 *selects: _TypedSelectable[Unpack[_Ts]],
144) -> CompoundSelect[Unpack[_Ts]]: ...
147@overload
148def except_all(
149 *selects: _SelectStatementForCompoundArgument[Unpack[_Ts]],
150) -> CompoundSelect[Unpack[_Ts]]: ...
153def except_all(
154 *selects: _SelectStatementForCompoundArgument[Unpack[_Ts]],
155) -> CompoundSelect[Unpack[_Ts]]:
156 r"""Return an ``EXCEPT ALL`` of multiple selectables.
158 The returned object is an instance of
159 :class:`_expression.CompoundSelect`.
161 :param \*selects:
162 a list of :class:`_expression.Select` instances.
164 """
165 return CompoundSelect._create_except_all(*selects)
168def exists(
169 __argument: Optional[
170 Union[_ColumnsClauseArgument[Any], SelectBase, ScalarSelect[Any]]
171 ] = None,
172 /,
173) -> Exists:
174 """Construct a new :class:`_expression.Exists` construct.
176 The :func:`_sql.exists` can be invoked by itself to produce an
177 :class:`_sql.Exists` construct, which will accept simple WHERE
178 criteria::
180 exists_criteria = exists().where(table1.c.col1 == table2.c.col2)
182 However, for greater flexibility in constructing the SELECT, an
183 existing :class:`_sql.Select` construct may be converted to an
184 :class:`_sql.Exists`, most conveniently by making use of the
185 :meth:`_sql.SelectBase.exists` method::
187 exists_criteria = (
188 select(table2.c.col2).where(table1.c.col1 == table2.c.col2).exists()
189 )
191 The EXISTS criteria is then used inside of an enclosing SELECT::
193 stmt = select(table1.c.col1).where(exists_criteria)
195 The above statement will then be of the form:
197 .. sourcecode:: sql
199 SELECT col1 FROM table1 WHERE EXISTS
200 (SELECT table2.col2 FROM table2 WHERE table2.col2 = table1.col1)
202 .. seealso::
204 :ref:`tutorial_exists` - in the :term:`2.0 style` tutorial.
206 :meth:`_sql.SelectBase.exists` - method to transform a ``SELECT`` to an
207 ``EXISTS`` clause.
209 """ # noqa: E501
211 return Exists(__argument)
214@overload
215def intersect(
216 *selects: _TypedSelectable[Unpack[_Ts]],
217) -> CompoundSelect[Unpack[_Ts]]: ...
220@overload
221def intersect(
222 *selects: _SelectStatementForCompoundArgument[Unpack[_Ts]],
223) -> CompoundSelect[Unpack[_Ts]]: ...
226def intersect(
227 *selects: _SelectStatementForCompoundArgument[Unpack[_Ts]],
228) -> CompoundSelect[Unpack[_Ts]]:
229 r"""Return an ``INTERSECT`` of multiple selectables.
231 The returned object is an instance of
232 :class:`_expression.CompoundSelect`.
234 :param \*selects:
235 a list of :class:`_expression.Select` instances.
237 """
238 return CompoundSelect._create_intersect(*selects)
241@overload
242def intersect_all(
243 *selects: _TypedSelectable[Unpack[_Ts]],
244) -> CompoundSelect[Unpack[_Ts]]: ...
247@overload
248def intersect_all(
249 *selects: _SelectStatementForCompoundArgument[Unpack[_Ts]],
250) -> CompoundSelect[Unpack[_Ts]]: ...
253def intersect_all(
254 *selects: _SelectStatementForCompoundArgument[Unpack[_Ts]],
255) -> CompoundSelect[Unpack[_Ts]]:
256 r"""Return an ``INTERSECT ALL`` of multiple selectables.
258 The returned object is an instance of
259 :class:`_expression.CompoundSelect`.
261 :param \*selects:
262 a list of :class:`_expression.Select` instances.
265 """
266 return CompoundSelect._create_intersect_all(*selects)
269def join(
270 left: _FromClauseArgument,
271 right: _FromClauseArgument,
272 onclause: Optional[_OnClauseArgument] = None,
273 isouter: bool = False,
274 full: bool = False,
275) -> Join:
276 """Produce a :class:`_expression.Join` object, given two
277 :class:`_expression.FromClause`
278 expressions.
280 E.g.::
282 j = join(
283 user_table, address_table, user_table.c.id == address_table.c.user_id
284 )
285 stmt = select(user_table).select_from(j)
287 would emit SQL along the lines of:
289 .. sourcecode:: sql
291 SELECT user.id, user.name FROM user
292 JOIN address ON user.id = address.user_id
294 Similar functionality is available given any
295 :class:`_expression.FromClause` object (e.g. such as a
296 :class:`_schema.Table`) using
297 the :meth:`_expression.FromClause.join` method.
299 :param left: The left side of the join.
301 :param right: the right side of the join; this is any
302 :class:`_expression.FromClause` object such as a
303 :class:`_schema.Table` object, and
304 may also be a selectable-compatible object such as an ORM-mapped
305 class.
307 :param onclause: a SQL expression representing the ON clause of the
308 join. If left at ``None``, :meth:`_expression.FromClause.join`
309 will attempt to
310 join the two tables based on a foreign key relationship.
312 :param isouter: if True, render a LEFT OUTER JOIN, instead of JOIN.
314 :param full: if True, render a FULL OUTER JOIN, instead of JOIN.
316 .. seealso::
318 :meth:`_expression.FromClause.join` - method form,
319 based on a given left side.
321 :class:`_expression.Join` - the type of object produced.
323 """ # noqa: E501
325 return Join(left, right, onclause, isouter, full)
328def lateral(
329 selectable: Union[SelectBase, _FromClauseArgument],
330 name: Optional[str] = None,
331) -> LateralFromClause:
332 """Return a :class:`_expression.Lateral` object.
334 :class:`_expression.Lateral` is an :class:`_expression.Alias`
335 subclass that represents
336 a subquery with the LATERAL keyword applied to it.
338 The special behavior of a LATERAL subquery is that it appears in the
339 FROM clause of an enclosing SELECT, but may correlate to other
340 FROM clauses of that SELECT. It is a special case of subquery
341 only supported by a small number of backends, currently more recent
342 PostgreSQL versions.
344 .. seealso::
346 :ref:`tutorial_lateral_correlation` - overview of usage.
348 """
349 return Lateral._factory(selectable, name=name)
352def outerjoin(
353 left: _FromClauseArgument,
354 right: _FromClauseArgument,
355 onclause: Optional[_OnClauseArgument] = None,
356 full: bool = False,
357) -> Join:
358 """Return an ``OUTER JOIN`` clause element.
360 The returned object is an instance of :class:`_expression.Join`.
362 Similar functionality is also available via the
363 :meth:`_expression.FromClause.outerjoin` method on any
364 :class:`_expression.FromClause`.
366 :param left: The left side of the join.
368 :param right: The right side of the join.
370 :param onclause: Optional criterion for the ``ON`` clause, is
371 derived from foreign key relationships established between
372 left and right otherwise.
374 To chain joins together, use the :meth:`_expression.FromClause.join`
375 or
376 :meth:`_expression.FromClause.outerjoin` methods on the resulting
377 :class:`_expression.Join` object.
379 """
380 return Join(left, right, onclause, isouter=True, full=full)
383# START OVERLOADED FUNCTIONS select Select 1-10
385# code within this block is **programmatically,
386# statically generated** by tools/generate_tuple_map_overloads.py
389@overload
390def select(__ent0: _TCCA[_T0], /) -> Select[_T0]: ...
393@overload
394def select(__ent0: _TCCA[_T0], __ent1: _TCCA[_T1], /) -> Select[_T0, _T1]: ...
397@overload
398def select(
399 __ent0: _TCCA[_T0], __ent1: _TCCA[_T1], __ent2: _TCCA[_T2], /
400) -> Select[_T0, _T1, _T2]: ...
403@overload
404def select(
405 __ent0: _TCCA[_T0],
406 __ent1: _TCCA[_T1],
407 __ent2: _TCCA[_T2],
408 __ent3: _TCCA[_T3],
409 /,
410) -> Select[_T0, _T1, _T2, _T3]: ...
413@overload
414def select(
415 __ent0: _TCCA[_T0],
416 __ent1: _TCCA[_T1],
417 __ent2: _TCCA[_T2],
418 __ent3: _TCCA[_T3],
419 __ent4: _TCCA[_T4],
420 /,
421) -> Select[_T0, _T1, _T2, _T3, _T4]: ...
424@overload
425def select(
426 __ent0: _TCCA[_T0],
427 __ent1: _TCCA[_T1],
428 __ent2: _TCCA[_T2],
429 __ent3: _TCCA[_T3],
430 __ent4: _TCCA[_T4],
431 __ent5: _TCCA[_T5],
432 /,
433) -> Select[_T0, _T1, _T2, _T3, _T4, _T5]: ...
436@overload
437def select(
438 __ent0: _TCCA[_T0],
439 __ent1: _TCCA[_T1],
440 __ent2: _TCCA[_T2],
441 __ent3: _TCCA[_T3],
442 __ent4: _TCCA[_T4],
443 __ent5: _TCCA[_T5],
444 __ent6: _TCCA[_T6],
445 /,
446) -> Select[_T0, _T1, _T2, _T3, _T4, _T5, _T6]: ...
449@overload
450def select(
451 __ent0: _TCCA[_T0],
452 __ent1: _TCCA[_T1],
453 __ent2: _TCCA[_T2],
454 __ent3: _TCCA[_T3],
455 __ent4: _TCCA[_T4],
456 __ent5: _TCCA[_T5],
457 __ent6: _TCCA[_T6],
458 __ent7: _TCCA[_T7],
459 /,
460) -> Select[_T0, _T1, _T2, _T3, _T4, _T5, _T6, _T7]: ...
463@overload
464def select(
465 __ent0: _TCCA[_T0],
466 __ent1: _TCCA[_T1],
467 __ent2: _TCCA[_T2],
468 __ent3: _TCCA[_T3],
469 __ent4: _TCCA[_T4],
470 __ent5: _TCCA[_T5],
471 __ent6: _TCCA[_T6],
472 __ent7: _TCCA[_T7],
473 __ent8: _TCCA[_T8],
474 /,
475) -> Select[_T0, _T1, _T2, _T3, _T4, _T5, _T6, _T7, _T8]: ...
478@overload
479def select(
480 __ent0: _TCCA[_T0],
481 __ent1: _TCCA[_T1],
482 __ent2: _TCCA[_T2],
483 __ent3: _TCCA[_T3],
484 __ent4: _TCCA[_T4],
485 __ent5: _TCCA[_T5],
486 __ent6: _TCCA[_T6],
487 __ent7: _TCCA[_T7],
488 __ent8: _TCCA[_T8],
489 __ent9: _TCCA[_T9],
490 /,
491 *entities: _ColumnsClauseArgument[Any],
492) -> Select[
493 _T0, _T1, _T2, _T3, _T4, _T5, _T6, _T7, _T8, _T9, Unpack[TupleAny]
494]: ...
497# END OVERLOADED FUNCTIONS select
500@overload
501def select(
502 *entities: _ColumnsClauseArgument[Any], **__kw: Any
503) -> Select[Unpack[TupleAny]]: ...
506def select(
507 *entities: _ColumnsClauseArgument[Any], **__kw: Any
508) -> Select[Unpack[TupleAny]]:
509 r"""Construct a new :class:`_expression.Select`.
512 .. versionadded:: 1.4 - The :func:`_sql.select` function now accepts
513 column arguments positionally. The top-level :func:`_sql.select`
514 function will automatically use the 1.x or 2.x style API based on
515 the incoming arguments; using :func:`_sql.select` from the
516 ``sqlalchemy.future`` module will enforce that only the 2.x style
517 constructor is used.
519 Similar functionality is also available via the
520 :meth:`_expression.FromClause.select` method on any
521 :class:`_expression.FromClause`.
523 .. seealso::
525 :ref:`tutorial_selecting_data` - in the :ref:`unified_tutorial`
527 :param \*entities:
528 Entities to SELECT from. For Core usage, this is typically a series
529 of :class:`_expression.ColumnElement` and / or
530 :class:`_expression.FromClause`
531 objects which will form the columns clause of the resulting
532 statement. For those objects that are instances of
533 :class:`_expression.FromClause` (typically :class:`_schema.Table`
534 or :class:`_expression.Alias`
535 objects), the :attr:`_expression.FromClause.c`
536 collection is extracted
537 to form a collection of :class:`_expression.ColumnElement` objects.
539 This parameter will also accept :class:`_expression.TextClause`
540 constructs as
541 given, as well as ORM-mapped classes.
543 """
544 # the keyword args are a necessary element in order for the typing
545 # to work out w/ the varargs vs. having named "keyword" arguments that
546 # aren't always present.
547 if __kw:
548 raise _no_kw()
549 return Select(*entities)
552def table(name: str, *columns: ColumnClause[Any], **kw: Any) -> TableClause:
553 """Produce a new :class:`_expression.TableClause`.
555 The object returned is an instance of
556 :class:`_expression.TableClause`, which
557 represents the "syntactical" portion of the schema-level
558 :class:`_schema.Table` object.
559 It may be used to construct lightweight table constructs.
561 :param name: Name of the table.
563 :param columns: A collection of :func:`_expression.column` constructs.
565 :param schema: The schema name for this table.
567 """
569 return TableClause(name, *columns, **kw)
572def tablesample(
573 selectable: _FromClauseArgument,
574 sampling: Union[float, Function[Any]],
575 name: Optional[str] = None,
576 seed: Optional[roles.ExpressionElementRole[Any]] = None,
577) -> TableSample:
578 """Return a :class:`_expression.TableSample` object.
580 :class:`_expression.TableSample` is an :class:`_expression.Alias`
581 subclass that represents
582 a table with the TABLESAMPLE clause applied to it.
583 :func:`_expression.tablesample`
584 is also available from the :class:`_expression.FromClause`
585 class via the
586 :meth:`_expression.FromClause.tablesample` method.
588 The TABLESAMPLE clause allows selecting a randomly selected approximate
589 percentage of rows from a table. It supports multiple sampling methods,
590 most commonly BERNOULLI and SYSTEM.
592 e.g.::
594 from sqlalchemy import func
596 selectable = people.tablesample(
597 func.bernoulli(1), name="alias", seed=func.random()
598 )
599 stmt = select(selectable.c.people_id)
601 Assuming ``people`` with a column ``people_id``, the above
602 statement would render as:
604 .. sourcecode:: sql
606 SELECT alias.people_id FROM
607 people AS alias TABLESAMPLE bernoulli(:bernoulli_1)
608 REPEATABLE (random())
610 :param sampling: a ``float`` percentage between 0 and 100 or
611 :class:`_functions.Function`.
613 :param name: optional alias name
615 :param seed: any real-valued SQL expression. When specified, the
616 REPEATABLE sub-clause is also rendered.
618 """
619 return TableSample._factory(selectable, sampling, name=name, seed=seed)
622@overload
623def union(
624 *selects: _TypedSelectable[Unpack[_Ts]],
625) -> CompoundSelect[Unpack[_Ts]]: ...
628@overload
629def union(
630 *selects: _SelectStatementForCompoundArgument[Unpack[_Ts]],
631) -> CompoundSelect[Unpack[_Ts]]: ...
634def union(
635 *selects: _SelectStatementForCompoundArgument[Unpack[_Ts]],
636) -> CompoundSelect[Unpack[_Ts]]:
637 r"""Return a ``UNION`` of multiple selectables.
639 The returned object is an instance of
640 :class:`_expression.CompoundSelect`.
642 A similar :func:`union()` method is available on all
643 :class:`_expression.FromClause` subclasses.
645 :param \*selects:
646 a list of :class:`_expression.Select` instances.
648 :param \**kwargs:
649 available keyword arguments are the same as those of
650 :func:`select`.
652 """
653 return CompoundSelect._create_union(*selects)
656@overload
657def union_all(
658 *selects: _TypedSelectable[Unpack[_Ts]],
659) -> CompoundSelect[Unpack[_Ts]]: ...
662@overload
663def union_all(
664 *selects: _SelectStatementForCompoundArgument[Unpack[_Ts]],
665) -> CompoundSelect[Unpack[_Ts]]: ...
668def union_all(
669 *selects: _SelectStatementForCompoundArgument[Unpack[_Ts]],
670) -> CompoundSelect[Unpack[_Ts]]:
671 r"""Return a ``UNION ALL`` of multiple selectables.
673 The returned object is an instance of
674 :class:`_expression.CompoundSelect`.
676 A similar :func:`union_all()` method is available on all
677 :class:`_expression.FromClause` subclasses.
679 :param \*selects:
680 a list of :class:`_expression.Select` instances.
682 """
683 return CompoundSelect._create_union_all(*selects)
686def values(
687 *columns: ColumnClause[Any],
688 name: Optional[str] = None,
689 literal_binds: bool = False,
690) -> Values:
691 r"""Construct a :class:`_expression.Values` construct representing the
692 SQL ``VALUES`` clause.
695 The column expressions and the actual data for :class:`_expression.Values`
696 are given in two separate steps. The constructor receives the column
697 expressions typically as :func:`_expression.column` constructs, and the
698 data is then passed via the :meth:`_expression.Values.data` method as a
699 list, which can be called multiple times to add more data, e.g.::
701 from sqlalchemy import column
702 from sqlalchemy import values
703 from sqlalchemy import Integer
704 from sqlalchemy import String
706 value_expr = (
707 values(
708 column("id", Integer),
709 column("name", String),
710 )
711 .data([(1, "name1"), (2, "name2")])
712 .data([(3, "name3")])
713 )
715 Would represent a SQL fragment like::
717 VALUES(1, "name1"), (2, "name2"), (3, "name3")
719 The :class:`_sql.values` construct has an optional
720 :paramref:`_sql.values.name` field; when using this field, the
721 PostgreSQL-specific "named VALUES" clause may be generated::
723 value_expr = values(
724 column("id", Integer), column("name", String), name="somename"
725 ).data([(1, "name1"), (2, "name2"), (3, "name3")])
727 When selecting from the above construct, the name and column names will
728 be listed out using a PostgreSQL-specific syntax::
730 >>> print(value_expr.select())
731 SELECT somename.id, somename.name
732 FROM (VALUES (:param_1, :param_2), (:param_3, :param_4),
733 (:param_5, :param_6)) AS somename (id, name)
735 For a more database-agnostic means of SELECTing named columns from a
736 VALUES expression, the :meth:`.Values.cte` method may be used, which
737 produces a named CTE with explicit column names against the VALUES
738 construct within; this syntax works on PostgreSQL, SQLite, and MariaDB::
740 value_expr = (
741 values(
742 column("id", Integer),
743 column("name", String),
744 )
745 .data([(1, "name1"), (2, "name2"), (3, "name3")])
746 .cte()
747 )
749 Rendering as::
751 >>> print(value_expr.select())
752 WITH anon_1(id, name) AS
753 (VALUES (:param_1, :param_2), (:param_3, :param_4), (:param_5, :param_6))
754 SELECT anon_1.id, anon_1.name
755 FROM anon_1
757 .. versionadded:: 2.0.42 Added the :meth:`.Values.cte` method to
758 :class:`.Values`
760 :param \*columns: column expressions, typically composed using
761 :func:`_expression.column` objects.
763 :param name: the name for this VALUES construct. If omitted, the
764 VALUES construct will be unnamed in a SQL expression. Different
765 backends may have different requirements here.
767 :param literal_binds: Defaults to False. Whether or not to render
768 the data values inline in the SQL output, rather than using bound
769 parameters.
771 """ # noqa: E501
773 return Values(*columns, literal_binds=literal_binds, name=name)