What is the usage of with as in mysql

In mysql, "with as" is also called a subquery, which is used to define a sql fragment, and the fragment will be used repeatedly by the entire sql statement many times. This sql fragment is equivalent to a public temporary table. The syntax is "with tmp as (query statement)".

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

What is the usage of with as in mysql

WITH AS phrase, also called subquery factoring, can define a SQL Fragment, this SQL fragment will be used by the entire SQL statement. It can make the SQL statement more readable, or it can be used as the part that provides data in different parts of UNION ALL.

For UNION ALL, use WITH AS to define a UNION ALL statement. When the fragment is called more than 2 times, the optimizer will automatically put the data obtained by the WITH AS phrase into a Temp table. The prompt "meterize" forces the data of the WITH AS phrase to be put into a global temporary table. Many queries can be speeded up this way.

Because the with as subquery is only executed once, the results are stored in the user's temporary table to improve query performance, so it is suitable for multiple reference scenarios, such as: complex report statistics, paging queries, and need to get Results such as sum, count, and avg are used as filtering conditions to perform secondary processing on the query results!

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

Common syntax

–For an alias

with tmp as (select * from tb_name)

– For multiple aliases

with
tmp 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 building e and d temporary tables

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

In fact, it is just a large The SQL statements that are repeatedly used by the heap are placed in with as and given an alias, which can be used in subsequent queries. This plays an optimization role in large batches of SQL statements and is clear.

Recommended learning: mysql video tutorial

The above is the detailed content of What is the usage of with as in mysql. For more information, please follow other related articles on the PHP Chinese website!