Oracle's with syntax

1. syntax for the WITH statement
Oracle introduced the WITH statement in 9i. The WITH statement is used to name the subquery in the query statement, which can then be referenced elsewhere in the query statement. The statement format is as follows:
1 with <alias_name> as (subquery_sql_statement)
2 SELECT <column_name_list> from <alias>;

Multiple subquery names can be defined in a with statement, and the subquery name can be repeatedly referenced in a query statement, even the previously defined subquery name can be referenced by a subquery defined later.
1 with <alias_one> as
2 (select <column_name_list> from TableA),
3 <alias_two> as
4 (select <column_name_list> from Alias_one)
5 SELECT <column_name_list>
6 from <alias_one>, <alias_two>
7 WHERE <join_condition>;

2. Advantages of the WITH statement
(1) Oracle improves query efficiency by storing the results of the with subquery in the user temporary table space for multiple references at a time.
(2) The WITH statement enhances the readability of SQL.

3. using examples with statements
1, the department's total salary is more than the average total salary of all departments.
Department table s_dept, employee table s_emp.
Analysis: To do this query, the first must calculate the total salary of all departments, and then calculate the average salary of the total salary, and then screen out the department's total salary is larger than the total salary of all departments of the Department of the average salary. So the 1th step with query to find out the total salary of all departments, 2nd step with the result table obtained from 1th step to find the average salary, and finally use these two times with the query to compare the total salary than the average salary results, as follows:

1 with dept_costs as– Find out the total wages of the Department
2 (SELECT d.dname, SUM (e.sal) dept_total
3 from DEPT D, EMP E
4 WHERE E.deptno = D.deptno
5 GROUP by D.dname),
6 ave_cost as– Query The average salary of the department, and in the latter with statement you can refer to the previous defined with statement
7 (SELECT SUM (dept_total)/COUNT (*) avg_sum from dept_costs)
8 SELECT *
9 from Dept_costs DC10 WHERE DC. Dept_total > (SELECT AC. Avg_sum from Ave_cost AC) – For comparison

4. with precautions
1. Note The syntax format
1) When there are multiple query definitions in the peer Select, the 1th is used with, followed by without with, and separated by commas.
2) The last with clause cannot have a comma between the following query, only the right parenthesis, and the query with the clause must be enclosed in parentheses.
3) If the WITH clause is defined and is not used in the query, the ora-32035 error is reported.
4) A WITH clause cannot be nested within a with clause.
Columns in a 2.With subquery should be aliased for reference.

5.With complex usage of statements
1. Generally we define the WITH statement only before the top-level SELECT statement.
2. In fact, the SQL statement can use the WITH statement where a select subquery is used, such as a scalar subquery, a subquery after from, an insert, and a subquery in update.