MySQL Delfråga med exempel

Vad är en delfråga i SQL?

A underfråga är en SELECT-fråga som finns inuti en annan fråga. Den inre SELECT-frågan används vanligtvis för att bestämma resultaten av den yttre SELECT-frågan, så databasen utvärderar den inre satsen först och skickar sedan dess utdata uppåt.

Den inre frågan kallas inre fråga eller kapslad fråga, och satsen som innehåller den kallas extern frågaLåt oss titta på syntaxen för underfrågen.

Diagrammet ovan visar den allmänna formen på satsen: den yttre SELECT-satsen anger de kolumner du vill se, och den inre SELECT-satsen inom parentes anger värdet eller listan med värden som WHERE-satsen jämförs mot.

Varför använda en delfråga?

Innan man tittar på de olika typerna är det bra att veta när en delfråga förtjänar sin plats i en sats.

En delfråga besvarar en fråga vars filtervärde inte är känt i förväg. Det måste beräknas från själva informationen. Ett vanligt kundklagomål på MyFlix Video Library är det låga antalet filmtitlar, och ledningen vill köpa filmer för den kategori som har minst antal titlar. Ingen vet vilken kategori det är förrän databasen tillfrågas, så värdet måste beräknas först och sedan användas som ett filter.

Delfrågor finns påtracav tre praktiska skäl:

• Läsbarhet: Varje del av logiken sitter i ett eget block inom parentes, så påståendet läses som en sekvens av små frågor snarare än ett komplext uttryck.
• Isolering: En intern fråga kan köras separat för att bekräfta att den returnerar det förväntade värdet, vilket gör testning och felsökning mycket enklare.
• Flexibilitet: Samma mönster fungerar i WHERE-, HAVING-, SELECT- och FROM-klausulerna, och även inuti INSERT-, UPDATE- och DELETE-satser.

Avvägningen är hastighet, vilket undersöks i JOIN-jämförelsen senare i den här artikeln.

Typer av delfrågor i MySQL

MySQL stöder tre typer av delfrågor, och typen bestäms av formen på resultatet som den interna frågan returnerar. Varje typ förklaras nedan med ett fungerande exempel mot myflixdb-databasen.

1) Skalär delfråga

A skalär delfråga returnerar exakt en rad och en kolumn, vilket innebär att den returnerar ett enda värde. Eftersom resultatet är ett enda värde kan det användas överallt där ett literalt värde är tillåtet. För att återgå till MyFlix-problemet ovan kan du använda en fråga som den här:

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

Det ger ett resultat:

Låt oss se hur den här frågan fungerar.

Som utförandediagrammet visar, MySQL första löpningarna SELECT MIN(category_id) FROM movies, tar emot ett värde och kör först sedan den yttre frågan med det värdet på plats. Eftersom ett enda värde returneras är de tillåtna operatorerna standardjämförelsemängden: =, <> (eller !=), >, >=, <och <=.

💡 Tips: Om en delfråga placeras efter = returnerar mer än en rad, MySQL ger fel 1242, Delfrågan returnerar mer än 1 rad. Växla operatorn till IN, eller skärp den inre WHERE-klausulen.

2) Radunderfråga

A radunderfråga returnerar också en enda rad, men den raden kan innehålla mer än en kolumn. Den yttre frågan jämför därför en rad med värden mot en radkonstruktor snarare än mot ett enda värde.

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

De tillåtna operatorerna är samma jämförelseoperatorer som listas ovan, tillämpade på hela raden samtidigt.

3) Tabellunderfråga

A tabellunderfråga returnerar flera rader, och ofta flera kolumner, så den yttre frågan måste använda en set-operator som IN, NOT IN, ANY, ALL, eller EXISTS.

Anta att du vill ha namn och telefonnummer till medlemmar som har hyrt en film och ännu inte har lämnat tillbaka den, så att du kan ringa dem för att ge en påminnelse. Du kan använda en fråga som den här:

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

Låt oss se hur den här frågan fungerar.

I det här fallet returnerar den inre frågan mer än ett resultat, så listan över medlemsnummer skickas till IN operatorn och varje matchande medlem returneras.

Kapsla underfrågor flera nivåer djupt

Hittills har du sett två nivåer. En delfråga kan också innehålla en annan delfråga, som producerar en trippelkapslad sats.

Anta att ledningen vill belöna den högst betalande medlemmen. Vi kan köra en fråga som denna:

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

Den innersta frågan hittar den största betalningen, den mellersta frågan konverterar det beloppet till ett medlemsnummer och den yttre frågan konverterar medlemsnumret till ett namn. Ovanstående fråga ger följande resultat:

Hur man använder delfrågor med INSERT, UPDATE och DELETE

Delfrågor är inte begränsade till SELECT-satser. Samma mönster inom parentes fungerar inuti datamodifieringssatser, vilket gör att en hel uppsättning rader kan ändras i ett svep utan att skapa en tillfällig tabell.

INSERT med en delfråga. En delfråga kan tillhandahålla de rader som infogas, vilket kopierar data från en tabell till en annan. Kolumnlistan i SELECT måste vara i linje med kolumnlistan 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);

UPPDATERA med en delfråga. Här avgör den interna frågan vilka rader som berörs. Exemplet nedan markerar varje medlem vars hyra fortfarande är utestående.

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

TA BORT med en delfråga. Samma idé tar bort rader som uppfyller ett villkor i en andra tabell.

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

⚠️ Varning: MySQL tillåter inte en sats att ändra en tabell och välja från samma tabell inuti en underfråga i FROM-satsen. Om fel 1093 visas, radbryt den inre frågan i en härledd tabell, till exempel SELECT * FROM (SELECT ...) AS t, Så att MySQL materialiserar resultatet innan ändringen tillämpas. Det är också klokt att köra den inre SELECT-funktionen på egen hand först, och att bekräfta radantalet, innan man kör en UPPDATERING eller ett RADERA i produktion.

Delfrågor kontra kopplingar

Både en delfråga och en JOIN kan kombinera information från mer än en tabell, så den naturliga frågan är vilken man ska välja.

Jämfört med joins är delfrågor enkla att använda och lättlästa. De är inte lika komplicerade som Fogar, och därför används de ofta av SQL nybörjare.

Men underfrågor har prestandaproblem. Att använda en join istället för en delfråga kan ibland ge upp till 500 gånger högre prestanda, eftersom optimeraren kan lösa en join i ett enda steg istället för att utvärdera den inre satsen upprepade gånger.

Givet ett val rekommenderas det att använda en JOIN över en underfråga. Delfrågor bör endast användas som en reservlösning när du inte kan använda en JOIN-operation för att uppnå ovanstående.

Delfrågor är också enkla att dela upp i enskilda logiska komponenter, vilket är mycket användbart när testning och felsöka frågorna.

Vanliga frågor