Subquery Order of Execution

Screen Link:

My Code:

SELECT CAST(COUNT(*) AS float)/CAST((SELECT COUNT(*) FROM recent_grads) AS float) AS proportion_abv_avg
FROM recent_grads
WHERE ShareWomen > (SELECT AVG(ShareWomen) FROM recent_grads)

What I expected to happen:
This works as expected. What I want to know is what is the order the subqueries execute? Does the ‘WHERE’ subquery execute before the ‘SELECT’ subquery?

What actually happened:

Replace this line with the output/error

Hi @ryan.pikulski,

According to this mission screen, the order in which the clauses run is the following:

  1. FROM
  2. WHERE
  3. GROUP BY
  4. HAVING
  5. SELECT
  6. ORDER BY
  7. LIMIT

Hey Elena,

Thank you for the response! (I was actually looking around for this very list). My understanding is that the Subquery is executed first and then the main query is executed. Does this mean that the Subqueries execute in the same order as the list you provided?

Yes, the same order also for each individual subquery.

According to these takeaways, a subquery can appear in SELECT, FROM, or WHERE clauses. Keeping the overall order of clauses as from my previous message, as soon as a subquery appears at one of these 3 clauses, we apply to each of them the same order of clauses, and then continue with the main query.

Don’t forget about JOIN

  • FROM
  • JOIN
  • WHERE
  • GROUP BY
  • HAVING
  • SELECT
  • ORDER BY
  • LIMIT / OFFSET
2 Likes

@alvinctk, yes, you’re right, thank you! JOIN and ON have priority.