What is WITH Clause in Oracle? (The WITH clause to simplify complex SQL)
- Oracle With Clause is similar to temporary tables, where you store the data once and read it multiple times in your sql query.
- Oracle With Clause is used when a sub-query is executed multiple times.
- In simple With Clause is used to simplify the complex SQL. You can improve the performance of the query by using with clause
- A WITH clause is really best used when the result of the WITH query is required more than one time in the body of the query such as where one averaged value needs to be compared against two or three times.
Syntax:
WITH
<Alias> AS (Subquery-Select-SQL)
SELECT
<Column-List> FROM <Alias>;
SQL> WITH EMP_COUNT AS
2 (SELECT Count(*) Num, DEPTNO
3 From EMP_TEST Group By DEPTNO)
4 SELECT DEPARTMENT, NUM
5 From DEPT_TEST, EMP_COUNT
6 Where DEPT_TEST.DEPTNO=EMP_COUNT.DEPTNO;
DEPARTMENT NUM
-------------------- ----------
Security 2
Insurance 4
ENU 3
What the above SELECT
statement does, basically, is create a subquery that gets a count of employees
by department (it names the subquery Emp_Count), and then calls that subquery
in the FROM clause of the main select statement the way it would call a table
or view.
WITH Clause Advantages
· The main advantage of
the with clause (or subquery factoring clause) is that repeated references to
the subquery may be more efficient as the data is easily retrieved from the
temporary table, rather than being re-queried by each reference.
· Multiple reference to
same table in a Query can be reduced by using the With clause, only single hit
will be given to the database and that result set can be used in the query
multiple times so that the cost of the query will be less when compared to
using in-line view.
Examples
For each employee,
we want to know how many other people are in their department. Using an Inline
View, we might do the following.
SQL> Select E.NAME, D.DEPT_COUNT EMP_DEPT_COUNT
2 From EMP_TEST E,
3 (Select DEPTNO, Count(*) DEPT_COUNT
4 From EMP_TEST
5 Group By DEPTNO) D
6 Where E.DEPTNO=D.DEPTNO;
NAME EMP_DEPT_COUNT
---------- --------------
Ankit 4
Nikhil 3
Rajan 4
Karan 4
Sajal 3
Ravi 2
Surya 2
Sam 4
Jack 3
9 rows selected.
SQL> WITH DEPT_COUNT As
2 (Select DEPTNO, Count(*) DEPT_COUNT
3 From EMP_TEST Group By DEPTNO)
4 Select E.NAME,D.DEPT_COUNT EMP_DEPT_COUNT
5 From EMP_TEST E, DEPT_COUNT D
6 Where E.DEPTNO=D.DEPTNO;
NAME EMP_DEPT_COUNT
---------- --------------
Ankit 4
Nikhil 3
Rajan 4
Karan 4
Sajal 3
Ravi 2
Surya 2
Sam 4
Jack 3
9 rows selected.
The difference seems rather insignificant here.
What if we also
want to pull back each Employees manager name and the number of people in the
Managers department?
Using the inline View, it now looks like this.
SQL> Select E.NAME, D1.DEPT_COUNT EMP_DEPT_COUNT,
2 M.NAME Manager_Name,
3 D2.DEPT_COUNT MGR_DEPT_COUNT
4 From EMP_TEST E,
5 (Select DEPTNO,Count(*) DEPT_COUNT
6 From EMP_TEST Group By DEPTNO) D1,
7 EMP_TEST M,
8 (Select DEPTNO, Count(*) DEPT_COUNT
9 From EMP_TEST Group By DEPTNO) D2
10 Where E.DEPTNO=D1.DEPTNO
11 And E.ManagerID=M.ID
12 And M.DEPTNO=D2.DEPTNO;
NAME EMP_DEPT_COUNT MANAGER_NA MGR_DEPT_COUNT
---------- -------------- ---------- --------------
Jack 3 Surya 2
Sam 4 Rajan 4
Ravi 2 Nikhil 3
Surya 2 Nikhil 3
Using the WITH clause
this would look like the following.
SQL> WITH DEPT_COUNT As
2 (Select DEPTNO, Count(*) DEPT_COUNT
3 From EMP_TEST Group By DEPTNO)
4 Select E.Name, D1.DEPT_COUNT EMP_DEPT_COUNT,
5 M.NAME Manager_Name,
6 D2.DEPT_COUNT MGR_DEPT_COUNT
7 From
8 EMP_TEST E, DEPT_COUNT D1,EMP_TEST M, DEPT_COUNT D2
9 Where E.DEPTNO=D1.DEPTNO
10 And E.ManagerID=M.ID
11 And M.DEPTNO=D2.DEPTNO;
NAME EMP_DEPT_COUNT MANAGER_NA MGR_DEPT_COUNT
---------- -------------- ---------- --------------
Jack 3 Surya 2
Sam 4 Rajan 4
Ravi 2 Nikhil 3
Surya 2 Nikhil 3
So, we don't need to redefine the same subquery multiple times. Instead we just use the query name defined in the WITH clause, making the query much easier to read.
From these four SELECT
statements we learn that the WITH clause lets us do the following:
1) We can
reference a named query any number of times
2) We can
create any number of named queries
3) Named
queries can reference other named queries that came before them and even
correlate to previous named queries
4) Named
queries are only good for the SELECT statement that names them, their scope is
local to the SELECT in which they are defined, hence no sharing across
statements.
The most obvious thing one
gets from use of the WITH clause is the ability to construct reusable
components inside a SELECT. We can give a name to a set of rows (no matter how
complicated the SELECT is to build them), and then use the name of the NAMED
SELECT, anywhere we would have otherwise had to duplicate the text we named.
Yes, but the WITH clause is
part of the SELECT part of the insert:
SQL> Create Table Tab (x number, y
number);
Table
created.
SQL>Insert into Tab(x, y)
With V As (
Select
1 A, 2 B from Dual Union All
Select
3 A, 4 B from Dual)
Select A, B From V ;
Update MyTable T
Set z = (
With comp As (
Select
b.*, 42 as computed
From MyTable T
Where
bs_id = 1
)
Select
c.computed
From comp c
where c.id = t.id
)
MERGE statement USING the WITH clause?
SQL> MERGE INTO Emp e USING
(WITH average AS
(SELECT deptno, AVG(sal) avg_sal FROM emp
group by deptno)
SELECT * FROM average
) u
ON (e.deptno = u.deptno)
WHEN MATCHED THEN
UPDATE SET e.sal =
CASE
WHEN e.sal <= u.avg_sal
THEN e.sal * 1.05
ELSE e.sal * 1.03
END
Get involved
and leave your Comments in the Box Below. The more people get involved, the
more we all benefit. So, leave your thoughts before you leave
the page.
what is the function to identify number of occurences of charecter in a string in sql ?
ReplyDelete