Confused about the order of execution SQL

Question

I have this SQL query can anyone help me with the order of execution like which clause is run first and then last:

SELECT COUNT(*),
       (SELECT COUNT(*)
          FROM recent_grads
       )
  FROM recent_grads
 WHERE ShareWomen > (SELECT AVG(ShareWomen)
                       FROM recent_grads
                    );

1. I know the "FROM" clause in line number 5 is run first because it tells our query that the table or dataset we will be using is called "recent_grads".

2. Then I think the "WHERE" clause is run second and in the WHERE clause, the subquery is run first. When the subquery is run then the subquery is replaced by a certain value. Then we have a proper filter statement.

3. I think the third clause that is run is the "SELECT" clauses from the first line and in that clause, the statement " SELECT COUNT()" is run first which returns the number of filtered rows. Then the subquery is run which returns the number of rows in the dataset without filter, so the original amount of rows.

Can someone please let me know if my order of execution is right, I am not sure about the third one when the "SELECT" clause is called, I wanna know if the subquery in the SELECT clause gets executed first or the COUNT() function itself gets executed first.

Answer 1

(I am saying almost the same as what you say, but I am speaking more generally about the execution of a SELECT.)

Without any subqueries, the clauses of a SELECT are required to be in the order that they will be looked at. (And you cannot shuffle the order.) The exception is that the columns in the SELECT are not used until at least after the FROM.

A subquery can be "correlated", meaning it references columns in the outer query, or "uncorrelated", as with both of your subqueries. For "correlated", the subquery must be run repeatedly.

The existence of an "Aggregate" (the first COUNT(*)) without a GROUP BY, says to "go through the entire table (FROM) to tally up the value.

A subquery that is a "constant" (both of your subqueries) will be treated as a separate SELECT to be performed before the main SELECT. (It is indeterminate as to which of the 2 comes first -- as it should not matter.) After that, you have something like:

SELECT COUNT(*),
       1234
    FROM recent_grads
    WHERE ShareWomen > 25.678;

Does that agree with what you are seeing?