Logical execution sequence analysis of SELECT statements in the database
Introduction
This is not a deep technical question, but a cool programming capability. This is not directly related to a person's development capabilities, but knowing this will be of great help to your SQL writing, troubleshooting and optimization. It is not a complex knowledge point, but a very basic SQL base. If you don't know this, you have been building houses with ordinary Cement. if you know this, you are building houses with high cement.
However, it is such a small knowledge point that you can investigate colleagues and friends around you, maybe you will get a "surprise ".
As this article was written with a sudden sensation, the SQL statements written below were not tested.
Take a look at the following SQL statements:
The code is as follows: #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 cannot be successfully executed?
The logical execution sequence of the SELECT statement is as follows:
1. FROM
2. ON
3. JOIN
4. WHERE
5. GROUP
6. with cube or WITH ROLLUP
7. HAVING
8. SELECT
9. DISTINCT
10. ORDER
11. TOP
MICROSOFT pointed out that the actual physical execution sequence of the SELECT statement may be different from that of the query processor.
Examples
Example 1:
The code is as follows:
Select id, COUNT (ID) AS TOTAL
FROM STUDENT
GROUP BY ID
Having total> 2
Do you think this SQL statement is familiar? A very basic grouping query. But it cannot be executed successfully, because the order of HAVING execution is above SELECT.
The actual execution order 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 last select id, COUNT (ID) as total statement is executed. Therefore, TOTAL cannot be identified when having total> 2 is executed.
Example 2
The code is as follows:
Select id, COUNT (ID) AS TOTAL
FROM STUDENT
GROUP BY ID
ORDER BY TOTAL
The actual execution order is:
1. FROM STUDENT
2. GROUP BY ID
3. select id, COUNT (ID) AS TOTAL
4. ORDER BY TOTAL
This time there was no problem and the execution was successful. What if I replace ORDER BY TOTAL with ORDER BY COUNT (ID?
The code is as follows:
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 successfully executed. check the SQL execution plan. it is the same as ORDER BY TOTAL. Order by is executed after SELECT, so the alias TOTAL can be used.
Example 3
The code is as follows:
Select firstname + ''+ lastname as name, COUNT (*) AS COUNT
FROM STUDENT
GROUP BY NAME
Actual execution sequence:
The code is as follows:
FROM STUDENT
GROUP BY NAME
Select firstname + ''+ lastname as name, COUNT (*) AS COUNT
Obviously, the alias NAME is not created when the group by name is executed, so it cannot be executed successfully.
Summary
I recall that I have asked people this question at will. no matter who says they don't know it, we will deliberately laugh at it. However, it turns out that some people still don't notice this knowledge point. it just serves as a friendly reminder.