Oracle with clause

Learn With with by example:

[SQL]
View plaincopyprint?

1. With
2. -- Query the total salaries of departments and departments
3. Dept_costs (
   
4. Select D. department_name, sum (E. Salary) dept_total
   
5. From orders ments D, employees e
   
6. Where D. department_id = E. department_id
   
7. Group
   By D. department_name
8. ),
9. -- Calculate the average total salary of a department based on the result of the previous with query.
   
10. Avg_costs (
11. Select
    AVG (dept_total) dept_avg
12. From dept_costs
    
13. )
14. -- Compare two with queries and output query results
15. Select *
16. From dept_costs
17. Where dept_total> (select dept_avg
    From avg_costs)
18. Order by department_name
    

With -- query the total salary of the Department and Department dept_costs as (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), -- calculate the Department's average total salary avg_costs as (select AVG (dept_total) dept_avg from dept_costs) using the result of the previous with query) -- compare two with queries and output the query result select * From dept_costs where dept_total> (select dept_avg from avg_costs) order by department_name

Note:
① Subqueries can reuse the same or previous with query blocks and can be called Through select (The with clause can only be called by select)
② The query output of the with clause is stored in the user's temporary tablespace for one query and used everywhere
③ There are multiple query definitions before a SELECT statement of the same level. The first statement uses with, and the latter does not use with, and is separated by commas.
④ The last with query block and the following select call cannot be separated by commas (,). They are separated only by right brackets. The with clause query 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 to it, not necessarily in the Select call, it is also possible to query block references in later
⑥ The query defined by the with clause can be used in the with clause, but a with clause cannot be nested inside the with clause.
7. The result column of the with query has an alias. You must use an alias or *

Let's look at the with syntax.

(I) brackets in AS and select cannot be omitted.
(Ii) Select call at the same level. With can be defined only once. Multiple with subqueries are separated by commas, but there is no comma between the last with subquery and the actual query below.

Advantages of with clause

① The with clause may change the execution plan.
② The with subquery is executed only once, and the results are stored in the user's temporary tablespace, which can be referenced multiple times to increase performance.
③ SQL is highly readable.

Case:

I. general usage

[SQL]
View plaincopyprint?

1. With
2. -- Query the name of an employee in a sales department
3. Saler_name (
   
4. Select department_id
   From orders ments where department_name = 'sales'
   Order by department_id
   
5. )
6. Select last_name, first_name
   
7. From employees e
   
8. Where department_id in (select *
   From saler_name)

With -- Query saler_name as (select department_id from departments where department_name = 'sales' order by department_id) of the employee in the sales department select last_name, first_name from employees e where department_id in (select * From 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 and must use select from (with query name)

II is referenced in most subqueries and visible at the same level

[SQL]
View plaincopyprint?

1. Select last_name
   
2. From (
   
3. -- Query the name of an employee in a sales department
4. Saler_name (
5. Select department_id
   From orders ments where department_name = 'sales'
   Order by department_id
   
6. )
7. Select last_name, first_name
   
8. From employees e
9. Where department_id
   In (select *
   From saler_name)
10. )

Select last_name from (with -- Query 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 * From saler_name ))

Iii. Reference in the collection

The two select calls of the set operation are treated as the same level, and two with definitions cannot appear.

[SQL]
View plaincopyprint?

1. With
2. -- Query the name of an employee in a sales department
3. Saler_name (
   
4. Select department_id
   From orders ments where department_name = 'sales'
   Order by department_id
   
5. )
6. Select last_name, first_name
   
7. From employees e
   
8. Where department_id in (select *
   From saler_name)
9. Union all
   
10. Select last_name, to_char (null)
    
11. From employees
    

Address: http://blog.csdn.net/linwaterbin/article/details/7990594