Subqueries in the FROM clause🔥

How to use subqueries in the FROM clause 👀

Jun 13, 2025

Got BDE?⚡️

Hi BDEs! ⚡️

Hope you enjoyed yesterday’s newsletter on subqueries and how to use them in the WHERE clause… today we’re learning how to use them in the FROM clause!

If you need something to do this weekend, and don’t wanna touch grass… here’s $30 off my full beginner SQL course that will teach you all the basics you need to start building projects for a portfolio 🎉

($30 off code SUBSTACK expires Monday) ⬇️ 💰

i'm ready to level up

Or if you wanna try out a few lessons, try my free SQL intro course for complete newbies ⬇️

Free Intro to SQL course 🔥

⚡️If you’re new here:

💁🏽‍♀️ Who Am I?

I’m Jess Ramos, the founder of Big Data Energy and the creator of the BEST SQL course and community: Big SQL Energy⚡️. Check me out on socials: 🔗LinkedIn, 🔗Instagram, 🔗YouTube, and 🔗TikTok. And of course subscribe to my 🔗newsletter here for all my upcoming lessons and updates— all for free!

⚡️ Social Highlights:

jessramosdata

A post shared by @jessramosdata

Subqueries in the FROM clause

Subqueries can be used in the FROM clause if you need to calculate something first and then pull from that result. For example, since you can’t nest aggregate functions in SQL, you have to calculate them in 2 separate SELECT statements—using either a CTE or subquery for the first calculation and then the final select statement to nest the second calculation around the first one. Here’s an example with a subquery where we’ll find the maximum average temperature per month:

select 
   max(avg_temp) as max_avg_temp
from
   (select 
      month, 
      avg(temperature) avg_temp
   from 
      temperature_log 
   group by 1
   )

First, the average temperature per month is calculated in the inner (or sub) query. Then the max of the avg_temp column is taken to calculate the max of the monthly averages. Remember we can’t do nested aggreate functions like max(avg(temperature)) in SQL! We have to use a subquery… or CTE! See the same query written with a CTE below:

with avgs as(
select 
      month, 
      avg(temperature) avg_temp
   from 
      temperature_log 
   group by 1
)
select 
   max(avg_temp) as max_avg_temp
from
   avgs

Either method is fine, but I’d recommend a CTE if the query gets any longer or more complex because it’s easier to understand. In my opinion, this use case of subqueries (in the FROM clause) looks the most similar to CTEs— just inside out. So this may be a good place to start if you want to learn about them.

You can also JOIN subqueries as well like this:

select 
   month,
   temperature,
   monthly_avg_temp
from
   temperature_log log
left join 
   (select 
      month, 
      avg(temperature) monthly_avg_temp
   from 
      temperature_log 
   group by 1
   ) as monthly_avg
on 
   log.month = monthly_avg.month

Personally, I’d start to use a CTE here though instead of a subquery because it’s beginning to get a little complex to unravel, especially if you pass the code off to someone else to review. There’s no harm in using a CTE to have more legible code! If you can’t tell, I’m Team CTE most of the time🤩