What is the usage of with as in oracle

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.

What is the usage of with as in Oracle

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!