Monday, September 26, 2016

Oracle SQL WITH Query


WITH Query:

In complex queries that process the same subquery multiple times, the WITH clause lets you factor out the subquery, give it a name, then reference that name multiple times within
the original complex query.

This query joins two tables and computes the aggregate SUM(SAL) more than once.
The text in blue represents the parts of the query that are repeated. 

SELECT dname, SUM(sal) AS dept_total
FROM emp, dept
WHERE emp.deptno = dept.deptno
GROUP BY dname HAVING SUM(sal) >( SELECT SUM(sal) * 1/3 FROM emp, dept
WHERE emp.deptno = dept.deptno ) ORDER BY SUM(sal) DESC;

Query optimized with WITH syntax
The aggregation and fetch from the database tables is done only once

WITH summary AS
( SELECT dname, SUM(sal) AS dept_total FROM emp, dept WHERE emp.deptno = dept.deptno GROUP BY dname )
SELECT dname, dept_total
FROM summary
WHERE dept_total > ( SELECT SUM(dept_total) * 1/3 FROM summary )
ORDER BY dept_total DESC;

No comments :

Post a Comment