Logical execution sequence analysis of SELECT statements in the database _ MySQL

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.