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

with temporary tables as (select......)

Users with as can be seen in SQL statements written by developers below. A personal understanding.

For example:

with a as (select * from emp )

select ename,deptno,sal,(select avg(sal) from a where deptno=a.deptno) asal from a e;

The use of this sql with as is to read the table emp into the temporary table space to form a temporary table a, and use the temporary table a table in the following sql statement, so that the table emp is read only once (here is a full table scan, you can add a where condition after the SQL statement), and the following SQL statement

select ename,deptno,sal,(select avg(sal) from a where deptno=a.deptno) asal from a e; reads data from temporary tables when executed.

There is a question as to whether the performance of SQL statements using temporary tables improves in this way, which remains to be proved. This SQL statement is only for understanding with temporary tables as... No focus on SQL statement performance.

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.