Home Subqueries 32. Subquery in From Clause

32. Subquery in From Clause

Subqueries in the from clause return full tables (no limit on the number of columns and rows) and are generally uncorrelated. Technically, there are ways to write correlated subqueries in a from clause (subqueries that depend on the outer query itself), but this is a very rare design pattern we will not be covering in this course.

When using a subquery in your from clause, you can think of it as creating a temporary table that can be treated like any other table in your query. You can join it, you can select from it, you can use its columns in your where, group by, order by, and having clauses, etc. This feature makes SQL enormously flexible, as you are not bound by the table structure of your database; you can create infinitely many tables from the underlying data structured however you'd like.

As an example, imagine that we need to generate a list of all gift amounts in 2021 and we also want the output to include the lifetime average gift amount for the donors who made the gifts. We can do this using a subquery in the from clause, like this:

select
    t.account_id
    ,t.amount
    ,ag.lifetime_average_gift
from transactions as t
    inner join (
            select
                account_id
                ,avg(amount) as lifetime_average_gift
            from transactions
            group by account_id
        ) as ag on t.account_id = ag.account_id
where year(t.transaction_date) = 2021

In this from clause, we are using an inner join to connect our transactions table to a subquery that we have aliased ag. That subquery computes the lifetime average gift amount for each donor, and the database treats this as a table itself, which allows us to include one of its columns (lifetime_average_gift) the select clause of our main query.

This is a very basic example, but hopefully it helps you start to understand the power of subqueries. Subqueries allow you to select, compute, and refine data in logical steps and pieces and then weave them into a single, cohesive query.

Exercise 1: from clause subquery

Development is about to send out tax letters for 2024. They need a list of all donors who gave at least one gift in 2024 that includes:

  • Donor ID
  • First Name
  • Last Name
  • Street Address
  • City
  • State
  • Zip Code
  • Largest 2024 gift
  • Cumulative 2024 amount

Write an SQL query using a subquery in the from clause to generate this listing.
No Results

Exercise 2: Advanced subquery fun

Development wants to identify transactions that are larger than the average gift amount in the donor's state of residence. Output should include:

  • Transaction ID
  • Transaction Date
  • Amount
  • Source Code
  • State
  • State Average Gift

Write an SQL query using a subquery in the from clause to generate this list.
No Results