Learn SQL query execution order from scratch

The SQL query statement execution order is as follows:

(7) SELECT 
(8) DISTINCT <select_list>
(1) FROM <left_table>
(3) <join_type> JOIN <right_table>
(2) ON <join_condition>
(4) WHERE <where_condition>
(5) GROUP BY <group_by_list>
(6) HAVING <having_condition>
(9) ORDER BY <order_by_condition>
(10) LIMIT <limit_number>

Preliminary preparations

1. Create a new test database

create database testData;

2. Create a test table and insert data as follows:

User Table

Order Form

Prepare SQL logical query test statements

SELECT a.user_id,COUNT(b.order_id) as total_orders
FROM user as a
LEFT JOIN orders as b
ON a.user_id = b.user_id
WHERE a.city = 'beijing'
GROUP BY a.user_id
HAVING COUNT(b.order_id) < 2
ORDER BY total_orders desc

Use the above SQL query to get customers from Beijing who have less than 2 orders;

During the execution of these SQL statements, a virtual table will be generated to save the execution results of the SQL statements.

1. Execute the FROM statement

The first step is to execute the FROM statement. We first need to know which table to start with, and this is what FROM tells us. Now that we have two tables, <left_table> and <right_table> , which table should we start with, or should we start after making some connection between the two tables? How do they relate to each other? — Cartesian product

After the FROM statement performs a Cartesian product on the two tables, a virtual table, VT1 (vitual table 1), is obtained, with the following content:

There are 28 (number of user records * number of orders records) records in total. This is the result of VT1. The following operations are based on VT1.

2. Execute ON filtering

After executing the Cartesian product, ON a.user_id = b.user_id conditional filtering is then performed. According to the conditions specified in ON , the data that does not meet the conditions is removed, and VT2 is obtained as follows:

select * from user as a inner JOIN orders as b ON a.user_id = b.user_id; 

3. Add external rows

This step only occurs when the join type is OUTER JOIN , such as LEFT OUTER JOIN , RIGHT OUTER JOIN , and FULL OUTER JOIN . Most of the time, we will omit the OUTER keyword, but OUTER represents the concept of external rows.

LEFT OUTER JOIN marks the left table as a reserved table: that is, all data in the left table will be queried, and if there is no corresponding data in the right table, it will be filled with NULL:

RIGHT OUTER JOIN marks the right table as a reserved table: that is, all data in the right table will be queried, and if there is no corresponding data in the left table, it will be supplemented with NULL;

FULL OUTER JOIN uses both left and right tables as reserved tables, but MySQL does not support full joins. You can implement full joins in the following ways:

Since I used LEFT JOIN in the prepared test SQL query logic statement, the resulting VT3 table is as follows:

4. Execute where condition filtering

The data with the added external rows is filtered by the where condition. Only the records that meet the <where_condition> condition will be filtered out. Execute WHERE a.city = 'beijing' to get VT4 as follows:

However, when using the WHERE clause, you need to pay attention to the following two points:

1. Since the data has not been grouped yet, you cannot use where_condition=MIN(col) in the where filter condition to filter group statistics.

2. Since the column selection operation has not been performed, the use of column aliases in the select is also not allowed. For example, select city as c from table1 where c='beijing' is not allowed.

5. Execute the group by statement

GROU BY clause is mainly used to group the virtual table obtained by using the WHERE clause. Execute GROUP BY a.user_id to obtain VT5 as follows:

6. Execution of having

The HAVING clause is mainly used in conjunction with GROUP BY clause to perform conditional filtering on the data of VT5 obtained by grouping. Execute HAVING COUNT(b.order_id) < 2 to obtain VT6 as follows:

7. Select List

The SELECT clause is only executed now. Don't assume that SELECT clause is the first one to be executed because it is written in the first line.

We execute SELECT a.user_id,user_name,COUNT(b.order_id) as total_orders in the test statement, select the content we need from VT6, and get VT7 as follows:

8. Execute distinct to deduplicate data

If the DISTINCT clause is specified in the query, a temporary in-memory table is created (if it cannot fit in memory, it needs to be stored on disk). The table structure of this temporary table is the same as the virtual table generated in the previous step. The difference is that a unique index is added to the column for the DISTINCT operation to eliminate duplicate data. The test SQL does not contain a DISTINCT clause, so it will not be executed.

9. Execute the order by clause

Sort the contents of the virtual table VT7 by the specified column, and then return a new virtual table. We execute ORDER BY total_orders DESC in the test SQL statement and get the following results:

DESC sorts in descending order, ASC sorts in ascending order

10. Execute the limit statement

The LIMIT clause selects the specified row data starting from the specified position from the virtual table obtained in the previous step, which is often used for paging;

LIMIT of MySQL database supports the following options: limit n,m

Indicates selecting m records starting from the nth record. For small data, there is no problem with using the LIMIT clause. However, when the amount of data is very large, using LIMIT n, m is very inefficient. Because the LIMIT mechanism is to scan from the beginning each time, if you need to read 3 pieces of data starting from the 600,000th row, you need to first scan to the 600,000th row and then read it. The scanning process is a very inefficient process.

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.