(Simplify complex SQL Subquery)

Oracle offers three types of materialization:

Global Temporary Tables	The table definition is permanent.
Materialized Views	The definition and the data are permanent.
The WITH clause	The materialized subquery data is persistent through the query.

- It’s called Subquery Factoring or Named Subquery

- Allows you to give a sub-query block a name and call subquery with this name.

- The SQL WITH clause is very similar to the use of Global temporary tables.

- This technique often used to improve query speed for complex subqueries and make simplify complex SQL.

- You can use subquery factoring (SQL WITH) when a subquery is executed multiple times within main Query.

- You can create any number of named factoring queries.

Syntax

WITH

<alias_name> AS (sql_subquery_statement)

SELECT column_list FROM <alias_name>[,tablename]

[WHERE <join_condition>]

Use SQL WITH as table Alias

(Change Table alias before use it in main query)

WITH

E AS (SELECT * FROM EMP WHERE SAL>1200)

SELECT * FROM E WHERE E.DEPTNO = 10

UNION

SELECT * FROM E WHERE E.DEPTNO = 90

(Use SQL WITH as multiple Subqueries)

WITH

EM AS (SELECT * FROM EMP),

DP AS (SELECT DEPTNO FROM DEPT WHERE LOC = 'DALLAS')

SELECT * FROM EM,DP WHERE EM.DEPTNO IN (DP.DEPTNO)

(Make simplify complex SQL)

WITH

m AS (

SELECT TO_CHAR (SYSDATE, 'mm') g_month FROM DUAL

d AS (

SELECT TO_CHAR (SYSDATE, 'dd') g_day FROM DUAL

y AS (

SELECT TO_CHAR (SYSDATE, 'yyyy') g_year FROM DUAL

)

SELECT g_day || '/' || g_month || '/' || g_year dat

FROM m, d, y

Appsstuff Middleware