en génie logiciel, il est courant de regrouper les instructions sous forme de petites unités facilement compréhensibles—à savoir des fonctions ou des méthodes. Cela rend le code réutilisable et améliore la lisibilité.
même si SQL a aussi des fonctions et des procédures, ce ne sont pas les bons outils pour construire des unités facilement compréhensibles et réutilisables. En SQL, ni les fonctions ni les procédures ne sont des citoyens de première classe de la même manière que les sous-requêtes.0 le bloc de construction de SQL sont des queries1 – pas des instructions.,
pour rendre les requêtes réutilisables, SQL-92 a introduit des vues. Une fois créée, une vue a un nom dans le schéma de base de données afin que les autres requêtes puissent l’utiliser comme une table.
SQL:1999 a ajouté la clausewith pour définir les « vues de portée d’instruction”. Ils ne sont pas stockés dans le schéma de base de données: ils ne sont valides que dans la requête à laquelle ils appartiennent. Cela permet d’améliorer la structure d’un énoncé sans polluer l’espace de noms global.,
Syntaxe
Le with l’article est, tout simplement, un préfixe facultatif pour les select:2
WITH query_name (column_name1, ...) AS (SELECT ...) SELECT ...La syntaxe après le mot-clé with est le même que pour le create view: il commence avec le nom de la requête, et optionally3 et entre parenthèses le nom des colonnes il retourne. Le mot—clé as introduit enfin la définition elle-même (la requête) – à nouveau entre parenthèses.,
With n’est pas un stand alone de commande de type create view est: il doit être suivi par select. Cette requête (et les sous-requêtes qu’elle contient) peuvent faire référence au nom de requête qui vient d’être défini dans leur clause from.
Une seule clausewithpeut introduire plusieurs noms de requête en les séparant par une virgule (Le mot-cléwith n’est pas répété)., Chacune de ces requêtes peut faire référence aux noms de requêtes précédemment définis dans la mêmewith clause4 (une règle souvent négligée—voir compatibilité):
WITH query_name1 AS ( SELECT ... ) , query_name2 AS ( SELECT ... FROM query_name1 ... )SELECT ...les noms de requêtes définis à l’aide dewith masquent des tables ou des vues5
considérations de Performance
La plupart des bases de données traitentwith-requêtes de la même manière qu’elles traitent les vues: elles remplacent la référence à la requête par sa définition et optimisent la requête globale.,
La base de données PostgreSQL était différente jusqu’à la version 12: elle optimisait chaque requêtewith et l’instruction principale indépendamment l’une de l’autre.
Si une requêtewith est référencée plusieurs fois, certaines bases de données mettent en cache (c’est-à-dire « matérialisent”) son résultat pour empêcher la double exécution.
En savoir plus à ce sujet dans « with Clause: impacts sur les performances”.
pour mon propre compte
je gagne ma vie de la formation, d’autres services liés à SQL et de la vente de mon livre. En savoir plus sur https://winand.at/.,
de Cas d’Utilisation
-
Lettré SQL
-
les tests d’Unité transitoire de données
-
Attribuer des noms aux colonnes sans nom
Compatibilité
Le with clause a été introduite par SQL:1999 comme un ensemble de fonctionnalités optionnelles. En laissant de côté la variante recursive, les fonctionnalités sont T121 pour les clauses with de niveau supérieur et t122 pour les clauses with dans les sous-requêtes.
la fonctionnalité de base dewith est bien prise en charge., La seule zone où les produits se comportent différemment est la résolution des noms. Il est particulièrement intéressant de noter que with est souvent traités comme des with recursive.6 les incompatibilités les plus subtiles sont liées aux noms de table qualifiés (schema.table ne peut pas faire référence à une requête with) 7 et aux vues utilisées dans la portée des requêtes with (la requête à l’intérieur de la vue ne « voit” pas>clause).,8
Conforme Alternatives
les Vues peuvent couvrir une partie des cas d’utilisation. Cependant, cela peut facilement conduire à un nombre déraisonnable de vues (« pollution de l’espace de noms”). Dans ces cas, les sous-requêtes sont souvent la meilleure option.
Extensions propriétaires
avec comme préfixe DML (PostgreSQL, SQL Server, SQLite)
certaines bases de données acceptentwith comme préfixe aux instructions DML (docs: PostgreSQL, SQL Server, SQLite).,
SQL Server peut également utiliser une requêtewith comme cible pour les instructions DML (essentiellement en construisant une vue pouvant être mise à jour).
fonctionne avec (Oracle)
La base de données Oracle prend en charge les déclarations de fonction et de procédure dans la clausewith depuis la version 12cr1 (documentation).
DML avec (PostgreSQL)
à Partir avec la 9.1, le PostgreSQL base de données prend en charge les instructions DML (insert, update, delete) dans le corps de la balise with requêtes (documentation)., Lors de l’utilisation de la clause returning (également propriétaire) de L’instruction DML, la requête with renvoie en fait des données (par exemple les lignes qui viennent d’être insérées).