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 Tuple
14from typing import TYPE_CHECKING
15from typing import Union
17from . import coercions
18from . import roles
19from ._typing import _ColumnsClauseArgument
20from ._typing import _no_kw
21from .elements import ColumnClause
22from .selectable import Alias
23from .selectable import CompoundSelect
24from .selectable import Exists
25from .selectable import FromClause
26from .selectable import Join
27from .selectable import Lateral
28from .selectable import LateralFromClause
29from .selectable import NamedFromClause
30from .selectable import Select
31from .selectable import TableClause
32from .selectable import TableSample
33from .selectable import Values
35if TYPE_CHECKING:
36 from ._typing import _FromClauseArgument
37 from ._typing import _OnClauseArgument
38 from ._typing import _OnlyColumnArgument
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 _TP
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[_TP]", "CompoundSelect[_TP]"]
114@overload
115def except_(
116 *selects: _TypedSelectable[_TP],
117) -> CompoundSelect[_TP]: ...
120@overload
121def except_(
122 *selects: _SelectStatementForCompoundArgument[_TP],
123) -> CompoundSelect[_TP]: ...
126def except_(
127 *selects: _SelectStatementForCompoundArgument[_TP],
128) -> CompoundSelect[_TP]:
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[_TP],
144) -> CompoundSelect[_TP]: ...
147@overload
148def except_all(
149 *selects: _SelectStatementForCompoundArgument[_TP],
150) -> CompoundSelect[_TP]: ...
153def except_all(
154 *selects: _SelectStatementForCompoundArgument[_TP],
155) -> CompoundSelect[_TP]:
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) -> Exists:
173 """Construct a new :class:`_expression.Exists` construct.
175 The :func:`_sql.exists` can be invoked by itself to produce an
176 :class:`_sql.Exists` construct, which will accept simple WHERE
177 criteria::
179 exists_criteria = exists().where(table1.c.col1 == table2.c.col2)
181 However, for greater flexibility in constructing the SELECT, an
182 existing :class:`_sql.Select` construct may be converted to an
183 :class:`_sql.Exists`, most conveniently by making use of the
184 :meth:`_sql.SelectBase.exists` method::
186 exists_criteria = (
187 select(table2.c.col2).where(table1.c.col1 == table2.c.col2).exists()
188 )
190 The EXISTS criteria is then used inside of an enclosing SELECT::
192 stmt = select(table1.c.col1).where(exists_criteria)
194 The above statement will then be of the form:
196 .. sourcecode:: sql
198 SELECT col1 FROM table1 WHERE EXISTS
199 (SELECT table2.col2 FROM table2 WHERE table2.col2 = table1.col1)
201 .. seealso::
203 :ref:`tutorial_exists` - in the :term:`2.0 style` tutorial.
205 :meth:`_sql.SelectBase.exists` - method to transform a ``SELECT`` to an
206 ``EXISTS`` clause.
208 """ # noqa: E501
210 return Exists(__argument)
213@overload
214def intersect(
215 *selects: _TypedSelectable[_TP],
216) -> CompoundSelect[_TP]: ...
219@overload
220def intersect(
221 *selects: _SelectStatementForCompoundArgument[_TP],
222) -> CompoundSelect[_TP]: ...
225def intersect(
226 *selects: _SelectStatementForCompoundArgument[_TP],
227) -> CompoundSelect[_TP]:
228 r"""Return an ``INTERSECT`` of multiple selectables.
230 The returned object is an instance of
231 :class:`_expression.CompoundSelect`.
233 :param \*selects:
234 a list of :class:`_expression.Select` instances.
236 """
237 return CompoundSelect._create_intersect(*selects)
240@overload
241def intersect_all(
242 *selects: _TypedSelectable[_TP],
243) -> CompoundSelect[_TP]: ...
246@overload
247def intersect_all(
248 *selects: _SelectStatementForCompoundArgument[_TP],
249) -> CompoundSelect[_TP]: ...
252def intersect_all(
253 *selects: _SelectStatementForCompoundArgument[_TP],
254) -> CompoundSelect[_TP]:
255 r"""Return an ``INTERSECT ALL`` of multiple selectables.
257 The returned object is an instance of
258 :class:`_expression.CompoundSelect`.
260 :param \*selects:
261 a list of :class:`_expression.Select` instances.
264 """
265 return CompoundSelect._create_intersect_all(*selects)
268def join(
269 left: _FromClauseArgument,
270 right: _FromClauseArgument,
271 onclause: Optional[_OnClauseArgument] = None,
272 isouter: bool = False,
273 full: bool = False,
274) -> Join:
275 """Produce a :class:`_expression.Join` object, given two
276 :class:`_expression.FromClause`
277 expressions.
279 E.g.::
281 j = join(
282 user_table, address_table, user_table.c.id == address_table.c.user_id
283 )
284 stmt = select(user_table).select_from(j)
286 would emit SQL along the lines of:
288 .. sourcecode:: sql
290 SELECT user.id, user.name FROM user
291 JOIN address ON user.id = address.user_id
293 Similar functionality is available given any
294 :class:`_expression.FromClause` object (e.g. such as a
295 :class:`_schema.Table`) using
296 the :meth:`_expression.FromClause.join` method.
298 :param left: The left side of the join.
300 :param right: the right side of the join; this is any
301 :class:`_expression.FromClause` object such as a
302 :class:`_schema.Table` object, and
303 may also be a selectable-compatible object such as an ORM-mapped
304 class.
306 :param onclause: a SQL expression representing the ON clause of the
307 join. If left at ``None``, :meth:`_expression.FromClause.join`
308 will attempt to
309 join the two tables based on a foreign key relationship.
311 :param isouter: if True, render a LEFT OUTER JOIN, instead of JOIN.
313 :param full: if True, render a FULL OUTER JOIN, instead of JOIN.
315 .. seealso::
317 :meth:`_expression.FromClause.join` - method form,
318 based on a given left side.
320 :class:`_expression.Join` - the type of object produced.
322 """ # noqa: E501
324 return Join(left, right, onclause, isouter, full)
327def lateral(
328 selectable: Union[SelectBase, _FromClauseArgument],
329 name: Optional[str] = None,
330) -> LateralFromClause:
331 """Return a :class:`_expression.Lateral` object.
333 :class:`_expression.Lateral` is an :class:`_expression.Alias`
334 subclass that represents
335 a subquery with the LATERAL keyword applied to it.
337 The special behavior of a LATERAL subquery is that it appears in the
338 FROM clause of an enclosing SELECT, but may correlate to other
339 FROM clauses of that SELECT. It is a special case of subquery
340 only supported by a small number of backends, currently more recent
341 PostgreSQL versions.
343 .. seealso::
345 :ref:`tutorial_lateral_correlation` - overview of usage.
347 """
348 return Lateral._factory(selectable, name=name)
351def outerjoin(
352 left: _FromClauseArgument,
353 right: _FromClauseArgument,
354 onclause: Optional[_OnClauseArgument] = None,
355 full: bool = False,
356) -> Join:
357 """Return an ``OUTER JOIN`` clause element.
359 The returned object is an instance of :class:`_expression.Join`.
361 Similar functionality is also available via the
362 :meth:`_expression.FromClause.outerjoin` method on any
363 :class:`_expression.FromClause`.
365 :param left: The left side of the join.
367 :param right: The right side of the join.
369 :param onclause: Optional criterion for the ``ON`` clause, is
370 derived from foreign key relationships established between
371 left and right otherwise.
373 To chain joins together, use the :meth:`_expression.FromClause.join`
374 or
375 :meth:`_expression.FromClause.outerjoin` methods on the resulting
376 :class:`_expression.Join` object.
378 """
379 return Join(left, right, onclause, isouter=True, full=full)
382# START OVERLOADED FUNCTIONS select Select 1-10
384# code within this block is **programmatically,
385# statically generated** by tools/generate_tuple_map_overloads.py
388@overload
389def select(__ent0: _TCCA[_T0]) -> Select[Tuple[_T0]]: ...
392@overload
393def select(
394 __ent0: _TCCA[_T0], __ent1: _TCCA[_T1]
395) -> Select[Tuple[_T0, _T1]]: ...
398@overload
399def select(
400 __ent0: _TCCA[_T0], __ent1: _TCCA[_T1], __ent2: _TCCA[_T2]
401) -> Select[Tuple[_T0, _T1, _T2]]: ...
404@overload
405def select(
406 __ent0: _TCCA[_T0],
407 __ent1: _TCCA[_T1],
408 __ent2: _TCCA[_T2],
409 __ent3: _TCCA[_T3],
410) -> Select[Tuple[_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) -> Select[Tuple[_T0, _T1, _T2, _T3, _T4]]: ...
423@overload
424def select(
425 __ent0: _TCCA[_T0],
426 __ent1: _TCCA[_T1],
427 __ent2: _TCCA[_T2],
428 __ent3: _TCCA[_T3],
429 __ent4: _TCCA[_T4],
430 __ent5: _TCCA[_T5],
431) -> Select[Tuple[_T0, _T1, _T2, _T3, _T4, _T5]]: ...
434@overload
435def select(
436 __ent0: _TCCA[_T0],
437 __ent1: _TCCA[_T1],
438 __ent2: _TCCA[_T2],
439 __ent3: _TCCA[_T3],
440 __ent4: _TCCA[_T4],
441 __ent5: _TCCA[_T5],
442 __ent6: _TCCA[_T6],
443) -> Select[Tuple[_T0, _T1, _T2, _T3, _T4, _T5, _T6]]: ...
446@overload
447def select(
448 __ent0: _TCCA[_T0],
449 __ent1: _TCCA[_T1],
450 __ent2: _TCCA[_T2],
451 __ent3: _TCCA[_T3],
452 __ent4: _TCCA[_T4],
453 __ent5: _TCCA[_T5],
454 __ent6: _TCCA[_T6],
455 __ent7: _TCCA[_T7],
456) -> Select[Tuple[_T0, _T1, _T2, _T3, _T4, _T5, _T6, _T7]]: ...
459@overload
460def select(
461 __ent0: _TCCA[_T0],
462 __ent1: _TCCA[_T1],
463 __ent2: _TCCA[_T2],
464 __ent3: _TCCA[_T3],
465 __ent4: _TCCA[_T4],
466 __ent5: _TCCA[_T5],
467 __ent6: _TCCA[_T6],
468 __ent7: _TCCA[_T7],
469 __ent8: _TCCA[_T8],
470) -> Select[Tuple[_T0, _T1, _T2, _T3, _T4, _T5, _T6, _T7, _T8]]: ...
473@overload
474def select(
475 __ent0: _TCCA[_T0],
476 __ent1: _TCCA[_T1],
477 __ent2: _TCCA[_T2],
478 __ent3: _TCCA[_T3],
479 __ent4: _TCCA[_T4],
480 __ent5: _TCCA[_T5],
481 __ent6: _TCCA[_T6],
482 __ent7: _TCCA[_T7],
483 __ent8: _TCCA[_T8],
484 __ent9: _TCCA[_T9],
485) -> Select[Tuple[_T0, _T1, _T2, _T3, _T4, _T5, _T6, _T7, _T8, _T9]]: ...
488# END OVERLOADED FUNCTIONS select
491@overload
492def select(
493 *entities: _ColumnsClauseArgument[Any], **__kw: Any
494) -> Select[Any]: ...
497def select(*entities: _ColumnsClauseArgument[Any], **__kw: Any) -> Select[Any]:
498 r"""Construct a new :class:`_expression.Select`.
501 .. versionadded:: 1.4 - The :func:`_sql.select` function now accepts
502 column arguments positionally. The top-level :func:`_sql.select`
503 function will automatically use the 1.x or 2.x style API based on
504 the incoming arguments; using :func:`_sql.select` from the
505 ``sqlalchemy.future`` module will enforce that only the 2.x style
506 constructor is used.
508 Similar functionality is also available via the
509 :meth:`_expression.FromClause.select` method on any
510 :class:`_expression.FromClause`.
512 .. seealso::
514 :ref:`tutorial_selecting_data` - in the :ref:`unified_tutorial`
516 :param \*entities:
517 Entities to SELECT from. For Core usage, this is typically a series
518 of :class:`_expression.ColumnElement` and / or
519 :class:`_expression.FromClause`
520 objects which will form the columns clause of the resulting
521 statement. For those objects that are instances of
522 :class:`_expression.FromClause` (typically :class:`_schema.Table`
523 or :class:`_expression.Alias`
524 objects), the :attr:`_expression.FromClause.c`
525 collection is extracted
526 to form a collection of :class:`_expression.ColumnElement` objects.
528 This parameter will also accept :class:`_expression.TextClause`
529 constructs as
530 given, as well as ORM-mapped classes.
532 """
533 # the keyword args are a necessary element in order for the typing
534 # to work out w/ the varargs vs. having named "keyword" arguments that
535 # aren't always present.
536 if __kw:
537 raise _no_kw()
538 return Select(*entities)
541def table(name: str, *columns: ColumnClause[Any], **kw: Any) -> TableClause:
542 """Produce a new :class:`_expression.TableClause`.
544 The object returned is an instance of
545 :class:`_expression.TableClause`, which
546 represents the "syntactical" portion of the schema-level
547 :class:`_schema.Table` object.
548 It may be used to construct lightweight table constructs.
550 :param name: Name of the table.
552 :param columns: A collection of :func:`_expression.column` constructs.
554 :param schema: The schema name for this table.
556 .. versionadded:: 1.3.18 :func:`_expression.table` can now
557 accept a ``schema`` argument.
558 """
560 return TableClause(name, *columns, **kw)
563def tablesample(
564 selectable: _FromClauseArgument,
565 sampling: Union[float, Function[Any]],
566 name: Optional[str] = None,
567 seed: Optional[roles.ExpressionElementRole[Any]] = None,
568) -> TableSample:
569 """Return a :class:`_expression.TableSample` object.
571 :class:`_expression.TableSample` is an :class:`_expression.Alias`
572 subclass that represents
573 a table with the TABLESAMPLE clause applied to it.
574 :func:`_expression.tablesample`
575 is also available from the :class:`_expression.FromClause`
576 class via the
577 :meth:`_expression.FromClause.tablesample` method.
579 The TABLESAMPLE clause allows selecting a randomly selected approximate
580 percentage of rows from a table. It supports multiple sampling methods,
581 most commonly BERNOULLI and SYSTEM.
583 e.g.::
585 from sqlalchemy import func
587 selectable = people.tablesample(
588 func.bernoulli(1), name="alias", seed=func.random()
589 )
590 stmt = select(selectable.c.people_id)
592 Assuming ``people`` with a column ``people_id``, the above
593 statement would render as:
595 .. sourcecode:: sql
597 SELECT alias.people_id FROM
598 people AS alias TABLESAMPLE bernoulli(:bernoulli_1)
599 REPEATABLE (random())
601 :param sampling: a ``float`` percentage between 0 and 100 or
602 :class:`_functions.Function`.
604 :param name: optional alias name
606 :param seed: any real-valued SQL expression. When specified, the
607 REPEATABLE sub-clause is also rendered.
609 """
610 return TableSample._factory(selectable, sampling, name=name, seed=seed)
613@overload
614def union(
615 *selects: _TypedSelectable[_TP],
616) -> CompoundSelect[_TP]: ...
619@overload
620def union(
621 *selects: _SelectStatementForCompoundArgument[_TP],
622) -> CompoundSelect[_TP]: ...
625def union(
626 *selects: _SelectStatementForCompoundArgument[_TP],
627) -> CompoundSelect[_TP]:
628 r"""Return a ``UNION`` of multiple selectables.
630 The returned object is an instance of
631 :class:`_expression.CompoundSelect`.
633 A similar :func:`union()` method is available on all
634 :class:`_expression.FromClause` subclasses.
636 :param \*selects:
637 a list of :class:`_expression.Select` instances.
639 :param \**kwargs:
640 available keyword arguments are the same as those of
641 :func:`select`.
643 """
644 return CompoundSelect._create_union(*selects)
647@overload
648def union_all(
649 *selects: _TypedSelectable[_TP],
650) -> CompoundSelect[_TP]: ...
653@overload
654def union_all(
655 *selects: _SelectStatementForCompoundArgument[_TP],
656) -> CompoundSelect[_TP]: ...
659def union_all(
660 *selects: _SelectStatementForCompoundArgument[_TP],
661) -> CompoundSelect[_TP]:
662 r"""Return a ``UNION ALL`` of multiple selectables.
664 The returned object is an instance of
665 :class:`_expression.CompoundSelect`.
667 A similar :func:`union_all()` method is available on all
668 :class:`_expression.FromClause` subclasses.
670 :param \*selects:
671 a list of :class:`_expression.Select` instances.
673 """
674 return CompoundSelect._create_union_all(*selects)
677def values(
678 *columns: _OnlyColumnArgument[Any],
679 name: Optional[str] = None,
680 literal_binds: bool = False,
681) -> Values:
682 r"""Construct a :class:`_expression.Values` construct representing the
683 SQL ``VALUES`` clause.
685 The column expressions and the actual data for :class:`_expression.Values`
686 are given in two separate steps. The constructor receives the column
687 expressions typically as :func:`_expression.column` constructs, and the
688 data is then passed via the :meth:`_expression.Values.data` method as a
689 list, which can be called multiple times to add more data, e.g.::
691 from sqlalchemy import column
692 from sqlalchemy import values
693 from sqlalchemy import Integer
694 from sqlalchemy import String
696 value_expr = (
697 values(
698 column("id", Integer),
699 column("name", String),
700 )
701 .data([(1, "name1"), (2, "name2")])
702 .data([(3, "name3")])
703 )
705 Would represent a SQL fragment like::
707 VALUES(1, "name1"), (2, "name2"), (3, "name3")
709 The :class:`_sql.values` construct has an optional
710 :paramref:`_sql.values.name` field; when using this field, the
711 PostgreSQL-specific "named VALUES" clause may be generated::
713 value_expr = values(
714 column("id", Integer), column("name", String), name="somename"
715 ).data([(1, "name1"), (2, "name2"), (3, "name3")])
717 When selecting from the above construct, the name and column names will
718 be listed out using a PostgreSQL-specific syntax::
720 >>> print(value_expr.select())
721 SELECT somename.id, somename.name
722 FROM (VALUES (:param_1, :param_2), (:param_3, :param_4),
723 (:param_5, :param_6)) AS somename (id, name)
725 For a more database-agnostic means of SELECTing named columns from a
726 VALUES expression, the :meth:`.Values.cte` method may be used, which
727 produces a named CTE with explicit column names against the VALUES
728 construct within; this syntax works on PostgreSQL, SQLite, and MariaDB::
730 value_expr = (
731 values(
732 column("id", Integer),
733 column("name", String),
734 )
735 .data([(1, "name1"), (2, "name2"), (3, "name3")])
736 .cte()
737 )
739 Rendering as::
741 >>> print(value_expr.select())
742 WITH anon_1(id, name) AS
743 (VALUES (:param_1, :param_2), (:param_3, :param_4), (:param_5, :param_6))
744 SELECT anon_1.id, anon_1.name
745 FROM anon_1
747 .. versionadded:: 2.0.42 Added the :meth:`.Values.cte` method to
748 :class:`.Values`
750 :param \*columns: column expressions, typically composed using
751 :func:`_expression.column` objects.
753 :param name: the name for this VALUES construct. If omitted, the
754 VALUES construct will be unnamed in a SQL expression. Different
755 backends may have different requirements here.
757 :param literal_binds: Defaults to False. Whether or not to render
758 the data values inline in the SQL output, rather than using bound
759 parameters.
761 """ # noqa: E501
763 return Values(*columns, literal_binds=literal_binds, name=name)