Skip to content
Tutorials Center
My learning

Topics

Lesson 15 of 37 ยท Core and Expressions

Subqueries, CTEs and SQL Functions

Compose queries with scalar subqueries, EXISTS, subquery() and cte(), combine with union, compute with case(), cast and window functions like row_number().

Because statements in SQLAlchemy are objects, you can embed one inside another. That is how you build the SQL that does not fit a single flat SELECT: "employees who earn more than their department average", "customers that have at least one order", "the top three per group". This lesson covers the composition tools: scalar subqueries, EXISTS, subqueries in FROM, CTEs, set operations, CASE expressions and window functions.

The data is a small emp table with departments and salaries, recreated at the top of each program.

Scalar subqueries

A scalar subquery returns exactly one value and can be used anywhere an expression can. Turn a select into one with .scalar_subquery().

Python Compare each row to a group average

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

The second query is correlated: the inner select refers to e.dept from the outer query, so it is evaluated for every outer row. SQLAlchemy figures out the correlation automatically because emp appears in both, and the alias e2 keeps the two copies apart.

EXISTS

exists() answers a yes/no question and often beats a join when you only need to know whether related rows exist, because there is no row multiplication to undo.

Python exists() and not exists()

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

Subqueries in FROM

.subquery() turns a select into something you can use like a table. Its columns are on .c, exactly as on a Table.

Python subquery(): aggregate first, then join

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

CTEs: named steps

A common table expression (CTE) is a subquery with a name, written as WITH name AS (...). It reads better when a query has several steps, and a CTE can refer to itself for recursive queries. In SQLAlchemy, call .cte("name").

Python A CTE and a recursive CTE

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

Combining results: UNION and friends

union(), union_all(), intersect() and except_() stack results of selects with matching columns. union_all keeps duplicates and is faster; union removes them.

Python union and union_all

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

case(), cast() and window functions

case() is SQL's if/else. cast() converts types. Window functions compute per-row values over a group of rows without collapsing them: ranking, running totals, previous row. Call .over(partition_by=..., order_by=...) on any function.

Python case, cast and row_number().over()

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

Window results cannot be used in WHERE directly, so wrap the ranked select in a subquery and filter on its column, as the last query does. This "top N per group" recipe is one of the most useful in all of SQL.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. What does .scalar_subquery() make of a select()?

    A scalar subquery must return one row and one column, and can be used like any other expression.
  2. 2. When is exists() a good choice over a join?

    EXISTS tests existence without multiplying rows, so there is nothing to de-duplicate.
  3. 3. How do you write a recursive CTE in SQLAlchemy?

    Create the anchor with cte(recursive=True), then extend it with union_all(select(...)) that refers to the CTE.
  4. 4. Why wrap a ranked select in a subquery to find the top row per group?

    WHERE is evaluated before window functions, so the filter has to be applied in an outer query.

Summary

  • Statements compose: .scalar_subquery() for single values, exists() for existence, .subquery() for derived tables and .cte() for named (and recursive) steps.
  • union/union_all/intersect/except_ combine result sets.
  • case() and cast() build conditional values and conversions; func.x().over(...) adds window functions, and filtering them needs an outer query.

Next: Declarative Models with Mapped and mapped_column