WITH Clause

WITH CLAUSE also known as sub-query factoring clause, It allows you to give a name to a select query and use the result inside a query. WITH clause generally improves query performance by running the query once and use the result later. WITH common table expression is very popular on SQLServer world as CTE. WITH clause makes the query easier to read and understand by removing the query complexity. Oracle optimizes the query by treating the query name as either an inline view or as a temporary table. You are required to access the performance of WITH clause on a case-by-case basis because not all CTE provides better performance.

SYNTAX:

WITH <alias_name> AS (sql_subquery_statement) 
SELECT column_list 
FROM   <alias_name>[, 
       tablename] [ 
WHERE  <join_condition>]

Example 1: Single sub-query select

WITH max_sal 
     AS (SELECT Max(sal) 
         FROM   baniya.emp) 
SELECT * 
FROM   max_sal; 

Example 2: Multiple sub-query select

WITH max_sal 
     AS (SELECT Max(sal) 
         FROM   baniya.emp), 
     emp_cnt 
     AS (SELECT Count(ename) 
         FROM   baniya.emp) 
SELECT * 
FROM   max_sal 
UNION 
SELECT * 
FROM   emp_cnt;

PL/SQL Example:

SET serveroutput ON; 
DECLARE 
    total_employee NUMBER := 0; 
    maximum_salary NUMBER := 0; 
BEGIN 
    WITH max_sal 
         AS (SELECT Max(sal) AS maxsal 
             FROM   baniya.emp) 
    SELECT maxsal 
    INTO   maximum_salary 
    FROM   max_sal; 

    WITH emp_cnt 
         AS (SELECT Count(ename) AS empcnt 
             FROM   baniya.emp) 
    SELECT empcnt 
    INTO   total_employee 
    FROM   emp_cnt; 

    --Print Output 
    dbms_output.Put_line('Maximum Salary: ' 
                         || maximum_salary); 

    dbms_output.Put_line('Total Employee No: ' 
                         || total_employee); 
END;
/

The above example can be re-written in PL/SQL without WITH clause, which is short,easy to read and manage. Which one do you like better?

SET serveroutput ON; 
DECLARE 
    total_employee NUMBER := 0; 
    maximum_salary NUMBER := 0; 
BEGIN 
    SELECT Max(sal) 
    INTO   maximum_salary 
    FROM   baniya.emp; 

    SELECT Count(ename) 
    INTO   total_employee 
    FROM   baniya.emp; 

    dbms_output.Put_line('Maximum Salary: ' 
                         || maximum_salary); 

    dbms_output.Put_line('Total Employee No: ' 
                         || total_employee); 
END; 
/

Oracle 12c has made a huge improvement on WITH clause which now can be used with Functions and Procedures. WITH clause must be followed by a single SELECT, INSERT, UPDATE, OR DELETE statement and there are few exceptions which are beyond the scope of this article. Last but not least, you cannot nest the WITH clause meaning you cannot have a sub-query within sub-query factoring clause.