Simply understand the writing and execution order of MySQL statements

There is a big difference between the writing order and the execution order of MySQL statements.

Writing order, the general writing order of mysql is:

select
<Data columns to be returned>
from
<table name>
<join, left join, right join...> join
<join table>
on
<join condition>
where
<where condition>
group by
<Grouping Condition>
having
<Filter conditions after grouping>
order by
<Sort criteria>
limit
<Line limit>

However, the execution order is:

from
<table name> # Cartesian product
on
<Filtering conditions> #Filter the virtual table of Cartesian product
<join, left join, right join...> join
<join table> #Specify join, which is used to add data to the virtual table after on. For example, left join will add the remaining data of the left table to the virtual table.
where
<where condition> #Filter the above virtual table
group by
<Grouping conditions> #Group
<sum() and other aggregate functions> #Used in the having clause for judgment. In writing, this type of aggregate function is written in the having judgment
having
<Grouping filter> # Aggregate and filter the grouped results
select
<Return data list> #The returned single column must be in the group by clause, except for aggregate functions
distinct
order by
<Sorting conditions> #Sorting
limit
<Line limit>

Partial explanation:

1. from: select * from table_1, table_2; The result is the same as select * from table_1 join table_2;, both of which are used to find the Cartesian product.

It is used to directly calculate the Cartesian product of two tables to obtain the virtual table VT1. This is the first operation performed by all select statements. Other operations are performed on this table, which is what the from operation completes.

2. on: Filter the qualified data from VT1 table to form VT2 table;

3. join: add the data of this join type to the VT2 table. For example, left join will add the remaining data of the left table to the virtual table VT2 to form the VT3 table. If the number of tables is greater than 2, steps 1-3 will be repeated.

4. where: Execute the filter (aggregate functions cannot be used) to obtain the VT4 table;

5. group by: Group the VT4 table to get the VT5 table; the columns used in the subsequent processing statements, such as select and having, must be included in the group by condition. If they are not included, an aggregate function must be used;

6. having: Filter the grouped data to get the VT6 table;

7. select: return the columns to get the VT7 table;

8. distinct: used to remove duplicates to obtain the VT8 table;

9. order by: used to sort and obtain the VT9 table;

10. limit: Return the required number of rows and get VT10;

Notice:

In the group by condition, each column must be a valid column and cannot be an aggregate function;

Null values ​​will also be returned as a group;

Except for aggregate functions, the columns in the select clause must be in the group by condition;

The above is the full content of this article. I hope it will be helpful for everyone’s study. I also hope that everyone will support 123WORDPRESS.COM.