Why the SQL WITH clause is not exactly like a function definition

The SQL WITH clause is similar in concept to a function definition in procedural code. In a function, we factor the common code, put it all together at one place and call it as many times as needed in the main program. That’s precisely how we use the WITH clause in SQL – factor out the common subquery, put it all together at one place and call it as many times as needed in the main query.

BUT there is a difference.

If you define a function and don’t call it in the main program, what happens? Nothing wrong happens. If all else is fine, the program compiles and executes successfully.

Now, if you define a WITH clause and don’t call it in the main query, what happens? Well – plenty wrong happens!

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 empno
 11  from emp;
 from emp, dept
      *
ERROR at line 4:
ORA-32035: unreferenced query name defined in WITH clause

The ORA-32035: unreferenced query name defined in WITH clause error is thrown.

Bottomline: You cannot define a WITH clause that does not get used.

[Photo by bradleygee]