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 _OnlyColumnArgument
40 from ._typing import _SelectStatementForCompoundArgument
41 from ._typing import _T0
42 from ._typing import _T1
43 from ._typing import _T2
44 from ._typing import _T3
45 from ._typing import _T4
46 from ._typing import _T5
47 from ._typing import _T6
48 from ._typing import _T7
49 from ._typing import _T8
50 from ._typing import _T9
51 from ._typing import _Ts
52 from ._typing import _TypedColumnClauseArgument as _TCCA
53 from .functions import Function
54 from .selectable import CTE
55 from .selectable import HasCTE
56 from .selectable import ScalarSelect
57 from .selectable import SelectBase
60def alias(
61 selectable: FromClause, name: Optional[str] = None, flat: bool = False
62) -> NamedFromClause:
63 """Return a named alias of the given :class:`.FromClause`.
65 For :class:`.Table` and :class:`.Join` objects, the return type is the
66 :class:`_expression.Alias` object. Other kinds of :class:`.NamedFromClause`
67 objects may be returned for other kinds of :class:`.FromClause` objects.
69 The named alias represents any :class:`_expression.FromClause` with an
70 alternate name assigned within SQL, typically using the ``AS`` clause when
71 generated, e.g. ``SELECT * FROM table AS aliasname``.
73 Equivalent functionality is available via the
74 :meth:`_expression.FromClause.alias`
75 method available on all :class:`_expression.FromClause` objects.
77 :param selectable: any :class:`_expression.FromClause` subclass,
78 such as a table, select statement, etc.
80 :param name: string name to be assigned as the alias.
81 If ``None``, a name will be deterministically generated at compile
82 time. Deterministic means the name is guaranteed to be unique against
83 other constructs used in the same statement, and will also be the same
84 name for each successive compilation of the same statement object.
86 :param flat: Will be passed through to if the given selectable
87 is an instance of :class:`_expression.Join` - see
88 :meth:`_expression.Join.alias` for details.
90 """
91 return Alias._factory(selectable, name=name, flat=flat)
94def cte(
95 selectable: HasCTE, name: Optional[str] = None, recursive: bool = False
96) -> CTE:
97 r"""Return a new :class:`_expression.CTE`,
98 or Common Table Expression instance.
100 Please see :meth:`_expression.HasCTE.cte` for detail on CTE usage.
102 """
103 return coercions.expect(roles.HasCTERole, selectable).cte(
104 name=name, recursive=recursive
105 )
108# TODO: mypy requires the _TypedSelectable overloads in all compound select
109# constructors since _SelectStatementForCompoundArgument includes
110# untyped args that make it return CompoundSelect[Unpack[tuple[Never, ...]]]
111# pyright does not have this issue
112_TypedSelectable = Union["Select[Unpack[_Ts]]", "CompoundSelect[Unpack[_Ts]]"]
115@overload
116def except_(
117 *selects: _TypedSelectable[Unpack[_Ts]],
118) -> CompoundSelect[Unpack[_Ts]]: ...
121@overload
122def except_(
123 *selects: _SelectStatementForCompoundArgument[Unpack[_Ts]],
124) -> CompoundSelect[Unpack[_Ts]]: ...
127def except_(
128 *selects: _SelectStatementForCompoundArgument[Unpack[_Ts]],
129) -> CompoundSelect[Unpack[_Ts]]:
130 r"""Return an ``EXCEPT`` of multiple selectables.
132 The returned object is an instance of
133 :class:`_expression.CompoundSelect`.
135 :param \*selects:
136 a list of :class:`_expression.Select` instances.
138 """
139 return CompoundSelect._create_except(*selects)
142@overload
143def except_all(
144 *selects: _TypedSelectable[Unpack[_Ts]],
145) -> CompoundSelect[Unpack[_Ts]]: ...
148@overload
149def except_all(
150 *selects: _SelectStatementForCompoundArgument[Unpack[_Ts]],
151) -> CompoundSelect[Unpack[_Ts]]: ...
154def except_all(
155 *selects: _SelectStatementForCompoundArgument[Unpack[_Ts]],
156) -> CompoundSelect[Unpack[_Ts]]:
157 r"""Return an ``EXCEPT ALL`` of multiple selectables.
159 The returned object is an instance of
160 :class:`_expression.CompoundSelect`.
162 :param \*selects:
163 a list of :class:`_expression.Select` instances.
165 """
166 return CompoundSelect._create_except_all(*selects)
169def exists(
170 __argument: Optional[
171 Union[_ColumnsClauseArgument[Any], SelectBase, ScalarSelect[Any]]
172 ] = None,
173 /,
174) -> Exists:
175 """Construct a new :class:`_expression.Exists` construct.
177 The :func:`_sql.exists` can be invoked by itself to produce an
178 :class:`_sql.Exists` construct, which will accept simple WHERE
179 criteria::
181 exists_criteria = exists().where(table1.c.col1 == table2.c.col2)
183 However, for greater flexibility in constructing the SELECT, an
184 existing :class:`_sql.Select` construct may be converted to an
185 :class:`_sql.Exists`, most conveniently by making use of the
186 :meth:`_sql.SelectBase.exists` method::
188 exists_criteria = (
189 select(table2.c.col2).where(table1.c.col1 == table2.c.col2).exists()
190 )
192 The EXISTS criteria is then used inside of an enclosing SELECT::
194 stmt = select(table1.c.col1).where(exists_criteria)
196 The above statement will then be of the form:
198 .. sourcecode:: sql
200 SELECT col1 FROM table1 WHERE EXISTS
201 (SELECT table2.col2 FROM table2 WHERE table2.col2 = table1.col1)
203 .. seealso::
205 :ref:`tutorial_exists` - in the :term:`2.0 style` tutorial.
207 :meth:`_sql.SelectBase.exists` - method to transform a ``SELECT`` to an
208 ``EXISTS`` clause.
210 """ # noqa: E501
212 return Exists(__argument)
215@overload
216def intersect(
217 *selects: _TypedSelectable[Unpack[_Ts]],
218) -> CompoundSelect[Unpack[_Ts]]: ...
221@overload
222def intersect(
223 *selects: _SelectStatementForCompoundArgument[Unpack[_Ts]],
224) -> CompoundSelect[Unpack[_Ts]]: ...
227def intersect(
228 *selects: _SelectStatementForCompoundArgument[Unpack[_Ts]],
229) -> CompoundSelect[Unpack[_Ts]]:
230 r"""Return an ``INTERSECT`` of multiple selectables.
232 The returned object is an instance of
233 :class:`_expression.CompoundSelect`.
235 :param \*selects:
236 a list of :class:`_expression.Select` instances.
238 """
239 return CompoundSelect._create_intersect(*selects)
242@overload
243def intersect_all(
244 *selects: _TypedSelectable[Unpack[_Ts]],
245) -> CompoundSelect[Unpack[_Ts]]: ...
248@overload
249def intersect_all(
250 *selects: _SelectStatementForCompoundArgument[Unpack[_Ts]],
251) -> CompoundSelect[Unpack[_Ts]]: ...
254def intersect_all(
255 *selects: _SelectStatementForCompoundArgument[Unpack[_Ts]],
256) -> CompoundSelect[Unpack[_Ts]]:
257 r"""Return an ``INTERSECT ALL`` of multiple selectables.
259 The returned object is an instance of
260 :class:`_expression.CompoundSelect`.
262 :param \*selects:
263 a list of :class:`_expression.Select` instances.
266 """
267 return CompoundSelect._create_intersect_all(*selects)
270def join(
271 left: _FromClauseArgument,
272 right: _FromClauseArgument,
273 onclause: Optional[_OnClauseArgument] = None,
274 isouter: bool = False,
275 full: bool = False,
276) -> Join:
277 """Produce a :class:`_expression.Join` object, given two
278 :class:`_expression.FromClause`
279 expressions.
281 E.g.::
283 j = join(
284 user_table, address_table, user_table.c.id == address_table.c.user_id
285 )
286 stmt = select(user_table).select_from(j)
288 would emit SQL along the lines of:
290 .. sourcecode:: sql
292 SELECT user.id, user.name FROM user
293 JOIN address ON user.id = address.user_id
295 Similar functionality is available given any
296 :class:`_expression.FromClause` object (e.g. such as a
297 :class:`_schema.Table`) using
298 the :meth:`_expression.FromClause.join` method.
300 :param left: The left side of the join.
302 :param right: the right side of the join; this is any
303 :class:`_expression.FromClause` object such as a
304 :class:`_schema.Table` object, and
305 may also be a selectable-compatible object such as an ORM-mapped
306 class.
308 :param onclause: a SQL expression representing the ON clause of the
309 join. If left at ``None``, :meth:`_expression.FromClause.join`
310 will attempt to
311 join the two tables based on a foreign key relationship.
313 :param isouter: if True, render a LEFT OUTER JOIN, instead of JOIN.
315 :param full: if True, render a FULL OUTER JOIN, instead of JOIN.
317 .. seealso::
319 :meth:`_expression.FromClause.join` - method form,
320 based on a given left side.
322 :class:`_expression.Join` - the type of object produced.
324 """ # noqa: E501
326 return Join(left, right, onclause, isouter, full)
329def lateral(
330 selectable: Union[SelectBase, _FromClauseArgument],
331 name: Optional[str] = None,
332) -> LateralFromClause:
333 """Return a :class:`_expression.Lateral` object.
335 :class:`_expression.Lateral` is an :class:`_expression.Alias`
336 subclass that represents
337 a subquery with the LATERAL keyword applied to it.
339 The special behavior of a LATERAL subquery is that it appears in the
340 FROM clause of an enclosing SELECT, but may correlate to other
341 FROM clauses of that SELECT. It is a special case of subquery
342 only supported by a small number of backends, currently more recent
343 PostgreSQL versions.
345 .. seealso::
347 :ref:`tutorial_lateral_correlation` - overview of usage.
349 """
350 return Lateral._factory(selectable, name=name)
353def outerjoin(
354 left: _FromClauseArgument,
355 right: _FromClauseArgument,
356 onclause: Optional[_OnClauseArgument] = None,
357 full: bool = False,
358) -> Join:
359 """Return an ``OUTER JOIN`` clause element.
361 The returned object is an instance of :class:`_expression.Join`.
363 Similar functionality is also available via the
364 :meth:`_expression.FromClause.outerjoin` method on any
365 :class:`_expression.FromClause`.
367 :param left: The left side of the join.
369 :param right: The right side of the join.
371 :param onclause: Optional criterion for the ``ON`` clause, is
372 derived from foreign key relationships established between
373 left and right otherwise.
375 To chain joins together, use the :meth:`_expression.FromClause.join`
376 or
377 :meth:`_expression.FromClause.outerjoin` methods on the resulting
378 :class:`_expression.Join` object.
380 """
381 return Join(left, right, onclause, isouter=True, full=full)
384# START OVERLOADED FUNCTIONS select Select 1-10
386# code within this block is **programmatically,
387# statically generated** by tools/generate_tuple_map_overloads.py
390@overload
391def select(__ent0: _TCCA[_T0], /) -> Select[_T0]: ...
394@overload
395def select(__ent0: _TCCA[_T0], __ent1: _TCCA[_T1], /) -> Select[_T0, _T1]: ...
398@overload
399def select(
400 __ent0: _TCCA[_T0], __ent1: _TCCA[_T1], __ent2: _TCCA[_T2], /
401) -> Select[_T0, _T1, _T2]: ...
404@overload
405def select(
406 __ent0: _TCCA[_T0],
407 __ent1: _TCCA[_T1],
408 __ent2: _TCCA[_T2],
409 __ent3: _TCCA[_T3],
410 /,
411) -> Select[_T0, _T1, _T2, _T3]: ...
414@overload
415def select(
416 __ent0: _TCCA[_T0],
417 __ent1: _TCCA[_T1],
418 __ent2: _TCCA[_T2],
419 __ent3: _TCCA[_T3],
420 __ent4: _TCCA[_T4],
421 /,
422) -> Select[_T0, _T1, _T2, _T3, _T4]: ...
425@overload
426def select(
427 __ent0: _TCCA[_T0],
428 __ent1: _TCCA[_T1],
429 __ent2: _TCCA[_T2],
430 __ent3: _TCCA[_T3],
431 __ent4: _TCCA[_T4],
432 __ent5: _TCCA[_T5],
433 /,
434) -> Select[_T0, _T1, _T2, _T3, _T4, _T5]: ...
437@overload
438def select(
439 __ent0: _TCCA[_T0],
440 __ent1: _TCCA[_T1],
441 __ent2: _TCCA[_T2],
442 __ent3: _TCCA[_T3],
443 __ent4: _TCCA[_T4],
444 __ent5: _TCCA[_T5],
445 __ent6: _TCCA[_T6],
446 /,
447) -> Select[_T0, _T1, _T2, _T3, _T4, _T5, _T6]: ...
450@overload
451def select(
452 __ent0: _TCCA[_T0],
453 __ent1: _TCCA[_T1],
454 __ent2: _TCCA[_T2],
455 __ent3: _TCCA[_T3],
456 __ent4: _TCCA[_T4],
457 __ent5: _TCCA[_T5],
458 __ent6: _TCCA[_T6],
459 __ent7: _TCCA[_T7],
460 /,
461) -> Select[_T0, _T1, _T2, _T3, _T4, _T5, _T6, _T7]: ...
464@overload
465def select(
466 __ent0: _TCCA[_T0],
467 __ent1: _TCCA[_T1],
468 __ent2: _TCCA[_T2],
469 __ent3: _TCCA[_T3],
470 __ent4: _TCCA[_T4],
471 __ent5: _TCCA[_T5],
472 __ent6: _TCCA[_T6],
473 __ent7: _TCCA[_T7],
474 __ent8: _TCCA[_T8],
475 /,
476) -> Select[_T0, _T1, _T2, _T3, _T4, _T5, _T6, _T7, _T8]: ...
479@overload
480def select(
481 __ent0: _TCCA[_T0],
482 __ent1: _TCCA[_T1],
483 __ent2: _TCCA[_T2],
484 __ent3: _TCCA[_T3],
485 __ent4: _TCCA[_T4],
486 __ent5: _TCCA[_T5],
487 __ent6: _TCCA[_T6],
488 __ent7: _TCCA[_T7],
489 __ent8: _TCCA[_T8],
490 __ent9: _TCCA[_T9],
491 /,
492 *entities: _ColumnsClauseArgument[Any],
493) -> Select[
494 _T0, _T1, _T2, _T3, _T4, _T5, _T6, _T7, _T8, _T9, Unpack[TupleAny]
495]: ...
498# END OVERLOADED FUNCTIONS select
501@overload
502def select(
503 *entities: _ColumnsClauseArgument[Any], **__kw: Any
504) -> Select[Unpack[TupleAny]]: ...
507def select(
508 *entities: _ColumnsClauseArgument[Any], **__kw: Any
509) -> Select[Unpack[TupleAny]]:
510 r"""Construct a new :class:`_expression.Select`.
513 .. versionadded:: 1.4 - The :func:`_sql.select` function now accepts
514 column arguments positionally. The top-level :func:`_sql.select`
515 function will automatically use the 1.x or 2.x style API based on
516 the incoming arguments; using :func:`_sql.select` from the
517 ``sqlalchemy.future`` module will enforce that only the 2.x style
518 constructor is used.
520 Similar functionality is also available via the
521 :meth:`_expression.FromClause.select` method on any
522 :class:`_expression.FromClause`.
524 .. seealso::
526 :ref:`tutorial_selecting_data` - in the :ref:`unified_tutorial`
528 :param \*entities:
529 Entities to SELECT from. For Core usage, this is typically a series
530 of :class:`_expression.ColumnElement` and / or
531 :class:`_expression.FromClause`
532 objects which will form the columns clause of the resulting
533 statement. For those objects that are instances of
534 :class:`_expression.FromClause` (typically :class:`_schema.Table`
535 or :class:`_expression.Alias`
536 objects), the :attr:`_expression.FromClause.c`
537 collection is extracted
538 to form a collection of :class:`_expression.ColumnElement` objects.
540 This parameter will also accept :class:`_expression.TextClause`
541 constructs as
542 given, as well as ORM-mapped classes.
544 """
545 # the keyword args are a necessary element in order for the typing
546 # to work out w/ the varargs vs. having named "keyword" arguments that
547 # aren't always present.
548 if __kw:
549 raise _no_kw()
550 return Select(*entities)
553def table(name: str, *columns: ColumnClause[Any], **kw: Any) -> TableClause:
554 """Produce a new :class:`_expression.TableClause`.
556 The object returned is an instance of
557 :class:`_expression.TableClause`, which
558 represents the "syntactical" portion of the schema-level
559 :class:`_schema.Table` object.
560 It may be used to construct lightweight table constructs.
562 :param name: Name of the table.
564 :param columns: A collection of :func:`_expression.column` constructs.
566 :param schema: The schema name for this table.
568 """
570 return TableClause(name, *columns, **kw)
573def tablesample(
574 selectable: _FromClauseArgument,
575 sampling: Union[float, Function[Any]],
576 name: Optional[str] = None,
577 seed: Optional[roles.ExpressionElementRole[Any]] = None,
578) -> TableSample:
579 """Return a :class:`_expression.TableSample` object.
581 :class:`_expression.TableSample` is an :class:`_expression.Alias`
582 subclass that represents
583 a table with the TABLESAMPLE clause applied to it.
584 :func:`_expression.tablesample`
585 is also available from the :class:`_expression.FromClause`
586 class via the
587 :meth:`_expression.FromClause.tablesample` method.
589 The TABLESAMPLE clause allows selecting a randomly selected approximate
590 percentage of rows from a table. It supports multiple sampling methods,
591 most commonly BERNOULLI and SYSTEM.
593 e.g.::
595 from sqlalchemy import func
597 selectable = people.tablesample(
598 func.bernoulli(1), name="alias", seed=func.random()
599 )
600 stmt = select(selectable.c.people_id)
602 Assuming ``people`` with a column ``people_id``, the above
603 statement would render as:
605 .. sourcecode:: sql
607 SELECT alias.people_id FROM
608 people AS alias TABLESAMPLE bernoulli(:bernoulli_1)
609 REPEATABLE (random())
611 :param sampling: a ``float`` percentage between 0 and 100 or
612 :class:`_functions.Function`.
614 :param name: optional alias name
616 :param seed: any real-valued SQL expression. When specified, the
617 REPEATABLE sub-clause is also rendered.
619 """
620 return TableSample._factory(selectable, sampling, name=name, seed=seed)
623@overload
624def union(
625 *selects: _TypedSelectable[Unpack[_Ts]],
626) -> CompoundSelect[Unpack[_Ts]]: ...
629@overload
630def union(
631 *selects: _SelectStatementForCompoundArgument[Unpack[_Ts]],
632) -> CompoundSelect[Unpack[_Ts]]: ...
635def union(
636 *selects: _SelectStatementForCompoundArgument[Unpack[_Ts]],
637) -> CompoundSelect[Unpack[_Ts]]:
638 r"""Return a ``UNION`` of multiple selectables.
640 The returned object is an instance of
641 :class:`_expression.CompoundSelect`.
643 A similar :func:`union()` method is available on all
644 :class:`_expression.FromClause` subclasses.
646 :param \*selects:
647 a list of :class:`_expression.Select` instances.
649 :param \**kwargs:
650 available keyword arguments are the same as those of
651 :func:`select`.
653 """
654 return CompoundSelect._create_union(*selects)
657@overload
658def union_all(
659 *selects: _TypedSelectable[Unpack[_Ts]],
660) -> CompoundSelect[Unpack[_Ts]]: ...
663@overload
664def union_all(
665 *selects: _SelectStatementForCompoundArgument[Unpack[_Ts]],
666) -> CompoundSelect[Unpack[_Ts]]: ...
669def union_all(
670 *selects: _SelectStatementForCompoundArgument[Unpack[_Ts]],
671) -> CompoundSelect[Unpack[_Ts]]:
672 r"""Return a ``UNION ALL`` of multiple selectables.
674 The returned object is an instance of
675 :class:`_expression.CompoundSelect`.
677 A similar :func:`union_all()` method is available on all
678 :class:`_expression.FromClause` subclasses.
680 :param \*selects:
681 a list of :class:`_expression.Select` instances.
683 """
684 return CompoundSelect._create_union_all(*selects)
687def values(
688 *columns: _OnlyColumnArgument[Any],
689 name: Optional[str] = None,
690 literal_binds: bool = False,
691) -> Values:
692 r"""Construct a :class:`_expression.Values` construct representing the
693 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)