In Oracle, the "with as" statement is equivalent to creating a temporary table and placing the intermediate results in a statement in the temporary table space. You can also use this statement to define multiple temporary tables. The syntax is "with temptablename([field list]) as (select ...)".
The operating environment of this tutorial: Windows 10 system, Oracle 11g version, Dell G3 computer.
The with as clause in Oracle query is equivalent to creating a temporary table and placing some intermediate results in a statement in the temporary table space. Name the subquery in the query and put it at the front of the SELECT statement. The syntax is as follows:
with temptablename([字段列表]) as (select ...) select ...
You can also use the with as clause to define multiple temporary tables. When creating multiple temporary tables, the subsequent temporary tables can access the previously built temporary tables. The syntax is as follows:
with temptablename1([字段列表]) as (select ...), temptablename2([字段列表]) as (select ...) select ...
Description:
(1) The temporary table created using the with as clause will be eliminated after the retrieval query is completed.
(2) When creating multiple temporary tables, the latter can access the temporary tables that have been built previously.
(3) When a query has multiple identical subqueries, the with as clause is generally used, because the subquery results are stored in the memory temporary table and the execution efficiency is high.
Usage example
Query which departments have a total salary higher than the average salary of all departments:
1. No need with as
The code and query results are as follows:
SELECT deptno,total_sal FROM (SELECT deptno,sum(sal) as total_sal FROM employee GROUP BY deptno) temp1 WHERE total_sal>( SELECT avg(total_sal) FROM (SELECT deptno,sum(sal) as total_sal FROM employee GROUP BY deptno) 10:46:44 10 ); DEPTNO TOTAL_SAL ---------- ---------- 20 5701632000 Elapsed: 00:00:02.33
In this example, two identical subqueries appear in one query.
2. Use with as
10:45:47 SQL> WITH tempA AS (SELECT deptno,sum(sal) as total_sal FROM employee GROUP BY deptno) SELECT * FROM tempA WHERE total_sal>( SELECT AVG(total_sal) FROM tempA 10:46:02 10 ); DEPTNO TOTAL_SAL ---------- ---------- 20 5701632000 Elapsed: 00:00:01.42
It can be seen from the comparison of the two queries that using the with as clause can simplify the query statement and improve the query speed.
Recommended tutorial: "Oracle Video Tutorial"
The above is the detailed content of What is the usage of with as in oracle. For more information, please follow other related articles on the PHP Chinese website!