with_clause
Oracle PL/SQL SQL

Oracle with clause usage

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

				


			

		

	






	

		

							

			
Related Articles