The SELECT statement logic in the database performs sequential analysis

The introduction

It's not a deep technical problem, it's great programming. This is not necessarily directly related to a person's ability to develop, but knowing this can be a great help in writing, troubleshooting, and optimizing SQL. It's not a complicated piece of knowledge, but it's a very basic SQL foundation. If you don't know this, you just build your house out of plain cement. Master these, you are building a house of high cement.

However, here's a simple tip to research your colleagues and friends, and you may be surprised at what you find.

Since this article was written out of the blue, the SQL statements that follow are not tested.

Look at the SQL statements below:

#1

SELECT ID,COUNT(ID) AS TOTAL

 

FROM STUDENT

 

GROUP BY ID

 

HAVING TOTAL>2

#2

SELECT ID,COUNT(ID) AS TOTAL

 

FROM STUDENT

 

GROUP BY ID

 

ORDER BY TOTAL

#3

SELECT FIRSTNAME+' '+LASTNAME AS NAME, COUNT(*) AS COUNT

 

FROM STUDENT

 

GROUP BY NAME

Which one do you think will not be implemented successfully?

Here is the logical order of execution of the SELECT statement:

1.FROM
2.ON
3.JOIN
4.WHERE
5.GROUP BY
6.WITH CUBE or WITH ROLLUP
7.HAVING
8.SELECT
9.DISTINCT
10.ORDER BY
11.TOP
MICROSOFT notes that the actual physical execution order of SELECT statements may differ from this order depending on the query processor.

A few examples

Example 1:

SELECT ID,COUNT(ID) AS TOTAL

 

FROM STUDENT

 

GROUP BY ID

 

HAVING TOTAL>2

Does this SQL statement look familiar? Yes, very basic grouping queries. But it does not execute successfully because HAVING executes in order above SELECT.

The actual execution sequence is as follows:

1.FROM STUDENT
2.GROUP BY ID
3.HAVING TOTAL > 2
4.SELECT ID,COUNT(ID) AS TOTAL
Obviously, TOTAL is the new alias generated after the execution of the last sentence SELECT ID,COUNT(ID) TOTAL. So in HAVING TOTAL > TOTAL is not recognized during execution.

Example 2

SELECT ID,COUNT(ID) AS TOTAL

 

FROM STUDENT

 

GROUP BY ID

 

ORDER BY TOTAL

The actual execution order of this is:

1.FROM STUDENT
2.GROUP BY ID
3.SELECT ID,COUNT(ID) AS TOTAL
4.ORDER BY TOTAL
This time there were no problems and it was successfully executed. What if I replace ORDER BY TOTAL with ORDER BY COUNT(ID)?

SELECT ID,COUNT(ID) AS TOTAL

 

FROM STUDENT

 

GROUP BY ID

 

ORDER BY COUNT(ID)

Actual execution sequence:

1.FROM STUDENT
2.GROUP BY ID
3.SELECT ID,COUNT(ID) AS TOTAL
4.ORDER BY COUNT(ID)

Yes, it can be executed successfully. Look at the SQL execution plan, which is the same as ORDER BY TOTAL above. ORDER BY is executed after SELECT, so the alias TOTAL can be used.

Example 3

SELECT FIRSTNAME+' '+LASTNAME AS NAME, COUNT(*) AS COUNT

 

FROM STUDENT

 

GROUP BY NAME

Actual execution sequence:

FROM STUDENT

 

GROUP BY NAME

 

SELECT FIRSTNAME+' '+LASTNAME AS NAME,COUNT(*) AS COUNT

Obviously, the alias NAME was not created when GROUP BY NAME was executed, so it cannot be executed successfully.

conclusion

Recall that we once casually asked some people this question, no matter who said they did not know, we would deliberately laugh at 1, of course, this laugh is not the other laugh. But as it turns out, there are a few people who just don't notice and post it here as a friendly reminder.