A small question about the execution order of SQL in MySQL

I encountered a sql problem at work today, about left join. Although it was solved later, I learned about the execution order of sql through this problem.

Scene restoration

To avoid security disputes, simulate the scenario.

There is a student table - S, a grade table G

CREATE TABLE `test_student` (
 `id` bigint(20) NOT NULL COMMENT 'student number',
	`sex` TINYINT DEFAULT '0' COMMENT 'Gender 0-Male 1-Female',
 `name` varchar(255) DEFAULT NULL COMMENT 'Name'
)ENGINE=InnoDB CHARSET=utf8mb4 COMMENT='student table';
 
CREATE TABLE `test_score` (
 `id` bigint(20) NOT NULL COMMENT 'student number',
 `score` int NOT NULL COMMENT 'score',
	`level` TINYINT COMMENT 'Grade 0-fail 1-pass 2-good 3-excellent'
) ENGINE=InnoDB CHARSET=utf8mb4 COMMENT='Score Table';
 
-- Initialize students INSERT INTO test_student VALUES(1, 0, '张三'), (2, 0, '李四'), (3, 1, '王X芳');

-- Initialize the scores INSERT INTO test_score VALUES(1, 10, 0), (2, 20, 0), (3, 100, 3);

Now there is a demand to find out the student's test scores. Possible sql

SELECT ts.name AS 'name', tc.score AS 'score'
FROM test_student ts
LEFT JOIN test_score tc
ON ts.id = tc.s_id;

Everything was going well. Suddenly, just after the exam, a student transferred in.

INSERT INTO test_student VALUES(4, 0, 'Newcomer');

He has no results, or the SQL query just now

What should I do if the teacher only needs to see the scores of the students who took the exam?

1. Use inner join

SELECT ts.name AS 'name', tc.score AS 'score'
FROM test_student ts
INNER JOIN test_score tc
ON ts.id = tc.s_id;

2. Add conditional filtering

SELECT ts.name AS 'name', tc.score AS 'score'
FROM test_student ts
LEFT JOIN test_score tc
ON ts.id = tc.s_id
AND tc.score is NOT NULL
;

I found that adding conditional filtering is still not correct. Uh, how about using where instead?

SELECT ts.name AS 'name', tc.score AS 'score'
FROM test_student ts
LEFT JOIN test_score tc
ON ts.id = tc.s_id
WHERE tc.score is NOT NULL
;

Bingo, then why is where right?

This involves the execution order of SQL

where and join on

Inferring from the above example

where will filter out the records that meet the conditions from the result set and discard those that do not meet the conditions

Join operation: Sometimes, in order to get a complete result, we need to get results from two or more tables. We need to execute join.

In addition to the INNER JOIN that we used in the example above, there are several other joins we can use.

The following is a list of the types of JOINs you can use, and the differences between them.

• JOIN: Returns rows if there is at least one match in the table
• LEFT JOIN: Returns all rows from the left table even if there are no matches in the right table
• RIGHT JOIN: Returns all rows from the right table even if there is no match in the left table
• FULL JOIN: Returns rows as long as there is a match in one of the tables (not supported by MySQL, you can consider using views to implement it)

Here is a simple and easy-to-understand tutorial on how to join SQL.

SQL order

From the above, we can see that in SQL, on is written before the where condition. So when the database engine analyzes and executes SQL, does on also come before where?

General SQL writing order

1.SELECT [column name* represents all columns]

2.FROM [table name]

3.join_type JOIN [table name]

4.ON [join condition]

5.WHERE [filter condition]

6. GROUP BY [Grouping Field]

7. HAVING [Grouping Condition]

8.ORDER BY [sort field]

So what is the order when SQL is executed?

The standard SQL parsing order is:

1. FROM assembles data from different data sources (tables)

2.WHERE Filter records based on conditions

3. GROUP BY groups data

4. Calculate aggregate functions, such as avg, sum

5. Use the HAVING clause to filter groups

6. Calculate all expressions

7. Use ORDER BY to sort the results

So what is the execution order of sql?

1.FROM: Perform Cartesian product on the first two tables to generate virtual table vt1

2.ON: Apply the on condition to vt1, and only those that meet the join_condition can be inserted into the virtual table vt2

3. OUTER (join): If OUTER JOIN is specified, rows not found in the preserved table are added to vt2 as external rows, generating t3. If from contains more than two tables, repeat steps 1 and 2 for the result table generated by the previous join and the next table, and then terminate directly.

4.WHERE: Perform a where filter on vt3, and only those that meet the where condition can be inserted into vt4

5.GROUP BY: Group vt4 by the group by field to get vt5

6.HAVING: Apply the HAVING filter to vt5 and only insert groups that make having_condition true into vt6

7.SELECT: Process the select list to generate vt7

8.DISTINCT: Remove duplicate rows from vt7 to generate vt8

9.ORDER BY: Sort the rows of vt8 by the column list in the order by clause to generate a cursor vc9

10.LIMIT (MySQL): Select a specified number of rows from the beginning of vc9 to generate vt10 and return it to the caller

At this point, you should find that it is not easy to write good SQL. However, understanding the execution order of SQL can help you write better programs during development.

For example, there cannot be too many join tables ( filter the conditions first, then connect the tables and create indexes for related query fields in the tables. This will make the query on multiple tables in big data much faster ). I will study and sort out this kind of SQL optimization problem next time.

Practice it!

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.