med – organisera komplexa frågor

i software engineering är det vanligt att gruppera instruktioner som små och lättförståeliga enheter—nämligen funktioner eller metoder. Detta gör koden återanvändbar och förbättrar läsbarheten.

även om SQL har funktioner och procedurer också, är de inte rätt verktyg för att bygga lättförståeliga och återanvändbara enheter. I SQL är varken funktioner eller förfaranden förstklassiga medborgare på samma sätt som subqueries är.0 byggstenen i SQL är queries1 – inte instruktioner.,

för att göra frågor återanvändbara introducerade SQL-92 vyer. En gång skapat, en vy har ett namn i databasschemat så att andra frågor kan använda den som en tabell.

SQL:1999 lade till klausulenwith för att definiera ”statement scoped views”. De lagras inte i databasschemat: i stället är de bara giltiga i den fråga de tillhör. Detta gör det möjligt att förbättra strukturen i ett uttalande utan att förorena det globala namnområdet.,

Syntax

klausulenwith är, enkelt uttryckt, ett valfritt prefix förselect:2

WITH query_name (column_name1, ...) AS (SELECT ...) SELECT ...

syntaxen efter sökordetwith är densamma som förwith är densamma som för create view: det börjar med frågenamnet och optionally3 och i parentes namnet på kolumnerna den returnerar. Nyckelordet as introducerar slutligen själva definitionen (frågan)—igen inom parentes.,

With är inte ett fristående kommando somcreate view är: det måste följas avselect. Den här frågan (och underqueries den innehåller) kan referera till det just definierade frågenamnet i deras from – klausul.

en endawith – klausul kan introducera flera frågenamn genom att separera dem med ett kommatecken (with – sökordet upprepas inte)., Var och en av dessa frågor kan hänvisa till de frågenamn som tidigare definierats inom samma with clause4 (en ofta försummad regel—se Kompatibilitet):

WITH query_name1 AS ( SELECT ... ) , query_name2 AS ( SELECT ... FROM query_name1 ... )SELECT ...

Frågenamn definierade med with maskera befintliga tabeller eller vyer med samma namn.5

Prestandaöverväganden

de flesta databaser process with-frågor på samma sätt som de bearbetar vyer: de ersätter referensen till frågan med sin definition och optimerar den totala frågan.,

PostgreSQL-databasen var annorlunda fram till version 12: Den optimerade varjewith – fråga och huvuddeklarationen oberoende av varandra.

om enwith fråga hänvisas till flera gånger, vissa databaser cache (dvs ”materialize”) dess resultat för att förhindra dubbel exekvering.

Läs mer om detta i ”with Klausul: Prestandaeffekter”.

använd Fall

• Literate SQL

• enhetstester på transienta data

• tilldela namn till kolumner utan känt namn

Kompatibilitet

klausulenwith infördes med SQL:1999 som en uppsättning valfria funktioner. Om du lämnarrecursive – varianten åt sidan är funktionerna t121 för toppnivå with – klausuler och t122 för with – klausuler i underqueries.

den grundläggande funktionaliteten förwith stöds väl., Det enda område där produkterna beter sig annorlunda är namnupplösning. Det är särskilt anmärkningsvärt attwith ofta behandlas somwith recursive.6 de mer subtila inkompatibiliteterna är relaterade till kvalificerade tabellnamn (schema.table kan inte hänvisa till en with – fråga)7 och vyer som används inom ramen för with – frågor (frågan i vyn ser inte ”den yttre with – klausulen).,8

överensstämmande alternativ

vyer kan täcka några av användningsfallen. Detta kan dock lätt leda till ett orimligt antal synpunkter (”namespace pollution”). I dessa fall är subqueries ofta det bättre alternativet.

egna tillägg

med som DML-prefix (PostgreSQL, SQL Server, SQLite)

vissa databaser accepterarwith som prefix till DML-satser (docs: PostgreSQL, SQL Server, SQLite).,

SQL Server kan också använda enwith fråga som ett mål för DML-uttalanden (i princip bygga en uppdaterbar vy).

funktioner i med (Oracle)

Oracle-databasen stöder funktions-och procedurdeklarationer i klausulenwith sedan version 12cR1 (dokumentation).

DML in with (PostgreSQL)

Från och med 9.1 stöder PostgreSQL-databasen DML-satser (insert,update,delete) I kroppen avwith frågor (dokumentation)., När du använder (även proprietär) returning – klausulen i DML-satsen returnerar with – frågan faktiskt data (t.ex. raderna som just infogats).