Sequential analysis of the logical execution of SELECT statements in a database _mysql

Introduction

It's not a matter of how deep the technical problem is, how much the ability of a fork to program. This is not necessarily directly related to a person's ability to develop, but knowing these will help you with your SQL writing, worry and optimization. It's not a complex knowledge point, but a very basic SQL base. Do not know this, you have been building the house with ordinary cement, master these, you are building a house with high cement.

However, it is such a small point of knowledge, you can go to investigate the colleagues around the friends, you may get a "surprise."

Because this article is a sudden feeling and write, the following random written SQL statements have not been tested.

Look at the following sections of SQL statements:

Copy Code code 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 can not be successfully executed?

The following 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 a SELECT statement may differ from that in this order because of the query processor.

Several examples

Example one:

Copy Code code as follows:

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 grouped queries. However, it cannot perform successfully because the execution order of having is above the 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 that is generated after the last sentence select Id,count (ID) as total executes. Therefore, total is not recognized when the having total>2 is executed.

Example Two

Copy Code code as follows:

SELECT Id,count (ID) as Total

From STUDENT

GROUP by ID

ORDER BY Total

The actual order of execution of this is:

1.FROM STUDENT
2.GROUP by ID
3.SELECT Id,count (ID) as Total
4.ORDER by Total
This time there is no problem, can be successfully implemented. What if the order by total is changed to order by COUNT (ID)?

Copy Code code as follows:

SELECT Id,count (ID) as Total

From STUDENT

GROUP by ID

Order by COUNT (ID)

Actual Order of execution:

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, looking at the SQL execution plan, which is the same as the above order by total. The order by is executed after the SELECT, so you can use the alias total.

Example Three

Copy Code code as follows:

SELECT firstname+ ' +lastname as NAME, COUNT (*) as Count

From STUDENT

GROUP by NAME

Actual Order of execution:

Copy Code code as follows:

From STUDENT

GROUP by NAME

SELECT firstname+ ' +lastname as Name,count (*) as COUNT

Obviously, alias name has not been created while executing group by name, so it cannot be executed successfully.

Summarize

Recall that once asked some people this question, no matter who said they do not know when we will deliberately laugh, of course, this ridicule is not that ridicule. But it turns out that there are some people who won't notice this knowledge, posted here just as a friendly reminder.