Shulou(Shulou.com)06/01 Report--

I came across a sql problem in my work today, about left join. Although I solved it later, I learned about the execution sequence of sql through this problem.

Scene restoration

In order to avoid security disputes, the scene is simulated.

There is a student form-S, a grade sheet G

CREATE TABLE `bigint (20) NOT NULL COMMENT 'student number', `sex`TINYINT DEFAULT'0' COMMENT 'gender 0-male 1-female', `name` varchar () DEFAULT NULL COMMENT 'name') ENGINE=InnoDB CHARSET=utf8mb4 COMMENT=' student form; CREATE TABLE `test_ score` (`id`bigint (20) NOT NULL COMMENT 'student number', `score`int NOT NULL COMMENT 'score', `level`TINYINT COMMENT 'score 0-fail 1-pass 2-excellent 3 -) ENGINE=InnoDB CHARSET=utf8mb4 COMMENT=' score sheet -- initializing students INSERT INTO test_student VALUES (1,0, 'Zhang San'), (2,0,'Li Si'), (3,1, 'Wang Xfang');-- initializing scores INSERT INTO test_score VALUES (1,10,0), (2,20,0), (3,100,3)

Now there is a need to find out the students' test scores, possible sql

SELECT ts.name AS 'first name', tc.score AS 'score' FROM test_student tsLEFT JOIN test_score tcON ts.id = tc.s_id

Everything went well, and suddenly, just after the exam, a student was transferred.

INSERT INTO test_student VALUES (4,0, 'newcomer')

He has no grades, or the sql check just now.

What should the teacher do if he only needs to look at the scores of the students who take the exam?

1. Use inner join

SELECT ts.name AS 'first name', tc.score AS 'score' FROM test_student tsINNER JOIN test_score tcON ts.id = tc.s_id

two。 Conditional filtering

SELECT ts.name AS 'first name', tc.score AS 'score' FROM test_student tsLEFT JOIN test_score tcON ts.id = tc.s_idAND tc.score is NOT NULL

Found that it is still wrong to add conditional filtering, er, to use where instead.

SELECT ts.name AS 'first name', tc.score AS 'score' FROM test_student tsLEFT JOIN test_score tcON ts.id = tc.s_idWHERE tc.score is NOT NULL

Bingo, then why is where right?

This involves the execution order of sql.

Where and join on

Infer from the above example

Where will filter out eligible records from the result set, and discard those that do not meet the criteria.

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

In addition to the INNER JOIN we used in the above example, we can also use several other connections.

The types of JOIN you can use and the differences between them are listed below.

JOIN: if there is at least one match in the table, return row LEFT JOIN: return all rows RIGHT JOIN from the left table even if there is no match in the right table; return all rows FULL JOIN from the right table even if there is no match in the left table: return rows as long as there is a match in one of the tables (mysql does not support it, you can consider using view implementation)

Here is a recommendation to write an easy-to-understand and simple learning SQL of various connections Join

Sql sequence

As you can see from the above, in sql, on is written before the where condition, so when the database engine analyzes and executes sql, does on also precede where?

The writing order of general sql

1.SELECT [column name * represents all columns]

2.FROM [Table name]

3.join_type JOIN [Table name]

4.ON [join condition]

5.WHERE [filter criteria]

6.GROUP BY [grouped field]

7.HAVING [grouping condition]

8.ORDER BY [sort field]

So what is the order in which sql is executed?

The standard sql parsing order is:

1.FROM assembly data, from different data sources (tables)

2.WHERE filters records based on conditions

Data grouping by 3.GROUP BY

4. Compute aggregate functions, such as avg,sum

5. Use the HAVING clause to filter grouping

6. Evaluate all expressions

7. Use ORDER BY to sort the results

What about the order in which sql is executed?

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

2.ON: apply the on condition to vt1. Only those who meet the join_condition condition can insert virtual table vt2.

3.OUTER (join): if you specify that rows not found in the OUTER JOIN retention table (preserved table) will be added to the vt2 as external rows, generate T3. If the from contains more than two tables, repeat the steps and steps for the result table generated by the previous join and the next table.

4.WHERE: vt3 is filtered by where. Only those that meet the where criteria can be inserted into vt4.

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

6.HAVING: apply a HAVING filter to vt5 only groups that make having_condition true insert vt6

7.SELECT: processing select lists to generate vt7

8.DISTINCT: removes duplicate lines from vt7 to produce vt8

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

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

When you get here, you should find that it is not easy to write a good sql. But knowing the execution sequence of sql can help to write a good program in the same way of development.

For example, there should not be too many join tables (filter the conditions first, and then establish the index of relevant query fields in the table according to the table join at the same time, which is quite fast in the case of big data multi-table joint query), this kind of sql optimization problem will be sorted out next time.

Practice it!

The above is the whole content of this article, I hope it will be helpful to your study, and I also hope that you will support it.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.