1.. highlight:: pycon+sql 2 3.. |prev| replace:: :doc:`data_insert` 4.. |next| replace:: :doc:`data_update` 5 6.. include:: tutorial_nav_include.rst 7 8.. _tutorial_selecting_data: 9 10.. rst-class:: core-header, orm-dependency 11 12Selecting Rows with Core or ORM 13-------------------------------- 14 15For both Core and ORM, the :func:`_sql.select` function generates a 16:class:`_sql.Select` construct which is used for all SELECT queries. 17Passed to methods like :meth:`_future.Connection.execute` in Core and 18:meth:`_orm.Session.execute` in ORM, a SELECT statement is emitted in the 19current transaction and the result rows available via the returned 20:class:`_engine.Result` object. 21 22.. container:: orm-header 23 24 **ORM Readers** - the content here applies equally well to both Core and ORM 25 use and basic ORM variant use cases are mentioned here. However there are 26 a lot more ORM-specific features available as well; these are documented 27 at :ref:`queryguide_toplevel`. 28 29 30The select() SQL Expression Construct 31^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ 32 33The :func:`_sql.select` construct builds up a statement in the same way 34as that of :func:`_sql.insert`, using a :term:`generative` approach where 35each method builds more state onto the object. Like the other SQL constructs, 36it can be stringified in place:: 37 38 >>> from sqlalchemy import select 39 >>> stmt = select(user_table).where(user_table.c.name == 'spongebob') 40 >>> print(stmt) 41 {opensql}SELECT user_account.id, user_account.name, user_account.fullname 42 FROM user_account 43 WHERE user_account.name = :name_1 44 45Also in the same manner as all other statement-level SQL constructs, to 46actually run the statement we pass it to an execution method. 47Since a SELECT statement returns 48rows we can always iterate the result object to get :class:`_engine.Row` 49objects back: 50 51.. sourcecode:: pycon+sql 52 53 >>> with engine.connect() as conn: 54 ... for row in conn.execute(stmt): 55 ... print(row) 56 {opensql}BEGIN (implicit) 57 SELECT user_account.id, user_account.name, user_account.fullname 58 FROM user_account 59 WHERE user_account.name = ? 60 [...] ('spongebob',){stop} 61 (1, 'spongebob', 'Spongebob Squarepants') 62 {opensql}ROLLBACK{stop} 63 64When using the ORM, particularly with a :func:`_sql.select` construct that's 65composed against ORM entities, we will want to execute it using the 66:meth:`_orm.Session.execute` method on the :class:`_orm.Session`; using 67this approach, we continue to get :class:`_engine.Row` objects from the 68result, however these rows are now capable of including 69complete entities, such as instances of the ``User`` class, as individual 70elements within each row: 71 72.. sourcecode:: pycon+sql 73 74 >>> stmt = select(User).where(User.name == 'spongebob') 75 >>> with Session(engine) as session: 76 ... for row in session.execute(stmt): 77 ... print(row) 78 {opensql}BEGIN (implicit) 79 SELECT user_account.id, user_account.name, user_account.fullname 80 FROM user_account 81 WHERE user_account.name = ? 82 [...] ('spongebob',){stop} 83 (User(id=1, name='spongebob', fullname='Spongebob Squarepants'),) 84 {opensql}ROLLBACK{stop} 85 86.. topic:: select() from a Table vs. ORM class 87 88 While the SQL generated in these examples looks the same whether we invoke 89 ``select(user_table)`` or ``select(User)``, in the more general case 90 they do not necessarily render the same thing, as an ORM-mapped class 91 may be mapped to other kinds of "selectables" besides tables. The 92 ``select()`` that's against an ORM entity also indicates that ORM-mapped 93 instances should be returned in a result, which is not the case when 94 SELECTing from a :class:`_schema.Table` object. 95 96The following sections will discuss the SELECT construct in more detail. 97 98 99Setting the COLUMNS and FROM clause 100^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ 101 102The :func:`_sql.select` function accepts positional elements representing any 103number of :class:`_schema.Column` and/or :class:`_schema.Table` expressions, as 104well as a wide range of compatible objects, which are resolved into a list of SQL 105expressions to be SELECTed from that will be returned as columns in the result 106set. These elements also serve in simpler cases to create the FROM clause, 107which is inferred from the columns and table-like expressions passed:: 108 109 >>> print(select(user_table)) 110 {opensql}SELECT user_account.id, user_account.name, user_account.fullname 111 FROM user_account 112 113To SELECT from individual columns using a Core approach, 114:class:`_schema.Column` objects are accessed from the :attr:`_schema.Table.c` 115accessor and can be sent directly; the FROM clause will be inferred as the set 116of all :class:`_schema.Table` and other :class:`_sql.FromClause` objects that 117are represented by those columns:: 118 119 >>> print(select(user_table.c.name, user_table.c.fullname)) 120 {opensql}SELECT user_account.name, user_account.fullname 121 FROM user_account 122 123.. _tutorial_selecting_orm_entities: 124 125Selecting ORM Entities and Columns 126~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ 127 128ORM entities, such our ``User`` class as well as the column-mapped 129attributes upon it such as ``User.name``, also participate in the SQL Expression 130Language system representing tables and columns. Below illustrates an 131example of SELECTing from the ``User`` entity, which ultimately renders 132in the same way as if we had used ``user_table`` directly:: 133 134 >>> print(select(User)) 135 {opensql}SELECT user_account.id, user_account.name, user_account.fullname 136 FROM user_account 137 138When executing a statement like the above using the ORM :meth:`_orm.Session.execute` 139method, there is an important difference when we select from a full entity 140such as ``User``, as opposed to ``user_table``, which is that the **entity 141itself is returned as a single element within each row**. That is, when we fetch rows from 142the above statement, as there is only the ``User`` entity in the list of 143things to fetch, we get back :class:`_engine.Row` objects that have only one element, which contain 144instances of the ``User`` class:: 145 146 >>> row = session.execute(select(User)).first() 147 {opensql}BEGIN... 148 SELECT user_account.id, user_account.name, user_account.fullname 149 FROM user_account 150 [...] (){stop} 151 >>> row 152 (User(id=1, name='spongebob', fullname='Spongebob Squarepants'),) 153 154The above :class:`_engine.Row` has just one element, representing the ``User`` entity:: 155 156 >>> row[0] 157 User(id=1, name='spongebob', fullname='Spongebob Squarepants') 158 159Alternatively, we can select individual columns of an ORM entity as distinct 160elements within result rows, by using the class-bound attributes; when these 161are passed to a construct such as :func:`_sql.select`, they are resolved into 162the :class:`_schema.Column` or other SQL expression represented by each 163attribute:: 164 165 >>> print(select(User.name, User.fullname)) 166 {opensql}SELECT user_account.name, user_account.fullname 167 FROM user_account 168 169When we invoke *this* statement using :meth:`_orm.Session.execute`, we now 170receive rows that have individual elements per value, each corresponding 171to a separate column or other SQL expression:: 172 173 >>> row = session.execute(select(User.name, User.fullname)).first() 174 {opensql}SELECT user_account.name, user_account.fullname 175 FROM user_account 176 [...] (){stop} 177 >>> row 178 ('spongebob', 'Spongebob Squarepants') 179 180The approaches can also be mixed, as below where we SELECT the ``name`` 181attribute of the ``User`` entity as the first element of the row, and combine 182it with full ``Address`` entities in the second element:: 183 184 >>> session.execute( 185 ... select(User.name, Address). 186 ... where(User.id==Address.user_id). 187 ... order_by(Address.id) 188 ... ).all() 189 {opensql}SELECT user_account.name, address.id, address.email_address, address.user_id 190 FROM user_account, address 191 WHERE user_account.id = address.user_id ORDER BY address.id 192 [...] (){stop} 193 [('spongebob', Address(id=1, email_address='spongebob@sqlalchemy.org')), 194 ('sandy', Address(id=2, email_address='sandy@sqlalchemy.org')), 195 ('sandy', Address(id=3, email_address='sandy@squirrelpower.org'))] 196 197Approaches towards selecting ORM entities and columns as well as common methods 198for converting rows are discussed further at :ref:`orm_queryguide_select_columns`. 199 200.. seealso:: 201 202 :ref:`orm_queryguide_select_columns` - in the :ref:`queryguide_toplevel` 203 204Selecting from Labeled SQL Expressions 205~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ 206 207The :meth:`_sql.ColumnElement.label` method as well as the same-named method 208available on ORM attributes provides a SQL label of a column or expression, 209allowing it to have a specific name in a result set. This can be helpful 210when referring to arbitrary SQL expressions in a result row by name: 211 212.. sourcecode:: pycon+sql 213 214 >>> from sqlalchemy import func, cast 215 >>> stmt = ( 216 ... select( 217 ... ("Username: " + user_table.c.name).label("username"), 218 ... ).order_by(user_table.c.name) 219 ... ) 220 >>> with engine.connect() as conn: 221 ... for row in conn.execute(stmt): 222 ... print(f"{row.username}") 223 {opensql}BEGIN (implicit) 224 SELECT ? || user_account.name AS username 225 FROM user_account ORDER BY user_account.name 226 [...] ('Username: ',){stop} 227 Username: patrick 228 Username: sandy 229 Username: spongebob 230 {opensql}ROLLBACK{stop} 231 232.. seealso:: 233 234 :ref:`tutorial_order_by_label` - the label names we create may also be 235 referred towards in the ORDER BY or GROUP BY clause of the :class:`_sql.Select`. 236 237.. _tutorial_select_arbtrary_text: 238 239Selecting with Textual Column Expressions 240~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ 241 242When we construct a :class:`_sql.Select` object using the :func:`_sql.select` 243function, we are normally passing to it a series of :class:`_schema.Table` 244and :class:`_schema.Column` objects that were defined using 245:ref:`table metadata <tutorial_working_with_metadata>`, or when using the ORM we may be 246sending ORM-mapped attributes that represent table columns. However, 247sometimes there is also the need to manufacture arbitrary SQL blocks inside 248of statements, such as constant string expressions, or just some arbitrary 249SQL that's quicker to write literally. 250 251The :func:`_sql.text` construct introduced at 252:ref:`tutorial_working_with_transactions` can in fact be embedded into a 253:class:`_sql.Select` construct directly, such as below where we manufacture 254a hardcoded string literal ``'some label'`` and embed it within the 255SELECT statement:: 256 257 >>> from sqlalchemy import text 258 >>> stmt = ( 259 ... select( 260 ... text("'some phrase'"), user_table.c.name 261 ... ).order_by(user_table.c.name) 262 ... ) 263 >>> with engine.connect() as conn: 264 ... print(conn.execute(stmt).all()) 265 {opensql}BEGIN (implicit) 266 SELECT 'some phrase', user_account.name 267 FROM user_account ORDER BY user_account.name 268 [generated in ...] () 269 {stop}[('some phrase', 'patrick'), ('some phrase', 'sandy'), ('some phrase', 'spongebob')] 270 {opensql}ROLLBACK{stop} 271 272While the :func:`_sql.text` construct can be used in most places to inject 273literal SQL phrases, more often than not we are actually dealing with textual 274units that each represent an individual 275column expression. In this common case we can get more functionality out of 276our textual fragment using the :func:`_sql.literal_column` 277construct instead. This object is similar to :func:`_sql.text` except that 278instead of representing arbitrary SQL of any form, 279it explicitly represents a single "column" and can then be labeled and referred 280towards in subqueries and other expressions:: 281 282 283 >>> from sqlalchemy import literal_column 284 >>> stmt = ( 285 ... select( 286 ... literal_column("'some phrase'").label("p"), user_table.c.name 287 ... ).order_by(user_table.c.name) 288 ... ) 289 >>> with engine.connect() as conn: 290 ... for row in conn.execute(stmt): 291 ... print(f"{row.p}, {row.name}") 292 {opensql}BEGIN (implicit) 293 SELECT 'some phrase' AS p, user_account.name 294 FROM user_account ORDER BY user_account.name 295 [generated in ...] () 296 {stop}some phrase, patrick 297 some phrase, sandy 298 some phrase, spongebob 299 {opensql}ROLLBACK{stop} 300 301 302Note that in both cases, when using :func:`_sql.text` or 303:func:`_sql.literal_column`, we are writing a syntactical SQL expression, and 304not a literal value. We therefore have to include whatever quoting or syntaxes 305are necessary for the SQL we want to see rendered. 306 307.. _tutorial_select_where_clause: 308 309The WHERE clause 310^^^^^^^^^^^^^^^^ 311 312SQLAlchemy allows us to compose SQL expressions, such as ``name = 'squidward'`` 313or ``user_id > 10``, by making use of standard Python operators in 314conjunction with 315:class:`_schema.Column` and similar objects. For boolean expressions, most 316Python operators such as ``==``, ``!=``, ``<``, ``>=`` etc. generate new 317SQL Expression objects, rather than plain boolean ``True``/``False`` values:: 318 319 >>> print(user_table.c.name == 'squidward') 320 user_account.name = :name_1 321 322 >>> print(address_table.c.user_id > 10) 323 address.user_id > :user_id_1 324 325 326We can use expressions like these to generate the WHERE clause by passing 327the resulting objects to the :meth:`_sql.Select.where` method:: 328 329 >>> print(select(user_table).where(user_table.c.name == 'squidward')) 330 {opensql}SELECT user_account.id, user_account.name, user_account.fullname 331 FROM user_account 332 WHERE user_account.name = :name_1 333 334 335To produce multiple expressions joined by AND, the :meth:`_sql.Select.where` 336method may be invoked any number of times:: 337 338 >>> print( 339 ... select(address_table.c.email_address). 340 ... where(user_table.c.name == 'squidward'). 341 ... where(address_table.c.user_id == user_table.c.id) 342 ... ) 343 {opensql}SELECT address.email_address 344 FROM address, user_account 345 WHERE user_account.name = :name_1 AND address.user_id = user_account.id 346 347A single call to :meth:`_sql.Select.where` also accepts multiple expressions 348with the same effect:: 349 350 >>> print( 351 ... select(address_table.c.email_address). 352 ... where( 353 ... user_table.c.name == 'squidward', 354 ... address_table.c.user_id == user_table.c.id 355 ... ) 356 ... ) 357 {opensql}SELECT address.email_address 358 FROM address, user_account 359 WHERE user_account.name = :name_1 AND address.user_id = user_account.id 360 361"AND" and "OR" conjunctions are both available directly using the 362:func:`_sql.and_` and :func:`_sql.or_` functions, illustrated below in terms 363of ORM entities:: 364 365 >>> from sqlalchemy import and_, or_ 366 >>> print( 367 ... select(Address.email_address). 368 ... where( 369 ... and_( 370 ... or_(User.name == 'squidward', User.name == 'sandy'), 371 ... Address.user_id == User.id 372 ... ) 373 ... ) 374 ... ) 375 {opensql}SELECT address.email_address 376 FROM address, user_account 377 WHERE (user_account.name = :name_1 OR user_account.name = :name_2) 378 AND address.user_id = user_account.id 379 380For simple "equality" comparisons against a single entity, there's also a 381popular method known as :meth:`_sql.Select.filter_by` which accepts keyword 382arguments that match to column keys or ORM attribute names. It will filter 383against the leftmost FROM clause or the last entity joined:: 384 385 >>> print( 386 ... select(User).filter_by(name='spongebob', fullname='Spongebob Squarepants') 387 ... ) 388 {opensql}SELECT user_account.id, user_account.name, user_account.fullname 389 FROM user_account 390 WHERE user_account.name = :name_1 AND user_account.fullname = :fullname_1 391 392 393.. seealso:: 394 395 396 :doc:`/core/operators` - descriptions of most SQL operator functions in SQLAlchemy 397 398 399.. _tutorial_select_join: 400 401Explicit FROM clauses and JOINs 402^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ 403 404As mentioned previously, the FROM clause is usually **inferred** 405based on the expressions that we are setting in the columns 406clause as well as other elements of the :class:`_sql.Select`. 407 408If we set a single column from a particular :class:`_schema.Table` 409in the COLUMNS clause, it puts that :class:`_schema.Table` in the FROM 410clause as well:: 411 412 >>> print(select(user_table.c.name)) 413 {opensql}SELECT user_account.name 414 FROM user_account 415 416If we were to put columns from two tables, then we get a comma-separated FROM 417clause:: 418 419 >>> print(select(user_table.c.name, address_table.c.email_address)) 420 {opensql}SELECT user_account.name, address.email_address 421 FROM user_account, address 422 423In order to JOIN these two tables together, we typically use one of two methods 424on :class:`_sql.Select`. The first is the :meth:`_sql.Select.join_from` 425method, which allows us to indicate the left and right side of the JOIN 426explicitly:: 427 428 >>> print( 429 ... select(user_table.c.name, address_table.c.email_address). 430 ... join_from(user_table, address_table) 431 ... ) 432 {opensql}SELECT user_account.name, address.email_address 433 FROM user_account JOIN address ON user_account.id = address.user_id 434 435 436The other is the the :meth:`_sql.Select.join` method, which indicates only the 437right side of the JOIN, the left hand-side is inferred:: 438 439 >>> print( 440 ... select(user_table.c.name, address_table.c.email_address). 441 ... join(address_table) 442 ... ) 443 {opensql}SELECT user_account.name, address.email_address 444 FROM user_account JOIN address ON user_account.id = address.user_id 445 446.. sidebar:: The ON Clause is inferred 447 448 When using :meth:`_sql.Select.join_from` or :meth:`_sql.Select.join`, we may 449 observe that the ON clause of the join is also inferred for us in simple 450 foreign key cases. More on that in the next section. 451 452We also have the option to add elements to the FROM clause explicitly, if it is not 453inferred the way we want from the columns clause. We use the 454:meth:`_sql.Select.select_from` method to achieve this, as below 455where we establish ``user_table`` as the first element in the FROM 456clause and :meth:`_sql.Select.join` to establish ``address_table`` as 457the second:: 458 459 >>> print( 460 ... select(address_table.c.email_address). 461 ... select_from(user_table).join(address_table) 462 ... ) 463 {opensql}SELECT address.email_address 464 FROM user_account JOIN address ON user_account.id = address.user_id 465 466Another example where we might want to use :meth:`_sql.Select.select_from` 467is if our columns clause doesn't have enough information to provide for a 468FROM clause. For example, to SELECT from the common SQL expression 469``count(*)``, we use a SQLAlchemy element known as :attr:`_sql.func` to 470produce the SQL ``count()`` function:: 471 472 >>> from sqlalchemy import func 473 >>> print ( 474 ... select(func.count('*')).select_from(user_table) 475 ... ) 476 {opensql}SELECT count(:count_2) AS count_1 477 FROM user_account 478 479.. seealso:: 480 481 :ref:`orm_queryguide_select_from` - in the :ref:`queryguide_toplevel` - 482 contains additional examples and notes 483 regarding the interaction of :meth:`_sql.Select.select_from` and 484 :meth:`_sql.Select.join`. 485 486.. _tutorial_select_join_onclause: 487 488Setting the ON Clause 489~~~~~~~~~~~~~~~~~~~~~ 490 491The previous examples of JOIN illustrated that the :class:`_sql.Select` construct 492can join between two tables and produce the ON clause automatically. This 493occurs in those examples because the ``user_table`` and ``address_table`` 494:class:`_sql.Table` objects include a single :class:`_schema.ForeignKeyConstraint` 495definition which is used to form this ON clause. 496 497If the left and right targets of the join do not have such a constraint, or 498there are multiple constraints in place, we need to specify the ON clause 499directly. Both :meth:`_sql.Select.join` and :meth:`_sql.Select.join_from` 500accept an additional argument for the ON clause, which is stated using the 501same SQL Expression mechanics as we saw about in :ref:`tutorial_select_where_clause`:: 502 503 >>> print( 504 ... select(address_table.c.email_address). 505 ... select_from(user_table). 506 ... join(address_table, user_table.c.id == address_table.c.user_id) 507 ... ) 508 {opensql}SELECT address.email_address 509 FROM user_account JOIN address ON user_account.id = address.user_id 510 511.. container:: orm-header 512 513 **ORM Tip** - there's another way to generate the ON clause when using 514 ORM entities that make use of the :func:`_orm.relationship` construct, 515 like the mapping set up in the previous section at 516 :ref:`tutorial_declaring_mapped_classes`. 517 This is a whole subject onto itself, which is introduced at length 518 at :ref:`tutorial_joining_relationships`. 519 520OUTER and FULL join 521~~~~~~~~~~~~~~~~~~~ 522 523Both the :meth:`_sql.Select.join` and :meth:`_sql.Select.join_from` methods 524accept keyword arguments :paramref:`_sql.Select.join.isouter` and 525:paramref:`_sql.Select.join.full` which will render LEFT OUTER JOIN 526and FULL OUTER JOIN, respectively:: 527 528 >>> print( 529 ... select(user_table).join(address_table, isouter=True) 530 ... ) 531 {opensql}SELECT user_account.id, user_account.name, user_account.fullname 532 FROM user_account LEFT OUTER JOIN address ON user_account.id = address.user_id{stop} 533 534 >>> print( 535 ... select(user_table).join(address_table, full=True) 536 ... ) 537 {opensql}SELECT user_account.id, user_account.name, user_account.fullname 538 FROM user_account FULL OUTER JOIN address ON user_account.id = address.user_id{stop} 539 540There is also a method :meth:`_sql.Select.outerjoin` that is equivalent to 541using ``.join(..., isouter=True)``. 542 543.. tip:: 544 545 SQL also has a "RIGHT OUTER JOIN". SQLAlchemy doesn't render this directly; 546 instead, reverse the order of the tables and use "LEFT OUTER JOIN". 547 548.. _tutorial_order_by_group_by_having: 549 550ORDER BY, GROUP BY, HAVING 551^^^^^^^^^^^^^^^^^^^^^^^^^^^ 552 553The SELECT SQL statement includes a clause called ORDER BY which is used to 554return the selected rows within a given ordering. 555 556The GROUP BY clause is constructed similarly to the ORDER BY clause, and has 557the purpose of sub-dividing the selected rows into specific groups upon which 558aggregate functions may be invoked. The HAVING clause is usually used with 559GROUP BY and is of a similar form to the WHERE clause, except that it's applied 560to the aggregated functions used within groups. 561 562.. _tutorial_order_by: 563 564ORDER BY 565~~~~~~~~ 566 567The ORDER BY clause is constructed in terms 568of SQL Expression constructs typically based on :class:`_schema.Column` or 569similar objects. The :meth:`_sql.Select.order_by` method accepts one or 570more of these expressions positionally:: 571 572 >>> print(select(user_table).order_by(user_table.c.name)) 573 {opensql}SELECT user_account.id, user_account.name, user_account.fullname 574 FROM user_account ORDER BY user_account.name 575 576Ascending / descending is available from the :meth:`_sql.ColumnElement.asc` 577and :meth:`_sql.ColumnElement.desc` modifiers, which are present 578from ORM-bound attributes as well:: 579 580 581 >>> print(select(User).order_by(User.fullname.desc())) 582 {opensql}SELECT user_account.id, user_account.name, user_account.fullname 583 FROM user_account ORDER BY user_account.fullname DESC 584 585The above statement will yield rows that are sorted by the 586``user_account.fullname`` column in descending order. 587 588.. _tutorial_group_by_w_aggregates: 589 590Aggregate functions with GROUP BY / HAVING 591~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ 592 593In SQL, aggregate functions allow column expressions across multiple rows 594to be aggregated together to produce a single result. Examples include 595counting, computing averages, as well as locating the maximum or minimum 596value in a set of values. 597 598SQLAlchemy provides for SQL functions in an open-ended way using a namespace 599known as :data:`_sql.func`. This is a special constructor object which 600will create new instances of :class:`_functions.Function` when given the name 601of a particular SQL function, which can have any name, as well as zero or 602more arguments to pass to the function, which are, like in all other cases, 603SQL Expression constructs. For example, to 604render the SQL COUNT() function against the ``user_account.id`` column, 605we call upon the ``count()`` name:: 606 607 >>> from sqlalchemy import func 608 >>> count_fn = func.count(user_table.c.id) 609 >>> print(count_fn) 610 {opensql}count(user_account.id) 611 612SQL functions are described in more detail later in this tutorial at 613:ref:`tutorial_functions`. 614 615When using aggregate functions in SQL, the GROUP BY clause is essential in that 616it allows rows to be partitioned into groups where aggregate functions will 617be applied to each group individually. When requesting non-aggregated columns 618in the COLUMNS clause of a SELECT statement, SQL requires that these columns 619all be subject to a GROUP BY clause, either directly or indirectly based on 620a primary key association. The HAVING clause is then used in a similar 621manner as the WHERE clause, except that it filters out rows based on aggregated 622values rather than direct row contents. 623 624SQLAlchemy provides for these two clauses using the :meth:`_sql.Select.group_by` 625and :meth:`_sql.Select.having` methods. Below we illustrate selecting 626user name fields as well as count of addresses, for those users that have more 627than one address: 628 629.. sourcecode:: python+sql 630 631 >>> with engine.connect() as conn: 632 ... result = conn.execute( 633 ... select(User.name, func.count(Address.id).label("count")). 634 ... join(Address). 635 ... group_by(User.name). 636 ... having(func.count(Address.id) > 1) 637 ... ) 638 ... print(result.all()) 639 {opensql}BEGIN (implicit) 640 SELECT user_account.name, count(address.id) AS count 641 FROM user_account JOIN address ON user_account.id = address.user_id GROUP BY user_account.name 642 HAVING count(address.id) > ? 643 [...] (1,){stop} 644 [('sandy', 2)] 645 {opensql}ROLLBACK{stop} 646 647.. _tutorial_order_by_label: 648 649Ordering or Grouping by a Label 650~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ 651 652An important technique, in particular on some database backends, is the ability 653to ORDER BY or GROUP BY an expression that is already stated in the columns 654clause, without re-stating the expression in the ORDER BY or GROUP BY clause 655and instead using the column name or labeled name from the COLUMNS clause. 656This form is available by passing the string text of the name to the 657:meth:`_sql.Select.order_by` or :meth:`_sql.Select.group_by` method. The text 658passed is **not rendered directly**; instead, the name given to an expression 659in the columns clause and rendered as that expression name in context, raising an 660error if no match is found. The unary modifiers 661:func:`.asc` and :func:`.desc` may also be used in this form: 662 663.. sourcecode:: pycon+sql 664 665 >>> from sqlalchemy import func, desc 666 >>> stmt = select( 667 ... Address.user_id, 668 ... func.count(Address.id).label('num_addresses')).\ 669 ... group_by("user_id").order_by("user_id", desc("num_addresses")) 670 >>> print(stmt) 671 {opensql}SELECT address.user_id, count(address.id) AS num_addresses 672 FROM address GROUP BY address.user_id ORDER BY address.user_id, num_addresses DESC 673 674.. _tutorial_using_aliases: 675 676Using Aliases 677^^^^^^^^^^^^^ 678 679Now that we are selecting from multiple tables and using joins, we quickly 680run into the case where we need to refer to the same table multiple times 681in the FROM clause of a statement. We accomplish this using SQL **aliases**, 682which are a syntax that supplies an alternative name to a table or subquery 683from which it can be referred towards in the statement. 684 685In the SQLAlchemy Expression Language, these "names" are instead represented by 686:class:`_sql.FromClause` objects known as the :class:`_sql.Alias` construct, 687which is constructed in Core using the :meth:`_sql.FromClause.alias` 688method. An :class:`_sql.Alias` construct is just like a :class:`_sql.Table` 689construct in that it also has a namespace of :class:`_schema.Column` 690objects within the :attr:`_sql.Alias.c` collection. The SELECT statement 691below for example returns all unique pairs of user names:: 692 693 >>> user_alias_1 = user_table.alias() 694 >>> user_alias_2 = user_table.alias() 695 >>> print( 696 ... select(user_alias_1.c.name, user_alias_2.c.name). 697 ... join_from(user_alias_1, user_alias_2, user_alias_1.c.id > user_alias_2.c.id) 698 ... ) 699 {opensql}SELECT user_account_1.name, user_account_2.name AS name_1 700 FROM user_account AS user_account_1 701 JOIN user_account AS user_account_2 ON user_account_1.id > user_account_2.id 702 703.. _tutorial_orm_entity_aliases: 704 705ORM Entity Aliases 706~~~~~~~~~~~~~~~~~~ 707 708The ORM equivalent of the :meth:`_sql.FromClause.alias` method is the 709ORM :func:`_orm.aliased` function, which may be applied to an entity 710such as ``User`` and ``Address``. This produces a :class:`_sql.Alias` object 711internally that's against the original mapped :class:`_schema.Table` object, 712while maintaining ORM functionality. The SELECT below selects from the 713``User`` entity all objects that include two particular email addresses:: 714 715 >>> from sqlalchemy.orm import aliased 716 >>> address_alias_1 = aliased(Address) 717 >>> address_alias_2 = aliased(Address) 718 >>> print( 719 ... select(User). 720 ... join_from(User, address_alias_1). 721 ... where(address_alias_1.email_address == 'patrick@aol.com'). 722 ... join_from(User, address_alias_2). 723 ... where(address_alias_2.email_address == 'patrick@gmail.com') 724 ... ) 725 {opensql}SELECT user_account.id, user_account.name, user_account.fullname 726 FROM user_account 727 JOIN address AS address_1 ON user_account.id = address_1.user_id 728 JOIN address AS address_2 ON user_account.id = address_2.user_id 729 WHERE address_1.email_address = :email_address_1 730 AND address_2.email_address = :email_address_2 731 732.. tip:: 733 734 As mentioned in :ref:`tutorial_select_join_onclause`, the ORM provides 735 for another way to join using the :func:`_orm.relationship` construct. 736 The above example using aliases is demonstrated using :func:`_orm.relationship` 737 at :ref:`tutorial_joining_relationships_aliased`. 738 739 740.. _tutorial_subqueries_ctes: 741 742Subqueries and CTEs 743^^^^^^^^^^^^^^^^^^^^ 744 745A subquery in SQL is a SELECT statement that is rendered within parenthesis and 746placed within the context of an enclosing statement, typically a SELECT 747statement but not necessarily. 748 749This section will cover a so-called "non-scalar" subquery, which is typically 750placed in the FROM clause of an enclosing SELECT. We will also cover the 751Common Table Expression or CTE, which is used in a similar way as a subquery, 752but includes additional features. 753 754SQLAlchemy uses the :class:`_sql.Subquery` object to represent a subquery and 755the :class:`_sql.CTE` to represent a CTE, usually obtained from the 756:meth:`_sql.Select.subquery` and :meth:`_sql.Select.cte` methods, respectively. 757Either object can be used as a FROM element inside of a larger 758:func:`_sql.select` construct. 759 760We can construct a :class:`_sql.Subquery` that will select an aggregate count 761of rows from the ``address`` table (aggregate functions and GROUP BY were 762introduced previously at :ref:`tutorial_group_by_w_aggregates`): 763 764 >>> subq = select( 765 ... func.count(address_table.c.id).label("count"), 766 ... address_table.c.user_id 767 ... ).group_by(address_table.c.user_id).subquery() 768 769Stringifying the subquery by itself without it being embedded inside of another 770:class:`_sql.Select` or other statement produces the plain SELECT statement 771without any enclosing parenthesis:: 772 773 >>> print(subq) 774 {opensql}SELECT count(address.id) AS count, address.user_id 775 FROM address GROUP BY address.user_id 776 777 778The :class:`_sql.Subquery` object behaves like any other FROM object such 779as a :class:`_schema.Table`, notably that it includes a :attr:`_sql.Subquery.c` 780namespace of the columns which it selects. We can use this namespace to 781refer to both the ``user_id`` column as well as our custom labeled 782``count`` expression:: 783 784 >>> print(select(subq.c.user_id, subq.c.count)) 785 {opensql}SELECT anon_1.user_id, anon_1.count 786 FROM (SELECT count(address.id) AS count, address.user_id AS user_id 787 FROM address GROUP BY address.user_id) AS anon_1 788 789With a selection of rows contained within the ``subq`` object, we can apply 790the object to a larger :class:`_sql.Select` that will join the data to 791the ``user_account`` table:: 792 793 >>> stmt = select( 794 ... user_table.c.name, 795 ... user_table.c.fullname, 796 ... subq.c.count 797 ... ).join_from(user_table, subq) 798 799 >>> print(stmt) 800 {opensql}SELECT user_account.name, user_account.fullname, anon_1.count 801 FROM user_account JOIN (SELECT count(address.id) AS count, address.user_id AS user_id 802 FROM address GROUP BY address.user_id) AS anon_1 ON user_account.id = anon_1.user_id 803 804In order to join from ``user_account`` to ``address``, we made use of the 805:meth:`_sql.Select.join_from` method. As has been illustrated previously, the 806ON clause of this join was again **inferred** based on foreign key constraints. 807Even though a SQL subquery does not itself have any constraints, SQLAlchemy can 808act upon constraints represented on the columns by determining that the 809``subq.c.user_id`` column is **derived** from the ``address_table.c.user_id`` 810column, which does express a foreign key relationship back to the 811``user_table.c.id`` column which is then used to generate the ON clause. 812 813Common Table Expressions (CTEs) 814~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ 815 816Usage of the :class:`_sql.CTE` construct in SQLAlchemy is virtually 817the same as how the :class:`_sql.Subquery` construct is used. By changing 818the invocation of the :meth:`_sql.Select.subquery` method to use 819:meth:`_sql.Select.cte` instead, we can use the resulting object as a FROM 820element in the same way, but the SQL rendered is the very different common 821table expression syntax:: 822 823 >>> subq = select( 824 ... func.count(address_table.c.id).label("count"), 825 ... address_table.c.user_id 826 ... ).group_by(address_table.c.user_id).cte() 827 828 >>> stmt = select( 829 ... user_table.c.name, 830 ... user_table.c.fullname, 831 ... subq.c.count 832 ... ).join_from(user_table, subq) 833 834 >>> print(stmt) 835 {opensql}WITH anon_1 AS 836 (SELECT count(address.id) AS count, address.user_id AS user_id 837 FROM address GROUP BY address.user_id) 838 SELECT user_account.name, user_account.fullname, anon_1.count 839 FROM user_account JOIN anon_1 ON user_account.id = anon_1.user_id 840 841The :class:`_sql.CTE` construct also features the ability to be used 842in a "recursive" style, and may in more elaborate cases be composed from the 843RETURNING clause of an INSERT, UPDATE or DELETE statement. The docstring 844for :class:`_sql.CTE` includes details on these additional patterns. 845 846In both cases, the subquery and CTE were named at the SQL level using an 847"anonymous" name. In the Python code, we don't need to provide these names 848at all. The object identity of the :class:`_sql.Subquery` or :class:`_sql.CTE` 849instances serves as the syntactical identity of the object when rendered. 850A name that will be rendered in the SQL can be provided by passing it as the 851first argument of the :meth:`_sql.Select.subquery` or :meth:`_sql.Select.cte` methods. 852 853.. seealso:: 854 855 :meth:`_sql.Select.subquery` - further detail on subqueries 856 857 :meth:`_sql.Select.cte` - examples for CTE including how to use 858 RECURSIVE as well as DML-oriented CTEs 859 860.. _tutorial_subqueries_orm_aliased: 861 862ORM Entity Subqueries/CTEs 863~~~~~~~~~~~~~~~~~~~~~~~~~~ 864 865In the ORM, the :func:`_orm.aliased` construct may be used to associate an ORM 866entity, such as our ``User`` or ``Address`` class, with any :class:`_sql.FromClause` 867concept that represents a source of rows. The preceding section 868:ref:`tutorial_orm_entity_aliases` illustrates using :func:`_orm.aliased` 869to associate the mapped class with an :class:`_sql.Alias` of its 870mapped :class:`_schema.Table`. Here we illustrate :func:`_orm.aliased` doing the same 871thing against both a :class:`_sql.Subquery` as well as a :class:`_sql.CTE` 872generated against a :class:`_sql.Select` construct, that ultimately derives 873from that same mapped :class:`_schema.Table`. 874 875Below is an example of applying :func:`_orm.aliased` to the :class:`_sql.Subquery` 876construct, so that ORM entities can be extracted from its rows. The result 877shows a series of ``User`` and ``Address`` objects, where the data for 878each ``Address`` object ultimately came from a subquery against the 879``address`` table rather than that table directly: 880 881.. sourcecode:: python+sql 882 883 >>> subq = select(Address).where(~Address.email_address.like('%@aol.com')).subquery() 884 >>> address_subq = aliased(Address, subq) 885 >>> stmt = select(User, address_subq).join_from(User, address_subq).order_by(User.id, address_subq.id) 886 >>> with Session(engine) as session: 887 ... for user, address in session.execute(stmt): 888 ... print(f"{user} {address}") 889 {opensql}BEGIN (implicit) 890 SELECT user_account.id, user_account.name, user_account.fullname, 891 anon_1.id AS id_1, anon_1.email_address, anon_1.user_id 892 FROM user_account JOIN 893 (SELECT address.id AS id, address.email_address AS email_address, address.user_id AS user_id 894 FROM address 895 WHERE address.email_address NOT LIKE ?) AS anon_1 ON user_account.id = anon_1.user_id 896 ORDER BY user_account.id, anon_1.id 897 [...] ('%@aol.com',){stop} 898 User(id=1, name='spongebob', fullname='Spongebob Squarepants') Address(id=1, email_address='spongebob@sqlalchemy.org') 899 User(id=2, name='sandy', fullname='Sandy Cheeks') Address(id=2, email_address='sandy@sqlalchemy.org') 900 User(id=2, name='sandy', fullname='Sandy Cheeks') Address(id=3, email_address='sandy@squirrelpower.org') 901 {opensql}ROLLBACK{stop} 902 903Another example follows, which is exactly the same except it makes use of the 904:class:`_sql.CTE` construct instead: 905 906.. sourcecode:: python+sql 907 908 >>> cte_obj = select(Address).where(~Address.email_address.like('%@aol.com')).cte() 909 >>> address_cte = aliased(Address, cte_obj) 910 >>> stmt = select(User, address_cte).join_from(User, address_cte).order_by(User.id, address_cte.id) 911 >>> with Session(engine) as session: 912 ... for user, address in session.execute(stmt): 913 ... print(f"{user} {address}") 914 {opensql}BEGIN (implicit) 915 WITH anon_1 AS 916 (SELECT address.id AS id, address.email_address AS email_address, address.user_id AS user_id 917 FROM address 918 WHERE address.email_address NOT LIKE ?) 919 SELECT user_account.id, user_account.name, user_account.fullname, 920 anon_1.id AS id_1, anon_1.email_address, anon_1.user_id 921 FROM user_account 922 JOIN anon_1 ON user_account.id = anon_1.user_id 923 ORDER BY user_account.id, anon_1.id 924 [...] ('%@aol.com',){stop} 925 User(id=1, name='spongebob', fullname='Spongebob Squarepants') Address(id=1, email_address='spongebob@sqlalchemy.org') 926 User(id=2, name='sandy', fullname='Sandy Cheeks') Address(id=2, email_address='sandy@sqlalchemy.org') 927 User(id=2, name='sandy', fullname='Sandy Cheeks') Address(id=3, email_address='sandy@squirrelpower.org') 928 {opensql}ROLLBACK{stop} 929 930.. seealso:: 931 932 :ref:`orm_queryguide_subqueries` - in the :ref:`queryguide_toplevel` 933 934.. _tutorial_scalar_subquery: 935 936Scalar and Correlated Subqueries 937^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ 938 939A scalar subquery is a subquery that returns exactly zero or one row and 940exactly one column. The subquery is then used in the COLUMNS or WHERE clause 941of an enclosing SELECT statement and is different than a regular subquery in 942that it is not used in the FROM clause. A :term:`correlated subquery` is a 943scalar subquery that refers to a table in the enclosing SELECT statement. 944 945SQLAlchemy represents the scalar subquery using the 946:class:`_sql.ScalarSelect` construct, which is part of the 947:class:`_sql.ColumnElement` expression hierarchy, in contrast to the regular 948subquery which is represented by the :class:`_sql.Subquery` construct, which is 949in the :class:`_sql.FromClause` hierarchy. 950 951Scalar subqueries are often, but not necessarily, used with aggregate functions, 952introduced previously at :ref:`tutorial_group_by_w_aggregates`. A scalar 953subquery is indicated explicitly by making use of the :meth:`_sql.Select.scalar_subquery` 954method as below. It's default string form when stringified by itself 955renders as an ordinary SELECT statement that is selecting from two tables:: 956 957 >>> subq = select(func.count(address_table.c.id)).\ 958 ... where(user_table.c.id == address_table.c.user_id).\ 959 ... scalar_subquery() 960 >>> print(subq) 961 {opensql}(SELECT count(address.id) AS count_1 962 FROM address, user_account 963 WHERE user_account.id = address.user_id) 964 965The above ``subq`` object now falls within the :class:`_sql.ColumnElement` 966SQL expression hierarchy, in that it may be used like any other column 967expression:: 968 969 >>> print(subq == 5) 970 {opensql}(SELECT count(address.id) AS count_1 971 FROM address, user_account 972 WHERE user_account.id = address.user_id) = :param_1 973 974 975Although the scalar subquery by itself renders both ``user_account`` and 976``address`` in its FROM clause when stringified by itself, when embedding it 977into an enclosing :func:`_sql.select` construct that deals with the 978``user_account`` table, the ``user_account`` table is automatically 979**correlated**, meaning it does not render in the FROM clause of the subquery:: 980 981 >>> stmt = select(user_table.c.name, subq.label("address_count")) 982 >>> print(stmt) 983 {opensql}SELECT user_account.name, (SELECT count(address.id) AS count_1 984 FROM address 985 WHERE user_account.id = address.user_id) AS address_count 986 FROM user_account 987 988Simple correlated subqueries will usually do the right thing that's desired. 989However, in the case where the correlation is ambiguous, SQLAlchemy will let 990us know that more clarity is needed:: 991 992 >>> stmt = select( 993 ... user_table.c.name, 994 ... address_table.c.email_address, 995 ... subq.label("address_count") 996 ... ).\ 997 ... join_from(user_table, address_table).\ 998 ... order_by(user_table.c.id, address_table.c.id) 999 >>> print(stmt) 1000 Traceback (most recent call last): 1001 ... 1002 InvalidRequestError: Select statement '<... Select object at ...>' returned 1003 no FROM clauses due to auto-correlation; specify correlate(<tables>) to 1004 control correlation manually. 1005 1006To specify that the ``user_table`` is the one we seek to correlate we specify 1007this using the :meth:`_sql.ScalarSelect.correlate` or 1008:meth:`_sql.ScalarSelect.correlate_except` methods:: 1009 1010 >>> subq = select(func.count(address_table.c.id)).\ 1011 ... where(user_table.c.id == address_table.c.user_id).\ 1012 ... scalar_subquery().correlate(user_table) 1013 1014The statement then can return the data for this column like any other: 1015 1016.. sourcecode:: pycon+sql 1017 1018 >>> with engine.connect() as conn: 1019 ... result = conn.execute( 1020 ... select( 1021 ... user_table.c.name, 1022 ... address_table.c.email_address, 1023 ... subq.label("address_count") 1024 ... ). 1025 ... join_from(user_table, address_table). 1026 ... order_by(user_table.c.id, address_table.c.id) 1027 ... ) 1028 ... print(result.all()) 1029 {opensql}BEGIN (implicit) 1030 SELECT user_account.name, address.email_address, (SELECT count(address.id) AS count_1 1031 FROM address 1032 WHERE user_account.id = address.user_id) AS address_count 1033 FROM user_account JOIN address ON user_account.id = address.user_id ORDER BY user_account.id, address.id 1034 [...] (){stop} 1035 [('spongebob', 'spongebob@sqlalchemy.org', 1), ('sandy', 'sandy@sqlalchemy.org', 2), 1036 ('sandy', 'sandy@squirrelpower.org', 2)] 1037 {opensql}ROLLBACK{stop} 1038 1039.. _tutorial_union: 1040 1041UNION, UNION ALL and other set operations 1042^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ 1043 1044In SQL,SELECT statements can be merged together using the UNION or UNION ALL 1045SQL operation, which produces the set of all rows produced by one or more 1046statements together. Other set operations such as INTERSECT [ALL] and 1047EXCEPT [ALL] are also possible. 1048 1049SQLAlchemy's :class:`_sql.Select` construct supports compositions of this 1050nature using functions like :func:`_sql.union`, :func:`_sql.intersect` and 1051:func:`_sql.except_`, and the "all" counterparts :func:`_sql.union_all`, 1052:func:`_sql.intersect_all` and :func:`_sql.except_all`. These functions all 1053accept an arbitrary number of sub-selectables, which are typically 1054:class:`_sql.Select` constructs but may also be an existing composition. 1055 1056The construct produced by these functions is the :class:`_sql.CompoundSelect`, 1057which is used in the same manner as the :class:`_sql.Select` construct, except 1058that it has fewer methods. The :class:`_sql.CompoundSelect` produced by 1059:func:`_sql.union_all` for example may be invoked directly using 1060:meth:`_engine.Connection.execute`:: 1061 1062 >>> from sqlalchemy import union_all 1063 >>> stmt1 = select(user_table).where(user_table.c.name == 'sandy') 1064 >>> stmt2 = select(user_table).where(user_table.c.name == 'spongebob') 1065 >>> u = union_all(stmt1, stmt2) 1066 >>> with engine.connect() as conn: 1067 ... result = conn.execute(u) 1068 ... print(result.all()) 1069 {opensql}BEGIN (implicit) 1070 SELECT user_account.id, user_account.name, user_account.fullname 1071 FROM user_account 1072 WHERE user_account.name = ? 1073 UNION ALL SELECT user_account.id, user_account.name, user_account.fullname 1074 FROM user_account 1075 WHERE user_account.name = ? 1076 [generated in ...] ('sandy', 'spongebob') 1077 {stop}[(2, 'sandy', 'Sandy Cheeks'), (1, 'spongebob', 'Spongebob Squarepants')] 1078 {opensql}ROLLBACK{stop} 1079 1080To use a :class:`_sql.CompoundSelect` as a subquery, just like :class:`_sql.Select` 1081it provides a :meth:`_sql.SelectBase.subquery` method which will produce a 1082:class:`_sql.Subquery` object with a :attr:`_sql.FromClause.c` 1083collection that may be referred towards in an enclosing :func:`_sql.select`:: 1084 1085 >>> u_subq = u.subquery() 1086 >>> stmt = ( 1087 ... select(u_subq.c.name, address_table.c.email_address). 1088 ... join_from(address_table, u_subq). 1089 ... order_by(u_subq.c.name, address_table.c.email_address) 1090 ... ) 1091 >>> with engine.connect() as conn: 1092 ... result = conn.execute(stmt) 1093 ... print(result.all()) 1094 {opensql}BEGIN (implicit) 1095 SELECT anon_1.name, address.email_address 1096 FROM address JOIN 1097 (SELECT user_account.id AS id, user_account.name AS name, user_account.fullname AS fullname 1098 FROM user_account 1099 WHERE user_account.name = ? 1100 UNION ALL 1101 SELECT user_account.id AS id, user_account.name AS name, user_account.fullname AS fullname 1102 FROM user_account 1103 WHERE user_account.name = ?) 1104 AS anon_1 ON anon_1.id = address.user_id 1105 ORDER BY anon_1.name, address.email_address 1106 [generated in ...] ('sandy', 'spongebob') 1107 {stop}[('sandy', 'sandy@sqlalchemy.org'), ('sandy', 'sandy@squirrelpower.org'), ('spongebob', 'spongebob@sqlalchemy.org')] 1108 {opensql}ROLLBACK{stop} 1109 1110.. _tutorial_orm_union: 1111 1112Selecting ORM Entities from Unions 1113~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ 1114 1115The preceding examples illustrated how to construct a UNION given two 1116:class:`_schema.Table` objects, to then return database rows. If we wanted 1117to use a UNION or other set operation to select rows that we then receive 1118as ORM objects, there are two approaches that may be used. In both cases, 1119we first construct a :func:`_sql.select` or :class:`_sql.CompoundSelect` 1120object that represents the SELECT / UNION / etc statement we want to 1121execute; this statement should be composed against the target 1122ORM entities or their underlying mapped :class:`_schema.Table` objects:: 1123 1124 >>> stmt1 = select(User).where(User.name == 'sandy') 1125 >>> stmt2 = select(User).where(User.name == 'spongebob') 1126 >>> u = union_all(stmt1, stmt2) 1127 1128For a simple SELECT with UNION that is not already nested inside of a 1129subquery, these 1130can often be used in an ORM object fetching context by using the 1131:meth:`_sql.Select.from_statement` method. With this approach, the UNION 1132statement represents the entire query; no additional 1133criteria can be added after :meth:`_sql.Select.from_statement` is used:: 1134 1135 >>> orm_stmt = select(User).from_statement(u) 1136 >>> with Session(engine) as session: 1137 ... for obj in session.execute(orm_stmt).scalars(): 1138 ... print(obj) 1139 {opensql}BEGIN (implicit) 1140 SELECT user_account.id, user_account.name, user_account.fullname 1141 FROM user_account 1142 WHERE user_account.name = ? UNION ALL SELECT user_account.id, user_account.name, user_account.fullname 1143 FROM user_account 1144 WHERE user_account.name = ? 1145 [generated in ...] ('sandy', 'spongebob') 1146 {stop}User(id=2, name='sandy', fullname='Sandy Cheeks') 1147 User(id=1, name='spongebob', fullname='Spongebob Squarepants') 1148 {opensql}ROLLBACK{stop} 1149 1150To use a UNION or other set-related construct as an entity-related component in 1151in a more flexible manner, the :class:`_sql.CompoundSelect` construct may be 1152organized into a subquery using :meth:`_sql.CompoundSelect.subquery`, which 1153then links to ORM objects using the :func:`_orm.aliased` function. This works 1154in the same way introduced at :ref:`tutorial_subqueries_orm_aliased`, to first 1155create an ad-hoc "mapping" of our desired entity to the subquery, then 1156selecting from that that new entity as though it were any other mapped class. 1157In the example below, we are able to add additional criteria such as ORDER BY 1158outside of the UNION itself, as we can filter or order by the columns exported 1159by the subquery:: 1160 1161 >>> user_alias = aliased(User, u.subquery()) 1162 >>> orm_stmt = select(user_alias).order_by(user_alias.id) 1163 >>> with Session(engine) as session: 1164 ... for obj in session.execute(orm_stmt).scalars(): 1165 ... print(obj) 1166 {opensql}BEGIN (implicit) 1167 SELECT anon_1.id, anon_1.name, anon_1.fullname 1168 FROM (SELECT user_account.id AS id, user_account.name AS name, user_account.fullname AS fullname 1169 FROM user_account 1170 WHERE user_account.name = ? UNION ALL SELECT user_account.id AS id, user_account.name AS name, user_account.fullname AS fullname 1171 FROM user_account 1172 WHERE user_account.name = ?) AS anon_1 ORDER BY anon_1.id 1173 [generated in ...] ('sandy', 'spongebob') 1174 {stop}User(id=1, name='spongebob', fullname='Spongebob Squarepants') 1175 User(id=2, name='sandy', fullname='Sandy Cheeks') 1176 {opensql}ROLLBACK{stop} 1177 1178.. seealso:: 1179 1180 :ref:`orm_queryguide_unions` - in the :ref:`queryguide_toplevel` 1181 1182.. _tutorial_exists: 1183 1184EXISTS subqueries 1185^^^^^^^^^^^^^^^^^^ 1186 1187The SQL EXISTS keyword is an operator that is used with :ref:`scalar subqueries 1188<tutorial_scalar_subquery>` to return a boolean true or false depending on if 1189the SELECT statement would return a row. SQLAlchemy includes a variant of the 1190:class:`_sql.ScalarSelect` object called :class:`_sql.Exists`, which will 1191generate an EXISTS subquery and is most conveniently generated using the 1192:meth:`_sql.SelectBase.exists` method. Below we produce an EXISTS so that we 1193can return ``user_account`` rows that have more than one related row in 1194``address``: 1195 1196.. sourcecode:: pycon+sql 1197 1198 >>> subq = ( 1199 ... select(func.count(address_table.c.id)). 1200 ... where(user_table.c.id == address_table.c.user_id). 1201 ... group_by(address_table.c.user_id). 1202 ... having(func.count(address_table.c.id) > 1) 1203 ... ).exists() 1204 >>> with engine.connect() as conn: 1205 ... result = conn.execute( 1206 ... select(user_table.c.name).where(subq) 1207 ... ) 1208 ... print(result.all()) 1209 {opensql}BEGIN (implicit) 1210 SELECT user_account.name 1211 FROM user_account 1212 WHERE EXISTS (SELECT count(address.id) AS count_1 1213 FROM address 1214 WHERE user_account.id = address.user_id GROUP BY address.user_id 1215 HAVING count(address.id) > ?) 1216 [...] (1,){stop} 1217 [('sandy',)] 1218 {opensql}ROLLBACK{stop} 1219 1220The EXISTS construct is more often than not used as a negation, e.g. NOT EXISTS, 1221as it provides a SQL-efficient form of locating rows for which a related 1222table has no rows. Below we select user names that have no email addresses; 1223note the binary negation operator (``~``) used inside the second WHERE 1224clause: 1225 1226.. sourcecode:: pycon+sql 1227 1228 >>> subq = ( 1229 ... select(address_table.c.id). 1230 ... where(user_table.c.id == address_table.c.user_id) 1231 ... ).exists() 1232 >>> with engine.connect() as conn: 1233 ... result = conn.execute( 1234 ... select(user_table.c.name).where(~subq) 1235 ... ) 1236 ... print(result.all()) 1237 {opensql}BEGIN (implicit) 1238 SELECT user_account.name 1239 FROM user_account 1240 WHERE NOT (EXISTS (SELECT address.id 1241 FROM address 1242 WHERE user_account.id = address.user_id)) 1243 [...] (){stop} 1244 [('patrick',)] 1245 {opensql}ROLLBACK{stop} 1246 1247.. _tutorial_functions: 1248 1249Working with SQL Functions 1250^^^^^^^^^^^^^^^^^^^^^^^^^^ 1251 1252First introduced earlier in this section at 1253:ref:`tutorial_group_by_w_aggregates`, the :data:`_sql.func` object serves as a 1254factory for creating new :class:`_functions.Function` objects, which when used 1255in a construct like :func:`_sql.select`, produce a SQL function display, 1256typically consisting of a name, some parenthesis (although not always), and 1257possibly some arguments. Examples of typical SQL functions include: 1258 1259* the ``count()`` function, an aggregate function which counts how many 1260 rows are returned: 1261 1262 .. sourcecode:: pycon+sql 1263 1264 >>> print(select(func.count()).select_from(user_table)) 1265 SELECT count(*) AS count_1 1266 FROM user_account 1267 1268 .. 1269 1270* the ``lower()`` function, a string function that converts a string to lower 1271 case: 1272 1273 .. sourcecode:: pycon+sql 1274 1275 >>> print(select(func.lower("A String With Much UPPERCASE"))) 1276 SELECT lower(:lower_2) AS lower_1 1277 1278 .. 1279 1280* the ``now()`` function, which provides for the current date and time; as this 1281 is a common function, SQLAlchemy knows how to render this differently for each 1282 backend, in the case of SQLite using the CURRENT_TIMESTAMP function: 1283 1284 .. sourcecode:: pycon+sql 1285 1286 >>> stmt = select(func.now()) 1287 >>> with engine.connect() as conn: 1288 ... result = conn.execute(stmt) 1289 ... print(result.all()) 1290 {opensql}BEGIN (implicit) 1291 SELECT CURRENT_TIMESTAMP AS now_1 1292 [...] () 1293 [(datetime.datetime(...),)] 1294 ROLLBACK 1295 1296 .. 1297 1298As most database backends feature dozens if not hundreds of different SQL 1299functions, :data:`_sql.func` tries to be as liberal as possible in what it 1300accepts. Any name that is accessed from this namespace is automatically 1301considered to be a SQL function that will render in a generic way:: 1302 1303 >>> print(select(func.some_crazy_function(user_table.c.name, 17))) 1304 SELECT some_crazy_function(user_account.name, :some_crazy_function_2) AS some_crazy_function_1 1305 FROM user_account 1306 1307At the same time, a relatively small set of extremely common SQL functions such 1308as :class:`_functions.count`, :class:`_functions.now`, :class:`_functions.max`, 1309:class:`_functions.concat` include pre-packaged versions of themselves which 1310provide for proper typing information as well as backend-specific SQL 1311generation in some cases. The example below contrasts the SQL generation 1312that occurs for the PostgreSQL dialect compared to the Oracle dialect for 1313the :class:`_functions.now` function:: 1314 1315 >>> from sqlalchemy.dialects import postgresql 1316 >>> print(select(func.now()).compile(dialect=postgresql.dialect())) 1317 SELECT now() AS now_1 1318 1319 >>> from sqlalchemy.dialects import oracle 1320 >>> print(select(func.now()).compile(dialect=oracle.dialect())) 1321 SELECT CURRENT_TIMESTAMP AS now_1 FROM DUAL 1322 1323Functions Have Return Types 1324~~~~~~~~~~~~~~~~~~~~~~~~~~~ 1325 1326As functions are column expressions, they also have 1327SQL :ref:`datatypes <types_toplevel>` that describe the data type of 1328a generated SQL expression. We refer to these types here as "SQL return types", 1329in reference to the type of SQL value that is returned by the function 1330in the context of a database-side SQL expression, 1331as opposed to the "return type" of a Python function. 1332 1333The SQL return type of any SQL function may be accessed, typically for 1334debugging purposes, by referring to the :attr:`_functions.Function.type` 1335attribute:: 1336 1337 >>> func.now().type 1338 DateTime() 1339 1340These SQL return types are significant when making 1341use of the function expression in the context of a larger expression; that is, 1342math operators will work better when the datatype of the expression is 1343something like :class:`_types.Integer` or :class:`_types.Numeric`, JSON 1344accessors in order to work need to be using a type such as 1345:class:`_types.JSON`. Certain classes of functions return entire rows 1346instead of column values, where there is a need to refer to specific columns; 1347such functions are referred towards 1348as :ref:`table valued functions <tutorial_functions_table_valued>`. 1349 1350The SQL return type of the function may also be significant when executing a 1351statement and getting rows back, for those cases where SQLAlchemy has to apply 1352result-set processing. A prime example of this are date-related functions on 1353SQLite, where SQLAlchemy's :class:`_types.DateTime` and related datatypes take 1354on the role of converting from string values to Python ``datetime()`` objects 1355as result rows are received. 1356 1357To apply a specific type to a function we're creating, we pass it using the 1358:paramref:`_functions.Function.type_` parameter; the type argument may be 1359either a :class:`_types.TypeEngine` class or an instance. In the example 1360below we pass the :class:`_types.JSON` class to generate the PostgreSQL 1361``json_object()`` function, noting that the SQL return type will be of 1362type JSON:: 1363 1364 >>> from sqlalchemy import JSON 1365 >>> function_expr = func.json_object('{a, 1, b, "def", c, 3.5}', type_=JSON) 1366 1367By creating our JSON function with the :class:`_types.JSON` datatype, the 1368SQL expression object takes on JSON-related features, such as that of accessing 1369elements:: 1370 1371 >>> stmt = select(function_expr["def"]) 1372 >>> print(stmt) 1373 SELECT json_object(:json_object_1)[:json_object_2] AS anon_1 1374 1375Built-in Functions Have Pre-Configured Return Types 1376~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ 1377 1378For common aggregate functions like :class:`_functions.count`, 1379:class:`_functions.max`, :class:`_functions.min` as well as a very small number 1380of date functions like :class:`_functions.now` and string functions like 1381:class:`_functions.concat`, the SQL return type is set up appropriately, 1382sometimes based on usage. The :class:`_functions.max` function and similar 1383aggregate filtering functions will set up the SQL return type based on the 1384argument given:: 1385 1386 >>> m1 = func.max(Column("some_int", Integer)) 1387 >>> m1.type 1388 Integer() 1389 1390 >>> m2 = func.max(Column("some_str", String)) 1391 >>> m2.type 1392 String() 1393 1394Date and time functions typically correspond to SQL expressions described by 1395:class:`_types.DateTime`, :class:`_types.Date` or :class:`_types.Time`:: 1396 1397 >>> func.now().type 1398 DateTime() 1399 >>> func.current_date().type 1400 Date() 1401 1402A known string function such as :class:`_functions.concat` 1403will know that a SQL expression would be of type :class:`_types.String`:: 1404 1405 >>> func.concat("x", "y").type 1406 String() 1407 1408However, for the vast majority of SQL functions, SQLAlchemy does not have them 1409explicitly present in its very small list of known functions. For example, 1410while there is typically no issue using SQL functions ``func.lower()`` 1411and ``func.upper()`` to convert the casing of strings, SQLAlchemy doesn't 1412actually know about these functions, so they have a "null" SQL return type:: 1413 1414 >>> func.upper("lowercase").type 1415 NullType() 1416 1417For simple functions like ``upper`` and ``lower``, the issue is not usually 1418significant, as string values may be received from the database without any 1419special type handling on the SQLAlchemy side, and SQLAlchemy's type 1420coercion rules can often correctly guess intent as well; the Python ``+`` 1421operator for example will be correctly interpreted as the string concatenation 1422operator based on looking at both sides of the expression:: 1423 1424 >>> print(select(func.upper("lowercase") + " suffix")) 1425 SELECT upper(:upper_1) || :upper_2 AS anon_1 1426 1427Overall, the scenario where the 1428:paramref:`_functions.Function.type_` parameter is likely necessary is: 1429 14301. the function is not already a SQLAlchemy built-in function; this can be 1431 evidenced by creating the function and observing the :attr:`_functions.Function.type` 1432 attribute, that is:: 1433 1434 >>> func.count().type 1435 Integer() 1436 1437 .. 1438 1439 vs.:: 1440 1441 >>> func.json_object('{"a", "b"}').type 1442 NullType() 1443 14442. Function-aware expression support is needed; this most typically refers to 1445 special operators related to datatypes such as :class:`_types.JSON` or 1446 :class:`_types.ARRAY` 1447 14483. Result value processing is needed, which may include types such as 1449 :class:`_functions.DateTime`, :class:`_types.Boolean`, :class:`_types.Enum`, 1450 or again special datatypes such as :class:`_types.JSON`, 1451 :class:`_types.ARRAY`. 1452 1453.. _tutorial_window_functions: 1454 1455Using Window Functions 1456~~~~~~~~~~~~~~~~~~~~~~ 1457 1458A window function is a special use of a SQL aggregate function which calculates 1459the aggregate value over the rows being returned in a group as the individual 1460result rows are processed. Whereas a function like ``MAX()`` will give you 1461the highest value of a column within a set of rows, using the same function 1462as a "window function" will given you the highest value for each row, 1463*as of that row*. 1464 1465In SQL, window functions allow one to specify the rows over which the 1466function should be applied, a "partition" value which considers the window 1467over different sub-sets of rows, and an "order by" expression which importantly 1468indicates the order in which rows should be applied to the aggregate function. 1469 1470In SQLAlchemy, all SQL functions generated by the :data:`_sql.func` namespace 1471include a method :meth:`_functions.FunctionElement.over` which 1472grants the window function, or "OVER", syntax; the construct produced 1473is the :class:`_sql.Over` construct. 1474 1475A common function used with window functions is the ``row_number()`` function 1476which simply counts rows. We may partition this row count against user name to 1477number the email addresses of individual users: 1478 1479.. sourcecode:: pycon+sql 1480 1481 >>> stmt = select( 1482 ... func.row_number().over(partition_by=user_table.c.name), 1483 ... user_table.c.name, 1484 ... address_table.c.email_address 1485 ... ).select_from(user_table).join(address_table) 1486 >>> with engine.connect() as conn: # doctest:+SKIP 1487 ... result = conn.execute(stmt) 1488 ... print(result.all()) 1489 {opensql}BEGIN (implicit) 1490 SELECT row_number() OVER (PARTITION BY user_account.name) AS anon_1, 1491 user_account.name, address.email_address 1492 FROM user_account JOIN address ON user_account.id = address.user_id 1493 [...] () 1494 [(1, 'sandy', 'sandy@sqlalchemy.org'), (2, 'sandy', 'sandy@squirrelpower.org'), (1, 'spongebob', 'spongebob@sqlalchemy.org')] 1495 ROLLBACK 1496 1497Above, the :paramref:`_functions.FunctionElement.over.partition_by` parameter 1498is used so that the ``PARTITION BY`` clause is rendered within the OVER clause. 1499We also may make use of the ``ORDER BY`` clause using :paramref:`_functions.FunctionElement.over.order_by`: 1500 1501.. sourcecode:: pycon+sql 1502 1503 >>> stmt = select( 1504 ... func.count().over(order_by=user_table.c.name), 1505 ... user_table.c.name, 1506 ... address_table.c.email_address).select_from(user_table).join(address_table) 1507 >>> with engine.connect() as conn: # doctest:+SKIP 1508 ... result = conn.execute(stmt) 1509 ... print(result.all()) 1510 {opensql}BEGIN (implicit) 1511 SELECT count(*) OVER (ORDER BY user_account.name) AS anon_1, 1512 user_account.name, address.email_address 1513 FROM user_account JOIN address ON user_account.id = address.user_id 1514 [...] () 1515 [(2, 'sandy', 'sandy@sqlalchemy.org'), (2, 'sandy', 'sandy@squirrelpower.org'), (3, 'spongebob', 'spongebob@sqlalchemy.org')] 1516 ROLLBACK 1517 1518Further options for window functions include usage of ranges; see 1519:func:`_expression.over` for more examples. 1520 1521.. tip:: 1522 1523 It's important to note that the :meth:`_functions.FunctionElement.over` 1524 method only applies to those SQL functions which are in fact aggregate 1525 functions; while the :class:`_sql.Over` construct will happily render itself 1526 for any SQL function given, the database will reject the expression if the 1527 function itself is not a SQL aggregate function. 1528 1529.. _tutorial_functions_within_group: 1530 1531Special Modifiers WITHIN GROUP, FILTER 1532~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ 1533 1534The "WITHIN GROUP" SQL syntax is used in conjunction with an "ordered set" 1535or a "hypothetical set" aggregate 1536function. Common "ordered set" functions include ``percentile_cont()`` 1537and ``rank()``. SQLAlchemy includes built in implementations 1538:class:`_functions.rank`, :class:`_functions.dense_rank`, 1539:class:`_functions.mode`, :class:`_functions.percentile_cont` and 1540:class:`_functions.percentile_disc` which include a :meth:`_functions.FunctionElement.within_group` 1541method:: 1542 1543 >>> print( 1544 ... func.unnest( 1545 ... func.percentile_disc([0.25,0.5,0.75,1]).within_group(user_table.c.name) 1546 ... ) 1547 ... ) 1548 unnest(percentile_disc(:percentile_disc_1) WITHIN GROUP (ORDER BY user_account.name)) 1549 1550"FILTER" is supported by some backends to limit the range of an aggregate function to a 1551particular subset of rows compared to the total range of rows returned, available 1552using the :meth:`_functions.FunctionElement.filter` method:: 1553 1554 >>> stmt = select( 1555 ... func.count(address_table.c.email_address).filter(user_table.c.name == 'sandy'), 1556 ... func.count(address_table.c.email_address).filter(user_table.c.name == 'spongebob') 1557 ... ).select_from(user_table).join(address_table) 1558 >>> with engine.connect() as conn: # doctest:+SKIP 1559 ... result = conn.execute(stmt) 1560 ... print(result.all()) 1561 {opensql}BEGIN (implicit) 1562 SELECT count(address.email_address) FILTER (WHERE user_account.name = ?) AS anon_1, 1563 count(address.email_address) FILTER (WHERE user_account.name = ?) AS anon_2 1564 FROM user_account JOIN address ON user_account.id = address.user_id 1565 [...] ('sandy', 'spongebob') 1566 [(2, 1)] 1567 ROLLBACK 1568 1569.. _tutorial_functions_table_valued: 1570 1571Table-Valued Functions 1572~~~~~~~~~~~~~~~~~~~~~~~~~ 1573 1574Table-valued SQL functions support a scalar representation that contains named 1575sub-elements. Often used for JSON and ARRAY-oriented functions as well as 1576functions like ``generate_series()``, the table-valued function is specified in 1577the FROM clause, and is then referred towards as a table, or sometimes even as 1578a column. Functions of this form are prominent within the PostgreSQL database, 1579however some forms of table valued functions are also supported by SQLite, 1580Oracle, and SQL Server. 1581 1582.. seealso:: 1583 1584 :ref:`postgresql_table_valued_overview` - in the :ref:`postgresql_toplevel` documentation. 1585 1586 While many databases support table valued and other special 1587 forms, PostgreSQL tends to be where there is the most demand for these 1588 features. See this section for additional examples of PostgreSQL 1589 syntaxes as well as additional features. 1590 1591SQLAlchemy provides the :meth:`_functions.FunctionElement.table_valued` method 1592as the basic "table valued function" construct, which will convert a 1593:data:`_sql.func` object into a FROM clause containing a series of named 1594columns, based on string names passed positionally. This returns a 1595:class:`_sql.TableValuedAlias` object, which is a function-enabled 1596:class:`_sql.Alias` construct that may be used as any other FROM clause as 1597introduced at :ref:`tutorial_using_aliases`. Below we illustrate the 1598``json_each()`` function, which while common on PostgreSQL is also supported by 1599modern versions of SQLite:: 1600 1601 >>> onetwothree = func.json_each('["one", "two", "three"]').table_valued("value") 1602 >>> stmt = select(onetwothree).where(onetwothree.c.value.in_(["two", "three"])) 1603 >>> with engine.connect() as conn: # doctest:+SKIP 1604 ... result = conn.execute(stmt) 1605 ... print(result.all()) 1606 {opensql}BEGIN (implicit) 1607 SELECT anon_1.value 1608 FROM json_each(?) AS anon_1 1609 WHERE anon_1.value IN (?, ?) 1610 [...] ('["one", "two", "three"]', 'two', 'three') 1611 [('two',), ('three',)] 1612 ROLLBACK 1613 1614Above, we used the ``json_each()`` JSON function supported by SQLite and 1615PostgreSQL to generate a table valued expression with a single column referred 1616towards as ``value``, and then selected two of its three rows. 1617 1618.. seealso:: 1619 1620 :ref:`postgresql_table_valued` - in the :ref:`postgresql_toplevel` documentation - 1621 this section will detail additional syntaxes such as special column derivations 1622 and "WITH ORDINALITY" that are known to work with PostgreSQL. 1623 1624.. _tutorial_functions_column_valued: 1625 1626Column Valued Functions - Table Valued Function as a Scalar Column 1627~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ 1628 1629A special syntax supported by PostgreSQL and Oracle is that of referring 1630towards a function in the FROM clause, which then delivers itself as a 1631single column in the columns clause of a SELECT statement or other column 1632expression context. PostgreSQL makes great use of this syntax for such 1633functions as ``json_array_elements()``, ``json_object_keys()``, 1634``json_each_text()``, ``json_each()``, etc. 1635 1636SQLAlchemy refers to this as a "column valued" function and is available 1637by applying the :meth:`_functions.FunctionElement.column_valued` modifier 1638to a :class:`_functions.Function` construct:: 1639 1640 >>> from sqlalchemy import select, func 1641 >>> stmt = select(func.json_array_elements('["one", "two"]').column_valued("x")) 1642 >>> print(stmt) 1643 SELECT x 1644 FROM json_array_elements(:json_array_elements_1) AS x 1645 1646The "column valued" form is also supported by the Oracle dialect, where 1647it is usable for custom SQL functions:: 1648 1649 >>> from sqlalchemy.dialects import oracle 1650 >>> stmt = select(func.scalar_strings(5).column_valued("s")) 1651 >>> print(stmt.compile(dialect=oracle.dialect())) 1652 SELECT COLUMN_VALUE s 1653 FROM TABLE (scalar_strings(:scalar_strings_1)) s 1654 1655 1656.. seealso:: 1657 1658 :ref:`postgresql_column_valued` - in the :ref:`postgresql_toplevel` documentation. 1659 1660 1661