MySQL Underforespørgsel med eksempler

Hvad er en underforespørgsel i SQL?

A underforespørgsel er en SELECT-forespørgsel, der er indeholdt i en anden forespørgsel. Den indre SELECT-forespørgsel bruges normalt til at bestemme resultaterne af den ydre SELECT-forespørgsel, så databasen evaluerer den indre sætning først og sender derefter dens output opad.

Den indre forespørgsel kaldes indre forespørgsel eller indlejret forespørgsel, og den sætning, der indeholder den, kaldes ydre forespørgselLad os se på syntaksen for underforespørgsler.

Diagrammet ovenfor viser den generelle form af sætningen: den ydre SELECT angiver de kolonner, du vil se, og den indre SELECT i parentes angiver den værdi eller listen over værdier, som WHERE-klausulen sammenlignes med.

Hvorfor bruge en underforespørgsel?

Før man ser på de forskellige typer, er det nyttigt at vide, hvornår en underforespørgsel fortjener sin plads i en sætning.

En underforespørgsel besvarer et spørgsmål, hvis filterværdi ikke er kendt på forhånd. Den skal beregnes ud fra selve dataene. En almindelig kundeklage hos MyFlix Video Library er det lave antal filmtitler, og ledelsen ønsker at købe film til den kategori, der har det mindste antal titler. Ingen ved, hvilken kategori det er, før databasen bliver spurgt, så værdien skal beregnes først og derefter bruges som et filter.

Underforespørgsler er påtracaf tre praktiske årsager:

• Læsbarhed: Hver del af logikken sidder i sin egen parentes, så udsagnet læses som en række små spørgsmål snarere end ét komplekst udtryk.
• Isolering: En intern forespørgsel kan køres alene for at bekræfte, at den returnerer den forventede værdi, hvilket gør test og fejlfinding langt nemmere.
• Fleksibilitet: Det samme mønster fungerer i WHERE-, HAVING-, SELECT- og FROM-klausulerne, og også i INSERT-, UPDATE- og DELETE-sætninger.

Afvejningen er hastighed, hvilket undersøges i JOIN-sammenligningen senere i denne artikel.

Typer af underforespørgsler i MySQL

MySQL understøtter tre typer underforespørgsler, og typen bestemmes af formen på det resultat, som den indre forespørgsel returnerer. Hver type forklares nedenfor med et fungerende eksempel i forhold til myflixdb-databasen.

1) Skalar underforespørgsel

A skalar underforespørgsel returnerer præcis én række og én kolonne, hvilket betyder, at den returnerer en enkelt værdi. Da resultatet er en enkelt værdi, kan den bruges overalt, hvor en literal værdi er tilladt. For at vende tilbage til MyFlix-problemet ovenfor kan du bruge en forespørgsel som denne:

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

Det giver et resultat:

Lad os se, hvordan denne forespørgsel fungerer.

Som udførelsesdiagrammet viser, MySQL første løb SELECT MIN(category_id) FROM movies, modtager én værdi og kører først derefter den ydre forespørgsel med den værdi på plads. Da en enkelt værdi returneres, er de tilladte operatorer standardsammenligningssættet: =, <> (eller !=), >, >=, <og <=.

💡 Tip: Hvis en underforespørgsel placeres efter = returnerer mere end én række, MySQL giver fejl 1242, Underforespørgsel returnerer mere end 1 rækkeSkift operatøren til IN, eller stramme den indre WHERE-klausul.

2) Rækkeunderforespørgsel

A rækkeunderforespørgsel returnerer også en enkelt række, men den række kan indeholde mere end én kolonne. Den ydre forespørgsel sammenligner derfor en række af værdier med en rækkekonstruktør i stedet for med en enkelt værdi.

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

De tilladte operatorer er de samme sammenligningsoperatorer, der er anført ovenfor, anvendt på hele rækken på én gang.

3) Tabelunderforespørgsel

A tabelunderforespørgsel returnerer flere rækker og ofte flere kolonner, så den ydre forespørgsel skal bruge en sætoperator som f.eks. IN, NOT IN, ANY, ALL eller EXISTS.

Forestil dig, at du ønsker navne og telefonnumre på medlemmer, der har lejet en film og endnu ikke har returneret den, så du kan ringe til dem for at give dem en påmindelse. Du kan bruge en forespørgsel som denne:

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

Lad os se, hvordan denne forespørgsel fungerer.

I dette tilfælde returnerer den indre forespørgsel mere end ét resultat, så listen over medlemsnumre gives til IN operatoren, og hvert matchende medlem returneres.

Indlejring af underforespørgsler flere niveauer dybt

Indtil videre har du set to niveauer. En underforespørgsel kan også indeholde en anden underforespørgsel, som producerer en tredobbelt indlejret sætning.

Antag at ledelsen ønsker at belønne det medlem, der betaler bedst. Vi kan køre en forespørgsel 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 inderste forespørgsel finder den største betaling, den midterste forespørgsel konverterer dette beløb til et medlemsnummer, og den ydre forespørgsel konverterer medlemsnummeret til et navn. Ovenstående forespørgsel giver følgende resultat:

Sådan bruger du underforespørgsler med INSERT, UPDATE og DELETE

Underforespørgsler er ikke begrænset til SELECT-sætninger. Det samme mønster i parentes fungerer i datamodifikationssætninger, hvilket gør det muligt at ændre et helt sæt rækker i én omgang uden at oprette en midlertidig tabel.

INSERT med en underforespørgsel. En underforespørgsel kan levere de rækker, der indsættes, hvilket kopierer data fra én tabel til en anden. Kolonnelisten i SELECT skal flugte 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);

OPDATER med en underforespørgsel. Her bestemmer den indre forespørgsel, hvilke rækker der berøres. Eksemplet nedenfor markerer alle medlemmer, hvis lejemål stadig er udestående.

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

SLET med en underforespørgsel. Den samme idé fjerner rækker, der opfylder en betingelse i en anden tabel.

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

⚠️ Advarsel: MySQL tillader ikke en sætning at ændre en tabel og vælge fra den samme tabel i en underforespørgsel i FROM-klausulen. Hvis fejl 1093 vises, skal den indre forespørgsel ombrydes i en afledt tabel, for eksempel SELECT * FROM (SELECT ...) AS t, Således at MySQL materialiserer resultatet, før ændringen anvendes. Det er også klogt at køre den indre SELECT alene først og bekræfte rækkeantallet, før du kører en OPDATER eller SLET i produktion.

Underforespørgsler vs. joins

Både en underforespørgsel og en JOIN kan kombinere information fra mere end én tabel, så det naturlige spørgsmål er, hvilken man skal vælge.

Sammenlignet med joins er underforespørgsler enkle at bruge og lette at læse. De er ikke så komplicerede som Sammenføjninger, og derfor bruges de ofte af SQL begyndere.

Men underforespørgsler har problemer med ydeevnen. Brug af en join i stedet for en underforespørgsel kan til tider give dig op til 500 gange bedre ydeevne, fordi optimeringsværktøjet kan løse en join i en enkelt omgang i stedet for at evaluere den indre sætning gentagne gange.

Givet et valg, anbefales det at bruge en JOIN over en underforespørgsel. Underforespørgsler bør kun bruges som en alternativ løsning, når du ikke kan bruge en JOIN-operation til at opnå ovenstående.

Underforespørgsler er også nemme at opdele i enkelte logiske komponenter, hvilket er meget nyttigt, når test og fejlfinding af forespørgslerne.

Ofte Stillede Spørgsmål