A brief understanding of MySQL SELECT execution order

A brief understanding of MySQL SELECT execution order

The complete syntax of the SELECT statement is:

(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>

Note: The sequence number before the syntax is the SELECT execution order

The MySQL SELECT execution order is divided into 10 steps. As marked above, the first operation to be executed is the FROM operation, and the last operation to be executed is the LIMIT operation. Each operation will generate a virtual table, which is used as the input for processing. These virtual tables are transparent to the user, but only the last virtual table will be returned as the result. If a clause is not specified in the statement, the corresponding step will be skipped.

Let's take a closer look at each stage of query processing.

SELECT a.customer_id, COUNT(b.order_id) as total_orders
 FROM table1 AS a
 LEFT JOIN table2 AS b
 ON a.customer_id = b.customer_id
 WHERE a.city = 'hangzhou'
 GROUP BY a.customer_id
 HAVING count(b.order_id) < 2
 ORDER BY total_orders DESC;

1. FORM: Calculate the Cartesian product of the table on the left and the table on the right of FROM to generate the virtual table VT1.

2. ON: Perform ON filtering on virtual table VT1, and only those rows that meet <join-condition> will be recorded in virtual table VT2.

3. JOIN: If OUTER JOIN is specified (such as left join, right join), the unmatched rows in the retained table are added to virtual table VT2 as external rows, generating virtual table VT3.

4. WHERE: Filter the virtual table VT3 based on the WHERE condition. Only records that meet <where-condition> will be inserted into virtual table VT4.

5. GROUP BY: Group the records in VT4 according to the columns in the group by clause to generate VT5.

6. HAVING: Apply having filtering to virtual table VT5, and only records that meet <having-condition> will be inserted into virtual table VT6.

7. SELECT: Execute the select operation, select the specified column, and insert it into the virtual table VT7.

8. DISTINCT: Remove duplicate records in VT7. Generate virtual table VT8.

9. ORDER BY: Sort the records in virtual table VT8 according to <order_by_list> to generate virtual table VT9.

10. LIMIT: Take out the records of the specified row, generate virtual table VT10, and return the result.

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.

You may also be interested in:
  • A brief discussion on MySQL select optimization solution
  • MySQL select results to perform update example tutorial
  • Solve the problem that MySQL read-write separation causes data not to be selected after insert
  • How MySQL Select Statement is Executed
  • Implementation of MySQL select in subquery optimization
  • MySQL learning notes: complete select statement usage example detailed explanation
  • MySQL select, insert, update batch operation statement code examples
  • Explanation of mysql transaction select for update and data consistency processing
  • The difference between Update and select in MySQL for single and multiple tables, and views and temporary tables
  • Detailed explanation of the use of MySQL select cache mechanism
  • Summary of Select usage in MySql database
  • How a select statement is executed in MySQL

<<:  The difference and usage of Ctrl+z, Ctrl+c and Ctrl+d in Linux commands

>>:  Detailed examples of variable and function promotion in JavaScript

Recommend

How to quickly deploy Gitlab using Docker

1. Download the gitlab image docker pull gitlab/g...

Vue implements simple calculator function

This article example shares the specific code of ...

Solve the problem of Syn Flooding in MySQL database

Syn attack is the most common and most easily exp...

Several methods to clear floating (recommended)

1. Add an empty element of the same type, and the...

Detailed explanation of how to customize the style of CSS scroll bars

This article introduces the CSS scrollbar selecto...

How to solve the problem of automatic package update in Debian system

I don't know when it started, but every time ...

Detailed explanation of Docker compose orchestration tool

Docker Compose Docker Compose is a tool for defin...

Linux installation MySQL tutorial (binary distribution)

This tutorial shares the detailed steps of instal...

Native JS implementation of loading progress bar

This article shares a dynamic loading progress ba...

Details of watch monitoring properties in Vue

Table of contents 1.watch monitors changes in gen...

How to deploy LNMP architecture in docker

Environmental requirements: IP hostname 192.168.1...

Explanation of Truncate Table usage

TRUNCATE TABLE Deletes all rows in a table withou...