Clausola WITH

La clausola WITH permette di assegnare un nome ad una subquery block e di essere richiamato dalla SELECT Statement che segue la clausola WITH.
E' una sintassi della SELECT e deve essere definita prima della SELECT statement.
La subquery referenziata con WITH viene trattata come una tabella temporanea (dinamica) utilizzabile soltanto all'interno di quella statement e non è memorizzata all'interno del database. Non può essere richiamata in successive sessioni SELECT statement. Il nome della subquery non può essere invocato all'interno della subquery che lo definisce.

E s e m p i o

tabella  SALE_CINEMA
---------------------------------------------------
CINEMA_ID             CINEMA_NAME
1                                THE SPACE
2                                STARDUST
3                                VILLAGE
4                                ARS

tabella SPETTACOLI
---------------------------------------------------------------------------------------------
SPETTACOLO_ID        SPETTACOLO_NAME        HOME_CINEMA_ID
1                                AVANGERS                               4
2                                SPIDERMAN                             1
3                                CAPTAIN AMERICA                  3
4                                CHRONICLE                  
5                                AVANGERS                              2
6                                AVANGERS                              1


E s e m p i o: individuare i cinema che proiettano più film.

WITH                                            
PRENOTA_CINEMA AS (  
-- numero di spettacoli che proietta ogni cinema
select P.CINEMA_ID, P.CINEMA_NAME, COUNT(S.SPETTACOLO_ID) ct
from SALE_CINEMA P, SPETTACOLI S                          
where P.CINEMA_ID = S.HOME_CINEMA_ID                
group by P.CINEMA_ID, P.CINEMA_NAME
),
-- individuo il cinema cha ha più spettacoli
CINEMA_PIENO AS (                              
select Max(ct)  max_ct
from PRENOTA_CINEMA                              
)                                              
SELECT cinema_name                              
FROM PRENOTA_CINEMA                              
WHERE ct = (select max_ct FROM CINEMA_PIENO);