Skip to content
Tutorials Center
My learning

Topics

Lesson 14 of 37 ยท Core and Expressions

Joins and Aggregates

Combine tables with join() and outerjoin(), summarize with func.count, sum and avg, group with group_by and having, and alias tables for self joins.

Relational databases split data across tables, and queries put it back together. A join connects rows from two tables through matching columns. An aggregate collapses many rows into one number: a count, a sum, an average. Most real reports use both.

In SQLAlchemy you write joins with join() and aggregates with the func namespace, and both produce plain select() statements you already know how to run. The examples use a tiny shop: customer, purchase (orders) and product.

Joining tables

select(...).join(target) joins to the target table. If there is exactly one foreign key between the tables, SQLAlchemy works out the ON clause by itself. Otherwise pass it explicitly as the second argument.

Python join() and outerjoin()

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.

An inner join keeps only matching rows, so Cy (no purchases) disappears. outerjoin() (a left outer join) keeps every customer and fills the missing side with None.

When your select() lists columns from several tables, SQLAlchemy has to decide which table to start FROM. If it guesses wrong, use select_from() or join_from():

Python
select(purchase.c.item, customer.c.name).join_from(purchase, customer)
select(purchase.c.item).select_from(purchase).join(customer)

Aggregate functions

SQL functions live in the func namespace: func.count(), func.sum(), func.avg(), func.min(), func.max(). Any attribute of func becomes a SQL function of that name, so functions SQLAlchemy has never heard of work too.

Python count, sum, avg, min, max

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.

func.count() with no argument counts rows (count(*)). col.distinct() inside an aggregate gives COUNT(DISTINCT region). Always label aggregates, so the result has a readable name.

GROUP BY and HAVING

group_by() makes one result row per distinct group, and aggregates then work per group. having() filters groups after aggregating, in the same way where() filters rows before.

Python group_by() and having()

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 two filters answer different questions. where(s.amount > 50) drops small orders before grouping, so east disappears completely. having(sum > 100) keeps only groups whose total is above 100.

flowchart LR
    A["FROM + JOIN"] --> B["WHERE<br/>(filter rows)"]
    B --> C["GROUP BY<br/>(make groups)"]
    C --> D["HAVING<br/>(filter groups)"]
    D --> E["SELECT<br/>(aggregates, labels)"]
    E --> F["ORDER BY"]
    F --> G["LIMIT / OFFSET"]

The logical order in which a database evaluates a query. It explains why you cannot use an aggregate in WHERE.

Joining and aggregating together

Combine both ideas to answer questions like "how much has each customer spent, including customers with no orders?" Use an outer join, and coalesce to turn the empty sum into zero.

Python Totals per customer

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.

Counting purchase.c.id instead of * matters: count(*) would count Cy's single empty row as 1, while count(column) ignores NULL values and gives 0.

Aliases and self joins

When a table joins to itself (an employee and the employee's manager), you need two names for the same table. table.alias("name") creates one. In the ORM you will use aliased().

Python Self join with alias()

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.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. Which join keeps customers that have no purchases?

    An outer (left) join keeps rows from the left table even when there is no match, filling the right side with NULL.
  2. 2. What is the difference between where() and having()?

    Aggregates are only available after grouping, so conditions on them belong in having().
  3. 3. Why use func.count(purchase.c.id) rather than func.count() after an outer join?

    count(*) counts the row of NULLs produced by the outer join as 1, but count(column) skips NULL.
  4. 4. How do you join a table to itself?

    An alias gives the same table a second name, which lets you refer to both sides of the join.

Summary

  • select().join() and outerjoin() combine tables; SQLAlchemy infers ON from foreign keys or you pass it.
  • func.count/sum/avg/min/max aggregate; label them. group_by() groups and having() filters groups.
  • coalesce replaces NULL results, and alias() supports self joins.

Next: Subqueries, CTEs and SQL Functions