استفاده از SQL WITH clause در اوراکل

مورد کاربرد WITH در Oracle:

• معمولا WITH clause را Subquery Factoring نيز مي نامند

• استفاده از SQL WITH clause خيلي شبيه استفاده از (Global temporary tables (GTT مي باشد

• اين تكنيك اغلب براي بهبود افزايش سرعت كوئري براي subqueries پيچيده استفاده مي شود

• استفاده از SQL WITH clause موقعي كه يك subquery داشته باشيم كه چندين بار تكرار شود

• هنگام استفاده از recursive queries مفيد مي باشد

• در Oracle 12c امكان ايجاد فانكشن در داخل WITH clause امكان پذير مي باشد

براي درك بهتر از چند مثال استفاده مي كنيم.

SELECT e.ename AS employee_name,
dc.dept_count AS emp_dept_count
FROM emp e,
(SELECT deptno, COUNT(*) AS dept_count
FROM emp
GROUP BY deptno) dc
WHERE e.deptno = dc.deptno

حال با استفاده از WITH clause در اوراکل

WITH dept_count AS (
SELECT deptno, COUNT(*) AS dept_count
FROM emp
GROUP BY deptno)
SELECT e.ename AS employee_name,
dc.dept_count AS emp_dept_count
FROM emp e,
dept_count dc
WHERE e.deptno = dc.deptno

WITH 
subquery_name
AS
(the aggregation SQL statement)
SELECT
(query naming subquery_name)

حال مثال زير را در قالب بالا قرار مي دهيم.

WITH sum_sales AS
( select sum(qty) all_sales from sales ),
number_stores AS
( select count(*) nbr_stores from stores ),
sales_by_store AS
( select stor_name, sum(qty) store_sales from
stores natural join sales
group by stor_name)
SELECT stores.stor_name
FROM   stores,
sum_sales,
number_stores,
sales_by_store
where  store_sales > (all_sales / nbr_stores)