MySQL Delspørring med eksempler

Hva er en delspørring i SQL?

A undersøkelse er en SELECT-spørring som finnes i en annen spørring. Den indre SELECT-spørringen brukes vanligvis til å bestemme resultatene av den ytre SELECT-spørringen, slik at databasen evaluerer den indre setningen først og deretter sender utdataene oppover.

Den indre spørringen kalles indre spørring eller nestet spørring, og setningen som inneholder den kalles ytre spørringLa oss se på syntaksen for underspørringen.

Diagrammet ovenfor viser den generelle formen på setningen: den ytre SELECT-klausulen angir kolonnene du vil se, og den indre SELECT-klausulen i parentes angir verdien eller listen over verdier som WHERE-klausulen sammenlignes med.

Hvorfor bruke en delspørring?

Før vi ser på de forskjellige typene, er det nyttig å vite når en delspørring fortjener sin plass i en setning.

En delspørring svarer på et spørsmål der filterverdien ikke er kjent på forhånd. Den må beregnes fra selve dataene. En vanlig kundeklage hos MyFlix Video Library er det lave antallet filmtitler, og ledelsen ønsker å kjøpe filmer for kategorien som har færrest titler. Ingen vet hvilken kategori det er før databasen blir spurt, så verdien må beregnes først og deretter brukes som et filter.

Delspørringer er påtractiv av tre praktiske grunner:

• lesbarhet: Hver del av logikken sitter i sin egen blokk i parentes, så utsagnet leses som en sekvens av små spørsmål i stedet for ett komplekst uttrykk.
• Isolasjon: En intern spørring kan kjøres på egenhånd for å bekrefte at den returnerer forventet verdi, noe som gjør testing og feilsøking mye enklere.
• Fleksibilitet: Det samme mønsteret fungerer i WHERE-, HAVING-, SELECT- og FROM-klausulene, og også i INSERT-, UPDATE- og DELETE-setninger.

Avveiningen er hastighet, som undersøkes i JOIN-sammenligningen senere i denne artikkelen.

Typer av delspørringer i MySQL

MySQL støtter tre typer delspørringer, og typen bestemmes av formen på resultatet som den indre spørringen returnerer. Hver type forklares nedenfor med et fungerende eksempel mot myflixdb-databasen.

1) Skalar delspørring

A skalar delspørring returnerer nøyaktig én rad og én kolonne, som betyr at den returnerer én enkelt verdi. Fordi resultatet er én enkelt verdi, kan den brukes overalt hvor en bokstavelig verdi er tillatt. For å gå tilbake til MyFlix-problemet ovenfor, kan du bruke en spørring som denne:

SELECT category_name FROM categories
WHERE category_id = (SELECT MIN(category_id) FROM movies);

Det gir et resultat:

La oss se hvordan denne spørringen fungerer.

Som utførelsesdiagrammet viser, MySQL første løp SELECT MIN(category_id) FROM movies, mottar én verdi, og kjører først deretter den ytre spørringen med den verdien på plass. Fordi én enkelt verdi returneres, er de tillatte operatorene standard sammenligningssettet: =, <> (eller !=), >, >=, <og <=.

💡 Tips: Hvis en delspørring plasseres etter = returnerer mer enn én rad, MySQL gir feil 1242, Delspørring returnerer mer enn én radBytt operatøren til IN, eller stramme inn den indre WHERE-klausulen.

2) Radunderspørring

A radunderspørring returnerer også en enkelt rad, men den raden kan inneholde mer enn én kolonne. Den ytre spørringen sammenligner derfor en rad med verdier mot en radkonstruktør i stedet for mot en enkelt verdi.

SELECT full_names, contact_number FROM members
WHERE (membership_number, gender) = (SELECT membership_number, gender FROM members WHERE full_names = 'Janet Jones');

De tillatte operatorene er de samme sammenligningsoperatorene som er oppført ovenfor, brukt på hele raden samtidig.

3) Tabellunderspørring

A tabellunderspørring returnerer flere rader, og ofte flere kolonner, så den ytre spørringen må bruke en settoperator som IN, NOT IN, ANY, ALLeller EXISTS.

