Shulou(Shulou.com)05/31 Report--

This article is about how to use with as in mysql. The editor thinks it is very practical, so share it with you as a reference and follow the editor to have a look.

In mysql, "with as", also known as a subquery, is used to define a sql fragment, which is repeated many times by the entire sql statement. This sql fragment is equivalent to a common temporary table with the syntax "with tmp as (query statement)".

The operating environment of this tutorial: windows10 system, mysql8.0.22 version, Dell G3 computer.

The WITH AS phrase, also known as the subquery part (subquery factoring), can define a SQL fragment that is used by the entire SQL statement. You can make the SQL statement more readable, or it can be used as a part of providing data in different parts of UNION ALL.

For UNION ALL, a UNION ALL statement is defined using WITH AS, and when the fragment is called more than twice, the optimizer automatically puts the data obtained by the WITH AS phrase into a Temp table. The prompt meterialize forces the data of the WITH AS phrase to be placed in a global temporary table. Many queries can be improved in this way.

Because the with as subquery is executed only once, the results are stored in the user's temporary table to improve query performance, so it is suitable for scenarios with multiple references, such as complex report statistics, paging query, and need to take sum, count, avg and other results as filter conditions to reprocess the query results!

It is especially useful for union all. Because every part of union all may be the same, but if each part is executed once, the cost is too high.

Common grammar

-for an alias

With tmp as (select * from tb_name)

-for multiple aliases

Withtmp as (select * from tb_name), tmp2 as (select * from tb_name2), tmp3 as (select * from tb_name3), …

-equivalent to building an e-temporary table

With e as (select * from scott.emp e where e.empno=7499) select * from e

-equivalent to the establishment of e, d temporary tables

Withe as (select * from scott.emp), d as (select * from scott.dept) select * from e, d where e.deptno = d.deptno

In fact, it is to put a lot of repeatedly used sql statements in with as, take an alias, the following query can use it, so for a large number of sql statements to play an optimization role, and clear.

Thank you for reading! This is the end of the article on "how to use with as in mysql". I hope the above content can be of some help to you, so that you can learn more knowledge. if you think the article is good, you can share it for more people to see!

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.