Oracle with clause Learning

Oracle with clause learning with: [SQL] with -- query the total salary of the Department and Department dept_costs as (www.2cto.com select d. department_name, sum (e. salary) dept_total from departments d, employees e where d. department_id = e. department_id group by d. department_name ),
-- Use the result of the previous with query to calculate the Department's average total salary avg_costs as (select avg (dept_total) dept_avg from dept_costs) -- compare two with queries and output the query result select * www.2cto.com from dept_costs where dept_total> (select dept_avg from avg_costs) order by department_name comment: ① subqueries can reuse the same or previous with query blocks and store the query outputs of the with clause to the user's temporary tablespace for one query, everywhere ③ There are multiple query definitions before select at the same level. The first one uses with, and the latter does not use, and use commas to separate ④ the last with query block and the following select call cannot be separated by commas (,), but only by right brackets, the query of the with clause must be enclosed in brackets ⑤ if the with clause is defined and is not used in the query, the ora-32035 error will be reported, as long as there is a reference, not necessarily in the select call, the with clause can also be used in subsequent with clause queries, however, a with clause cannot be nested inside the with clause. The result column of the with clause cannot have an alias. You must use an alias or * refer to the with syntax.
(I) the brackets in the as and select statements cannot be omitted. (ii) select calls at the same level can only be defined once with commas, however, there is no advantage between the last with subquery and the following actual query. ① The with subquery may change the execution plan. ② The with subquery is executed only once, the results are stored in the user's temporary tablespace and can be referenced multiple times. This increases the readability of www.2cto.com ③ SQL: i. general usage: [SQL] with -- query the name of an employee in a SALES department saler_name as (select department_id from departments where department_name = 'sales' order by department_id) select last_name, first_name from employees e where department_id in (select * fro M saler_name) Note: using the with clause, you can define a result set in a complex query and use it repeatedly in the query. If you do not use it, an error is returned. In addition, the with clause obtains a temporary table, which must be referenced in most subqueries using select from (with query name) Ⅱ, for the same level, see [SQL] select last_name from (with -- query the SALES Department employee name www.2cto.com saler_name as (select department_id from orders where department_name = 'sales' order by department_id) select last_name, first_name from employees e where department_id in (select * from saler_name) III is treated as the same level in the two select calls of the set operation in the reference set, two with definitions [SQL] with -- query the SALES Department employee name saler_name as (www.2cto.com select department_id from orders ments where department_name = 'sales' order by department_id) select last_name, first_name from employees e where department_id in (select * from saler_name) union all select last_name, to_char (null) from employees