La oss si at du vil ha navn og telefonnumre til medlemmer som har leid en film og ennå ikke har returnert den, slik at du kan ringe dem opp for å gi dem en påminnelse. Du kan bruke en spørring som denne:

SELECT full_names, contact_number FROM members
WHERE membership_number IN (SELECT membership_number FROM movierentals WHERE return_date IS NULL);

La oss se hvordan denne spørringen fungerer.

I dette tilfellet returnerer den indre spørringen mer enn ett resultat, så listen over medlemsnumre sendes til IN operatoren, og alle samsvarende medlemmer returneres.

Nestende underspørringer flere nivåer dypt

Så langt har du sett to nivåer. En delspørring kan også inneholde en annen delspørring, som produserer en trippelnestet setning.

Anta at ledelsen ønsker å belønne det medlemmet som betaler best. Vi kan kjøre en spørring som denne:

SELECT full_names FROM members
WHERE membership_number = (SELECT membership_number FROM payments
    WHERE amount_paid = (SELECT MAX(amount_paid) FROM payments));

Den innerste spørringen finner den største betalingen, den midterste spørringen konverterer dette beløpet til et medlemsnummer, og den ytre spørringen konverterer medlemsnummeret til et navn. Spørringen ovenfor gir følgende resultat:

Slik bruker du delspørringer med INSERT, UPDATE og DELETE

Delspørringer er ikke begrenset til SELECT-setninger. Det samme mønsteret i parentes fungerer i datamodifikasjonssetninger, som gjør at et helt sett med rader kan endres i én omgang uten å opprette en midlertidig tabell.

INSERT med en delspørring. En delspørring kan levere radene som settes inn, som kopierer data fra én tabell til en annen. Kolonnelisten i SELECT må være på linje med kolonnelisten i INSERT.

INSERT INTO vip_members (membership_number, full_names)
SELECT membership_number, full_names FROM members
WHERE membership_number IN (SELECT membership_number FROM payments WHERE amount_paid > 5000);

OPPDATERING med en delspørring. Her bestemmer den interne spørringen hvilke rader som berøres. Eksemplet nedenfor markerer alle medlemmer hvis utleie fortsatt er utestående.

UPDATE members
SET reminder_sent = 1
WHERE membership_number IN (SELECT membership_number FROM movierentals WHERE return_date IS NULL);

SLETT med en delspørring. Den samme ideen fjerner rader som oppfyller en betingelse i en andre tabell.

DELETE FROM members
WHERE membership_number NOT IN (SELECT membership_number FROM movierentals);

⚠️ Advarsel: MySQL tillater ikke at en setning endrer en tabell og velger fra den samme tabellen i en delspørring i FROM-klausulen. Hvis feil 1093 vises, pakk inn den indre spørringen i en avledet tabell, for eksempel SELECT * FROM (SELECT ...) AS t, Slik at MySQL materialiserer resultatet før endringen blir brukt. Det er også lurt å kjøre den indre SELECT-en på egenhånd først, og å bekrefte radantallet før du kjører en OPPDATERING eller SLETT i produksjon.

Underspørringer vs. sammenføyninger

Både en delspørring og en JOIN kan kombinere informasjon fra mer enn én tabell, så det naturlige spørsmålet er hvilken man skal gripe etter.

Sammenlignet med koblinger er underspørringer enkle å bruke og lettlese. De er ikke så kompliserte som tiltrer, og derfor brukes de ofte av SQL nybegynnere.

Men underspørringer har ytelsesproblemer. Å bruke en join i stedet for en underspørring kan noen ganger gi deg opptil 500 ganger bedre ytelse, fordi optimaliseringsprogrammet kan løse en join i én omgang i stedet for å evaluere den indre setningen gjentatte ganger.

Gitt et valg, anbefales det å bruke en JOIN over en underspørring. Underspørringer bør bare brukes som en reserveløsning når du ikke kan bruke en JOIN-operasjon for å oppnå det ovennevnte.

Delspørringer er også enkle å dele opp i enkeltstående logiske komponenter, noe som er veldig nyttig når testing og feilsøking av spørringene.

Spørsmål og svar