ORACLE with AS usage

Syntax:

With Tempname as (select ...)
Select ...

For example: Now you want to get 11-14 from 1-19. The general SQL is as follows:

SELECT *
from (
            --simulate a 20-row data SELECT level as
             lv from
               DUAL CONNECT by Level
         <
) TT
 wher E tt.lv > tt.lv < 15

SQL with AS is:

With TT as (
                --simulate a 20-row data Select level as
                 LV from
                 DUAL CONNECT by Level
                <
             ) 
Select LV From TT
WHERE LV > LV < 15

The WITH query statement does not start with a select, but begins with the "with" keyword
You can assume that a temporary table TT was constructed beforehand before the query was actually made, and then you can use it more than once to do further analysis and processing

advantages of the WITH clause method

Increased SQL readability, if multiple subqueries are constructed, the structure will be clearer, and more importantly: "One analysis, multiple use", which is why the performance of the place, reached the "less read" goal. The

 first method table that uses a subquery is scanned two times, and a table is scanned only once using the WITH clause method. This can greatly improve the efficiency of data analysis and query.

 In addition, observe the WITH clause method execution plan, where "sys_temp_xxxx" is the intermediate statistic result temporary table constructed during the operation.