MySQL Subquery met voorbeelden

Wat is een subquery in SQL?

A subquery Een SELECT-query is een query die is opgenomen in een andere query. De binnenste SELECT-query wordt meestal gebruikt om de resultaten van de buitenste SELECT-query te bepalen. De database evalueert daarom eerst de binnenste query en geeft de uitvoer ervan vervolgens door aan de bovenliggende query.

De interne query wordt de genoemd interne query of geneste query, en de instructie die deze bevat, wordt de genoemd. buitenste queryLaten we de syntaxis van de subquery eens nader bekijken.

Het bovenstaande diagram toont de algemene structuur van de instructie: de buitenste SELECT-instructie geeft de kolommen op die u wilt zien, en de binnenste SELECT-instructie tussen haakjes geeft de waarde of de lijst met waarden op waarmee de WHERE-clausule vergelijkt.

Waarom een ​​subquery gebruiken?

Voordat we de verschillende typen bekijken, is het handig om te weten wanneer een subquery een plek in een statement verdient.

Een subquery beantwoordt een vraag waarvan de filterwaarde niet van tevoren bekend is. Deze moet worden berekend op basis van de gegevens zelf. Een veelvoorkomende klacht van klanten bij de MyFlix-videotheek is het lage aantal filmtitels, en het management wil films aanschaffen voor de categorie met het minste aantal titels. Niemand weet welke categorie dat is totdat de database wordt geraadpleegd, dus de waarde moet eerst worden berekend en vervolgens als filter worden gebruikt.

Subquery's bevinden zich optracom drie praktische redenen:

• Leesbaarheid: Elk onderdeel van de logica staat in een eigen blok tussen haakjes, waardoor de bewering leest als een reeks kleine vragen in plaats van één complexe uitdrukking.
• Isolatie: Een interne query kan afzonderlijk worden uitgevoerd om te bevestigen dat deze de verwachte waarde retourneert, wat testen en debuggen aanzienlijk vereenvoudigt.
• Flexibiliteit: Hetzelfde patroon werkt in de WHERE-, HAVING-, SELECT- en FROM-clausules, en ook binnen INSERT-, UPDATE- en DELETE-instructies.

De afweging is snelheid, wat later in dit artikel wordt onderzocht in de vergelijking met JOIN.

Soorten subquery's in MySQL

MySQL Het systeem ondersteunt drie typen subquery's, waarbij het type wordt bepaald door de structuur van het resultaat dat de interne query retourneert. Elk type wordt hieronder uitgelegd met een werkend voorbeeld aan de hand van de myflixdb-database.

1) Scalaire subquery

A scalaire subquery Het retourneert precies één rij en één kolom, wat betekent dat het één enkele waarde retourneert. Omdat het resultaat een enkele waarde is, kan het overal worden gebruikt waar een letterlijke waarde is toegestaan. Terugkerend naar het MyFlix-probleem hierboven, kunt u een query zoals deze gebruiken:

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

Het levert het volgende resultaat op:

Laten we eens kijken hoe deze query werkt.

Zoals het uitvoeringsdiagram laat zien, MySQL eerste runs SELECT MIN(category_id) FROM moviesDe query ontvangt één waarde en voert pas daarna de buitenste query uit met die waarde. Omdat er slechts één waarde wordt geretourneerd, zijn de toegestane operatoren de standaard vergelijkingsoperatoren: =, <> (of !=), >, >=, <en <=.

💡Tip: Als een subquery na = retourneert meer dan één rij, MySQL Geeft foutcode 1242, De subquery retourneert meer dan 1 rij.Schakel de operator over naar IN, of de binnenste WHERE-clausule aanscherpen.

2) Rij-subquery

A rij-subquery Ook deze query retourneert één rij, maar die rij kan meerdere kolommen bevatten. De buitenste query vergelijkt daarom een ​​rij met waarden met een rijconstructor in plaats van met een enkele waarde.

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

De toegestane operatoren zijn dezelfde vergelijkingsoperatoren als hierboven vermeld, toegepast op de hele rij tegelijk.

3) Tabel-subquery

