WITH Clause in Oracle SQL

       Usage of With Clause in Oracle SQL

• WITH clause, often referred to as Common Table Expressions (CTEs) or Subquery Factoring,.
• It is  a powerful tool in Oracle SQL that allows you to define a "temporary result" set that you can reference within a larger query.
• Imagine that we are  creating a "temporary table" that exists only for the duration of that specific query execution. 
• It makes complex queries much more readable and efficient.

Definition

The WITH clause defines one or more named subqueries before the main SELECT, INSERT, UPDATE, or DELETE statement. 

These named subqueries act like "virtual tables" that the main query can "select" from as a physical tables in the database.

Syntax:

WITH query_tb_name AS
(
       -- Your subquery logic here
       SELECT column1,
              column2
       FROM   table_name
       WHERE  condition )
SELECT *
FROM   query_tb_name; -- Reference the name defined above

query_tb_name: The alias you give to your subquery.

AS (...): The actual SQL query that generates the data

Main Query: The final statement that uses the data from the CTE.

When we use a WITH clause, Oracle’s optimizer handles it in one of two ways:

1. Inlining: The optimizer treats it like a regular subquery and integrates it into the main query.

2. Materialization: If the subquery is complex or used multiple times, Oracle may execute it once, store the result in a temporary segment (in memory or on disk), and then read from that temporary storage instead of DB original memory This is a huge performance win for heavy calculations.

Practical Example

Let us consider  EMPLOYEES table and a DEPARTMENTS table. 

Q: Find all employees who earn more than the average salary of their specific department.

Without a WITH clause, this usually requires a messy correlated sub-query. 

If we include "WITH clause", it becomes a clean, two-step process:

Using Inline view:

SELECT e.ename,
       e.sal,
       d.avg_dept_salary
FROM   emp e,
       (SELECT deptno,
               Round(Avg(sal), 2) AS avg_dept_salary
        FROM   emp
        GROUP  BY deptno) d
WHERE  1 = 1
       AND e.deptno = d.deptno
       AND e.sal > d.avg_dept_salary; 

Using Analytical Functions:

SELECT ename,
       sal,
       avg_dept_salary
FROM   (SELECT ename,
               sal,
               Round(Avg(sal)
                       OVER (
                         partition BY deptno), 2) AS avg_dept_salary
        FROM   emp)
WHERE  sal > avg_dept_salary; 

--------------------

Using With Clause

WITH dept_avg_salaries
     AS (
        -- Step 1: Calculate average salary per department
        SELECT deptno,
               Round(Avg(sal), 2) AS avg_dept_salary
         FROM   emp
         GROUP  BY deptno)
-- Step 2: Compare individual employees to those averages
SELECT e.ename,
       e.sal,
       d.avg_dept_salary
FROM   emp e,
       dept_avg_salaries d
WHERE  1 = 1
       AND e.deptno = d.deptno
       AND e.sal > d.avg_dept_salary; 

WITH clause has a "superpower" specifically for handling Hierarchical Queries (like an Organization Chart) that standard sub-queries can't handle easily.

Using Oracle SQL classic way: Level and Connect by Clause

SELECT LEVEL,
       ename,
       job
FROM   emp
START WITH mgr IS NULL -- The "Root" (CEO/President)
CONNECT BY PRIOR empno = mgr; -- The relationship (Parent = Child)

Modern Way: Recursive CTE (WITH Clause)

WITH org_chart (empno, ename, mgr, lvl)
     AS (
        -- Anchor Member: Start with the top boss
        SELECT empno,
               ename,
               mgr,
               1 AS lvl
        FROM   emp
        WHERE  mgr IS NULL
         UNION ALL
         -- Recursive Member: Join employees to their managers in the Org_Chart
         SELECT e.empno,
                e.ename,
                e.mgr,
                oc.lvl + 1
         FROM   emp e,
                org_chart oc
         WHERE  1 = 1
                AND e.mgr = oc.empno)
SELECT *
FROM   org_chart; 

[OR]

WITH emp_hierarchy (empno, ename, path, lvl)
     AS (
        -- START: The Anchor (Top Level)
        SELECT empno,
               ename,
               ename AS path,
               1     AS lvl
        FROM   emp
        WHERE  mgr IS NULL
         UNION ALL
         -- RECURSE: Join children to the parents found in the previous step
         SELECT e.empno,
                e.ename,
                eh.path
                || ' -> '
                || e.ename,
                eh.lvl + 1
         FROM   emp e,
                emp_hierarchy eh
         WHERE  1 = 1
                AND e.mgr = eh.empno)
SELECT *
FROM   emp_hierarchy
ORDER  BY path; 

 When we need to perform sequential data transformations:

1.Cleaning data in the first step, 

2. Aggregating it in the second, 

3. Joining it all together in the third.

Syntax for Multiple CTEs:

WITH first_cte AS
(
       SELECT ...
       FROM   table1 ), -- Note the comma here
second_cte AS
(
       SELECT ...
       FROM   first_cte -- You can reference the first CTE here!
), third_cte AS
(
       SELECT ...
       FROM   some_other_table )
SELECT *
FROM   second_cte s
JOIN   third_cte t
ON     s.id = t.id;

Practical Example: Sales Reporting: Suppose you want to find departments where the total sales are higher than the company-wide average department sales.

This requires following steps:

1. Calculate total sales per department.

2. Calculate the average of those totals.

3. Compare the two

WITH
-- Step 1: Sum up sales by department
dept_totals
AS (SELECT deptno,
           Sum(sal) AS total_dept_cost
    FROM   emp
    GROUP  BY deptno),
     -- Step 2: Calculate the average of the sums from Step 1
     avg_of_totals
     AS (SELECT Avg(total_dept_cost) AS overall_avg
         FROM   dept_totals)
-- Main Query: Compare Step 1 results to Step 2 results
SELECT d.deptno,
       d.total_dept_cost
FROM   dept_totals d,
       avg_of_totals a
WHERE  d.total_dept_cost > a.overall_avg;