Write Smarter Queries with the SQL WITH Clause

The WITH clause, also known as the subquery factoring clause, was introduced in Oracle 9i as a way to make queries more efficient and readable. Let’s see how WITH works with a basic example.

Say we have three departments at our workplace – Accounting, Research and Sales – and we want to select only those departments with a total salary more than the average total.

The total salary per department is:

SQL> select dname, sum(sal) as dept_total
  2  from emp, dept
  3  where emp.deptno = dept.deptno
  4  group by dname
  5  /

DNAME          DEPT_TOTAL
-------------- ----------
ACCOUNTING           9150
RESEARCH            10875
SALES                9400

The average total salary per department is:

SQL>       select sum(sal) * 1/3
  2        from emp, dept
  3        where emp.deptno = dept.deptno
  4  /

SUM(SAL)*1/3
------------
  9808.33333

So the final query, without the WITH clause, will be:

SQL> select dname, sum(sal) as dept_total
  2  from emp, dept
  3  where emp.deptno = dept.deptno
  4  group by dname having
  5      sum(sal) >
  6      (
  7        select sum(sal) * 1/3
  8        from emp, dept
  9        where emp.deptno = dept.deptno
 10     )
 11  order by sum(sal) desc;

DNAME          DEPT_TOTAL
-------------- ----------
RESEARCH            10875

Notice the problem? We’re duplicating a large part of the query, calculating the sum twice. This makes the query inefficient and poor to read.

That’s when the WITH clause comes to our rescue. When a subquery is being processed multiple times, WITH lets you factor it out, give it a name and then reference the name wherever needed in the query.

SQL> with sumsal as
  2  (
  3   select dname, sum(sal) as dept_total
  4   from emp, dept
  5   where emp.deptno = dept.deptno
  6   group by dname
  7  )
  8  -- End of factored subquery
  9  -- Main query starts
 10  select dname, dept_total
 11  from sumsal        -- Reference #1
 12  where dept_total >
 13      (
 14         select sum(dept_total) * 1/3
 15         from sumsal -- Reference #2
 16       )
 17  order by dept_total desc;

DNAME          DEPT_TOTAL
-------------- ----------
RESEARCH            10875

The rewritten query using WITH not just improves performance but also makes code easier to understand and maintain.

The WITH Clause Syntax

with clause-name AS
(
subquery
)
main query referencing clause-name;

The syntax of the WITH clause is analogous to defining a function and invoking it multiple times in the main program. There is one difference though in the older versions of Oracle, which we’ll look at in the next article.

[Photo by hidden side]