– med – Organisere Komplekse Spørringer

I software engineering, er det vanlig praksis å konsernet instruksjoner som liten og lett forståelige enheter—nemlig funksjoner eller metoder. Dette gjør koden gjenbrukbare og forbedrer lesbarheten.

Selv om SQL har funksjoner og prosedyrer så vel, de er ikke riktig verktøy for å bygge lett forståelig og gjenbrukbare enheter. I SQL, verken funksjoner eller prosedyrer er førsteklasses borgere på samme måte som subqueries er.0 building block av SQL er queries1—ikke instruksjonene.,

for Å gjøre spørringer gjenbrukbare, SQL-92 introdusert utsikt. Når den er laget, en visning har et navn i databasen skjemaet slik at andre spørsmål kan bruke det som et bord.

SQL:1999 lagt with punkt for å definere «uttalelse omfattet utsikt». De er ikke lagret i database skjema: i stedet, de er bare gyldig i spørringen de tilhører. Dette gjør det mulig å forbedre strukturen i en uttalelse uten å forurense det globale navnerommet.,

Syntaks

with klausulen er, enkelt sagt, en valgfri prefiks for select:2

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

syntaksen etter søkeordet with er det samme som det er for create view: det starter med spørringen navn, og optionally3 og i parentes navnet på kolonner-det kommer tilbake. Søkeordet as til slutt introduserer definisjonen i seg selv (query)—igjen (i parentes).,

With er ikke en frittstående kommando som create view er: det må bli etterfulgt av select. Denne spørringen (og subqueries det inneholder) kan se bare definert spørring navn i deres from klausulen.

En enkel with klausulen kan introdusere flere spørring navn ved å skille dem med komma (with søkeord er ikke gjentatt)., Hvert av disse spørsmålene kan du se spørring navn som er definert tidligere i samme with clause4 (en ofte oversett regel—se Kompatibilitet):

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

Spørring navn som er definert ved hjelp av with mask eksisterende tabeller eller synspunkter med samme navn.5

Ytelse Hensyn

de Fleste databaser prosessen with-søk på samme måte som de behandler visninger: de erstatte referanse til søket ved sin definisjon og optimalisere den generelle spørsmål.,

PostgreSQL database var annerledes til versjon 12: det som er optimalisert hver with spørring og de viktigste erklæringen uavhengig av hverandre.

Hvis en with spørringen er referert til flere ganger, noen databaser cache (dvs. «materialisere») sitt resultat for å hindre dobbel gjennomføring.

Les mer om dette i «with Klausulen: Ytelse Konsekvenser».

Bruke Tilfeller

• Lese og skrive SQL –

• Unit tester på forbigående data

• Angi navn på kolonner uten et kjent navn

Kompatibilitet

with klausulen ble innført med SQL:1999 som et sett av ekstra funksjoner. Forlater recursive variant til side, funksjonene er T121 for topp-nivå with klausuler og T122 for with klausuler i subqueries.

Den grunnleggende funksjonalitet with er godt støttet., Det eneste området hvor produkter oppføre seg annerledes er navnet oppløsning. Det er spesielt verdt å merke seg at with er ofte behandlet som with recursive.6 De mer subtile inkompabiliteter er knyttet til kvalifiserte tabell-navn (schema.table kan ikke se en with spørring)7 og utsikt brukt i omfanget av with spørringer (spørring inne i visningen ikke «se» den ytre with punkt).,8

Samsvar Alternativer

Utsikten kan dekke noen av use cases. Men dette kan lett føre til en urimelig antall visninger («namespace forurensning»). I de tilfeller subqueries er ofte bedre alternativ.

Proprietære Utvidelser

med så DML-prefikset (PostgreSQL, SQL Server, SQLite)

Noen databaser akseptere with som prefiks til DML-setninger (dokumenter: PostgreSQL, SQL Server, SQLite).,

SQL Server kan også bruke en with spørring som et mål for DML-setninger (i utgangspunktet å bygge en updatable vis).

Funksjoner i med (Oracle)

Oracle Database støtter funksjonen og prosedyre erklæringer innenfor with klausulen siden versjon 12cR1 (dokumentasjon).

DML på med (PostgreSQL)

du Starter med 9.1, PostgreSQL database støtter DML-setninger (insert, update, delete) i kroppen av with spørringer (dokumentasjon)., Når du bruker den (også proprietære) returning klausulen i DML-erklæringen, with spørringen faktisk returnerer data (f.eks. rader bare er satt inn).