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:

1 2 3 4 5 6 7 8 9 10 11

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:

1 2 3 4 5 6 7 8

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:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15

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.

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21

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

1 2 3 4 5

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, which we’ll look at in the next article: Why the SQL WITH clause is not exactly like a function definition.