Uso de Clausula WIHT con subselect como tablas-ORACLE

he examples below use the following tables.

-- drop table emp purge;

-- drop table dept purge;

create table dept (

  deptno number(2) constraint pk_dept primary key,

  dname varchar2(14),

  loc varchar2(13)

) ;

create table emp (

  empno number(4) constraint pk_emp primary key,

  ename varchar2(10),

  job varchar2(9),

  mgr number(4),

  hiredate date,

  sal number(7,2),

  comm number(7,2),

  deptno number(2) constraint fk_deptno references dept

);

Subquery Factoring

The WITH clause, or subquery factoring clause, is part of the SQL-99 standard and was added into the Oracle SQL syntax in Oracle 9.2. The WITH clause may be processed as an inline view or resolved as a temporary table. The advantage of the latter is that repeated references to the subquery may be more efficient as the data is easily retrieved from the temporary table, rather than being requeried by each reference. You should assess the performance implications of the WITH clause on a case-by-case basis.

This article shows how the WITH clause can be used to reduce repetition and simplify complex SQL statements. I'm not suggesting the following queries are the best way to retrieve the required information. They merely demonstrate the use of the WITH clause.

Using the SCOTT schema, for each employee we want to know how many other people are in their department. Using an inline view we might do the following.

ANTES: 

select e.ename as employee_name,

       dc.dept_count as emp_dept_count

from   emp e,

       (select deptno, count(*) as dept_count

        from   emp

        group by deptno) dc

where  e.deptno = dc.deptno;

DESPUES CON WIHT:

with dept_count as (

  select deptno, count(*) as dept_count

  from   emp

  group by deptno)

select e.ename as employee_name,

       dc.dept_count as emp_dept_count

from   emp e,

       dept_count dc

where  e.deptno = dc.deptno;

... hay muchos mas ejemplos en este articulo:

https://oracle-base.com/articles/misc/with-clause

Fuentes.

Artículo:  "WITH Clause : Subquery Factoring in Oracle" Publicado en https://oracle-base.com/ Por Tin Hall el 2007-06-20. Consultado el 10/06/2022.

URL: https://oracle-base.com/articles/misc/with-clause