Does mysql have a with as statement?

There is no "with as" statement in mysql5.7 and below versions, and there is a "with as" statement in mysql8.0 and above versions; mysql5.7 does not support this statement, but you can use it to create a temporary The same effect can be achieved in table mode. After mysql8.0, subqueries using this statement are supported.

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

Does mysql have a with as statement?

WITH AS subquery part [not supported by mysql5.7 and below, supported by mysql8.0 and sqlserver 】

The advantage of the subquery part is that it is a partial query. It is better to have three table connections that appear multiple times in one statement.

Single subquery part

with a as (select * from  Student a inner join Course b ON a.Id=b.StudentId  )select * from a

Analysis: This is how it works here Each time query a of the statement type queries the joint query set of the student and course tables

Multiple query part syntax

with a as (select * from  Student),
b as (select * from Course)
select * from a inner join b on a.Id=b.StudentId

If multiple subqueries in the entire query need to use the same subquery Query results, then you can use with as to extract the common subquery and add an alias. The subsequent query statements can be used directly, which plays a very good role in optimizing a large number of complex SQL statements.

Note:

• is equivalent to a temporary table, but unlike a view, it will not be stored and must be used in conjunction with select.

• There can be multiple temporary tables before the same select. Just write a with, separated by commas. Do not use commas in the last with statement.

• The with clause should be enclosed in parentheses.

To summarize the usage of with, it is similar to generating a temporary table.

Recommended learning: mysql video tutorial

The above is the detailed content of Does mysql have a with as statement?. For more information, please follow other related articles on the PHP Chinese website!