Digging Information From A Database

Better Essays
Digging information from a database sometimes requires creating complicated queries. Often a simple select * from . . . is nested into another one and both into another one and so forth until the result becomes a very complicated query containing scalar queries (subqueries in the select clause) and in-line views (subqueries in the from clause). Such queries are difficult to read, hard to maintain, and a nightmare to optimize.

Fortunately there is a WITH clause defined in SQL-99 and implemented in Oracle9 release 2 and later. In Oracle database the WITH clause is used for materializing subqueries to avoid recomputing them multiple times without using temporary tables. The WITH clause allows factor out a sub-query, name it, and then
…show more content…
What Can We Do WITH

Samples above shows that we can do following WITH:
1. Reference a named query is allowed any number of times.
2. Any number of named queries are allowed.
3. Named queries can reference other named queries that came before them and even correlate to previous named queries.
4. Named queries has a local scope to the SELECT in which they are defined.


The obvious is the ability to create reusable construction inside a SELECT, name it and reuse it whenever necessary.

Referencing the Same Sub-query Multiple Times

The following query joins two tables and computes the aggregate SUM(SAL) more than once. The bold text 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
Get Access