استفاده از 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)