A tabelsubquery retourneert meerdere rijen en vaak ook meerdere kolommen, dus de buitenste query moet een set-operator gebruiken zoals IN, NOT IN, ANY, ALLof EXISTS.

Stel dat u de namen en telefoonnummers wilt weten van leden die een film hebben gehuurd en deze nog niet hebben teruggebracht, zodat u hen kunt bellen om hen eraan te herinneren. U kunt hiervoor een query gebruiken zoals deze:

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

Laten we eens kijken hoe deze query werkt.

In dit geval levert de interne query meer dan één resultaat op, dus de lijst met lidmaatschapsnummers wordt doorgegeven aan de IN De operator en elk overeenkomend lid worden geretourneerd.

Geneste subquery's op meerdere niveaus diep

Tot nu toe heb je twee niveaus gezien. Een subquery kan ook een andere subquery bevatten, wat resulteert in een drievoudig geneste instructie.

Stel dat het management de best betalende medewerker wil belonen. We kunnen dan een query uitvoeren zoals deze:

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

De binnenste query vindt de grootste betaling, de middelste query zet dat bedrag om in een lidmaatschapsnummer en de buitenste query zet het lidmaatschapsnummer om in een naam. De bovenstaande query geeft het volgende resultaat:

Hoe subquery's te gebruiken met INSERT, UPDATE en DELETE

Subquery's zijn niet beperkt tot SELECT-instructies. Hetzelfde patroon met haakjes werkt ook binnen instructies voor gegevenswijziging, waardoor een hele set rijen in één keer kan worden gewijzigd zonder een tijdelijke tabel aan te maken.

INSERT met een subquery. Een subquery kan de rijen leveren die worden ingevoegd, waarmee gegevens van de ene tabel naar de andere worden gekopieerd. De kolomlijst van de SELECT-query moet overeenkomen met de kolomlijst van de INSERT-query.

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);

UPDATE met een subquery. De interne query bepaalt hier welke rijen worden aangeraakt. Het onderstaande voorbeeld markeert alle leden van wie de huur nog openstaat.

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

Verwijderen met een subquery. Hetzelfde principe verwijdert rijen die voldoen aan een voorwaarde die in een tweede tabel is vastgelegd.

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

⚠️ Waarschuwing: MySQL Het is niet toegestaan ​​dat een instructie een tabel wijzigt en tegelijkertijd een selectie maakt uit dezelfde tabel binnen een subquery in de FROM-clausule. Als fout 1093 verschijnt, moet u de interne query in een afgeleide tabel plaatsen, bijvoorbeeld SELECT * FROM (SELECT ...) AS t, Zodat MySQL Het resultaat wordt zichtbaar voordat de wijziging wordt doorgevoerd. Het is ook verstandig om eerst de interne SELECT-query afzonderlijk uit te voeren en het aantal rijen te controleren voordat de rest van de query wordt uitgevoerd. UPDATE nodig heeft of VERWIJDEREN in de maak.

Subquery's versus joins

Zowel een subquery als een JOIN kunnen informatie uit meerdere tabellen combineren, dus de logische vraag is welke van de twee je het beste kunt gebruiken.

In vergelijking met joins zijn subquery's eenvoudig in gebruik en makkelijk te lezen. Ze zijn niet zo complex als Sluit zich aan bij, en daarom worden ze vaak gebruikt door SQL-beginners.

Subquery's hebben echter prestatieproblemen. Het gebruik van een join in plaats van een subquery kan soms tot wel 500 keer sneller zijn, omdat de optimizer een join in één keer kan verwerken in plaats van de onderliggende instructie herhaaldelijk te evalueren.

Als u de keuze heeft, wordt het aanbevolen om een ​​JOIN over een subquery te gebruiken. Subquery's mogen alleen als noodoplossing worden gebruikt wanneer een JOIN-bewerking niet volstaat om het bovenstaande te bereiken.

Subquery's zijn ook gemakkelijk op te splitsen in afzonderlijke logische componenten, wat erg handig is wanneer het testen van en het debuggen van de queries.

Veelgestelde vragen