Oracle WITH clause to simplify complex SQL

Oracle WITH  clause or subquery factoring clause provides a way to pre-materialize results of queries before statement executes. Also, Oracle WITH clause  is more efficient than using a subquery.

Oracle WITH clause and SQL:

--Subquery version select a.empno,b.dname from scott.emp a,scott.dept b where a.deptno=b.deptno and b.deptno < (select avg(deptno) from scott.dept); --Oracle WITH clause with temp_table as (select avg(deptno) dept_result from scott.dept)  select a.empno,b.dname  from scott.emp a,scott.dept b, temp_table c  where a.deptno=b.deptno  and b.deptno < c.dept_result;     EMPNO DNAME ---------- --------------      7369 RESEARCH      7566 RESEARCH      7782 ACCOUNTING      7788 RESEARCH      7839 ACCOUNTING      7876 RESEARCH      7902 RESEARCH      7934 ACCOUNTING 8 rows selected.

Oracle WITH clause and PL/SQL functions:

--Create table with test data create table test_tb (id number); insert into test_tb values (1); insert into test_tb values (2); commit; with function f_test(p_id number) return number is begin  return p_id+1; end; select f_test(id) from test_tb / Note: this feature does not work in Oracle 12.1.0.1

I hope this helps.

Please feel free to leave your questions or suggest improvements to this section.