Do you know the logical execution order of select statements?

　Introduction

　This is not a deep technical problem, but a great programming ability. This is not necessarily directly related to a person's development capabilities, but knowing these will be of great help to your SQL writing, troubleshooting and optimization. It is not a complicated knowledge point, but a very basic SQL foundation. If you don’t understand these things, you have been building houses with ordinary cement; if you understand these things, you are building houses with high-grade cement.

However, it is such a small knowledge point. You can investigate your colleagues and friends around you. Maybe you will get a "surprise".

Since this article was written suddenly, the SQL statement written below has not been tested.

Look at the following SQL statements:

SELECT ID,COUNT(ID) AS TOTAL
 
FROM STUDENT
 
GROUP BY ID
 
HAVING TOTAL>2

SELECT ID,COUNT(ID) AS TOTAL
 
FROM STUDENT
 
GROUP BY ID
 
ORDER BY TOTAL

SELECT FIRSTNAME+' '+LASTNAME AS NAME, COUNT(*) AS COUNT
 
FROM STUDENT
 
GROUP BY NAME

Which one do you think cannot be executed successfully?

Back to business

The following is the logical execution sequence of the SELECT statement:

FROM

ON

JOIN

WHERE

GROUP BY

WITH CUBE or WITH ROLLUP

HAVING

SELECT

DISTINCT

ORDER BY

TOP

　MICROSOFT pointed out that the actual physical execution order of the SELECT statement may differ from this order due to different query processors.

　A few examples

　Example 1

SELECT ID,COUNT(ID) AS TOTAL
 
FROM STUDENT
 
GROUP BY ID
 
HAVING TOTAL>2

Do you think this SQL statement looks familiar? Yes, very basic group query. But it cannot be executed successfully because the execution order of HAVING is above SELECT.

　The actual execution sequence is as follows:

FROM STUDENT

GROUP BY ID

HAVING TOTAL>2

SELECT ID,COUNT(ID) AS TOTAL

　Obviously, TOTAL is in the last sentence SELECT ID,COUNT(ID) A new alias generated after AS TOTAL is executed. Therefore, TOTAL cannot be recognized when HAVING TOTAL>2 is executed.

　Example 2

SELECT ID,COUNT(ID) AS TOTAL
 
FROM STUDENT
 
GROUP BY ID
 
ORDER BY TOTAL

The actual execution sequence of this is:

FROM STUDENT

GROUP BY ID

SELECT ID,COUNT(ID) AS TOTAL

ORDER BY TOTAL

This time there is no problem and it can be executed successfully . What if ORDER BY TOTAL is replaced by ORDER BY COUNT(ID)?

SELECT ID,COUNT(ID) AS TOTAL
 
FROM STUDENT
 
GROUP BY ID
 
ORDER BY COUNT(ID)

Actual execution sequence:

FROM STUDENT

GROUP BY ID

SELECT ID,COUNT(ID) AS TOTAL

ORDER BY COUNT(ID)

Yes, it can be executed successfully, see SQL execution plan, it 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 has not been created when GROUP BY NAME is executed, so it cannot be executed successfully.

　Summary

　I recall that I once randomly asked some people this question. No matter who said they didn’t know, we would deliberately laugh at them. Of course, one person’s ridicule was not the other’s ridicule. But it turns out that there are still some people who will not notice this knowledge point. I post it here just as a friendly reminder.