(Simplify complex SQL Subquery)
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
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
