Hello again ! After explaining to some friends how the oracle with clause works, I finally had some time to blog about it. When you finish reading this post I hope you have learned how the with clause works and how/when to use it.
What is it ? When to use it ?
The WITH clause from SQL-99 standard has been incorporated into the Oracle SQL standard from the Oracle 9i release 2 onwards.Internally, the WITH clause may be resolved either as an inline view or as a temporary table, if Oracle finds that the WITH clause content is sufficiently complex. You may also force Oracle to materialize WITH clause as a temporary table by using the
/*+ MATERIALIZE */
hint .
The WITH clause is typically used to simplify complex queries, or as a replacement for recursive queries.(those who use the CONNECT BY clause)
How to use it ?
Let’s start with a simple example using the HR schema.Suppose you have the following SQL statement , to retrieve how many people work in an employee’s department :
SELECT e.first_name || ' ' || e.last_name AS employee_name, dc.dept_count AS emp_dept_count FROM employees e, (SELECT department_id, COUNT(*) AS dept_count FROM employees GROUP BY department_id) dc WHERE e.department_id = dc.department_id ORDER BY 1;
We could rewrite the query above using the WITH clause as follows:
WITH dept_count AS ( SELECT department_id, COUNT(*) AS dept_count FROM employees GROUP BY department_id) -- LINE MARK SELECT e.first_name || ' ' || e.last_name AS employee_name, dc.dept_count AS emp_dept_count FROM employees e, dept_count dc WHERE e.department_id = dc.department_id ORDER BY 1;
As you can see, these queries are identical. The WITH clause query, has 2 parts : the part above the “LINE MARK” , that gets the data we will need ; the part below the “LINE MARK” that uses the data we gathered. This example however doesn’t show none of the advantages of using the WITH clause. Suppose, we also want each employees manager name and the number of people in the managers department. We could go with the traditional approach and write :
SELECT e.first_name || ' ' || e.last_name AS employee_name, dc1.dept_count AS emp_dept_count, m.first_name || ' ' || m.last_name AS manager_name, dc2.dept_count AS mgr_dept_count FROM employees e, (SELECT department_id, COUNT(*) AS dept_count FROM employees GROUP BY department_id) dc1, employees m, (SELECT department_id, COUNT(*) AS dept_count FROM employees GROUP BY department_id) dc2 WHERE e.department_id = dc1.department_id AND e.manager_id = m.employee_id AND m.department_id = dc2.department_id ORDER BY 1;
or we could use WITH clause to make things simpler :
WITH dept_count AS ( SELECT department_id, COUNT(*) AS dept_count FROM employees GROUP BY department_id) -- LINE MARK SELECT e.first_name || ' ' || e.last_name AS employee_name, dc1.dept_count AS emp_dept_count, m.first_name || ' ' || m.last_name AS manager_name, dc2.dept_count AS mgr_dept_count FROM employees e, dept_count dc1, employees m, dept_count dc2 WHERE e.department_id = dc1.department_id AND e.manager_id = m.employee_id AND m.department_id = dc2.department_id ORDER BY 1;
As you can see, using the WITH clause here saves us from the trouble of using the SQL part above the “LINE MARK” twice ! The subquery above the “LINE MARK” is only computed once but instantiated twice as dc1 and dc2.
I’m not going any further explaining the usage of the WITH clause in recursive querys as you may find an excellent article here.
Final thoughts
At first, using WITH as a start for a SQL sentence may seem awkward. But after a while you’ll find it very easy it to use. Just bear in mind that whatever you write on that initial sql statement, that will be your “temporary table” !
References
[1] Burleson Consulting ; Oracle WITH clause ; http://www.dba-oracle.com/t_with_clause.htm
[2] Oracle Base ; Oracle WITH clause ; http://www.oracle-base.com/articles/misc/WithClause.php
[3] PSOUG Reference ; Oracle WITH clause ; http://psoug.org/reference/with.html
[4] Burleson Consulting ; Oracle WITH clause to simplify complex SQL ; http://www.remote dba.net/oracle_10g_tuning/t_oracle_with_sql_clause.htm