MySQL Alkérdés példákkal

Mi az az alkérdés az SQL-ben?

A allekérdezés egy SELECT lekérdezés, amely egy másik lekérdezésen belül található. A belső választó lekérdezést általában a külső választó lekérdezés eredményeinek meghatározására használják, így az adatbázis először a belső utasítást értékeli ki, majd a kimenetét továbbítja felfelé.

A belső lekérdezést az ún. belső lekérdezés vagy beágyazott lekérdezés, és az azt tartalmazó utasítás az úgynevezett külső lekérdezésNézzük meg az alkérés szintaxisát.

A fenti ábra az utasítás általános alakját mutatja: a külső SELECT adja meg a megtekinteni kívánt oszlopokat, a szögletes zárójelben lévő belső SELECT pedig az értéket vagy az értékek listáját, amelyekkel a WHERE záradék összehasonlít.

Miért érdemes alkérdést használni?

Mielőtt a különböző típusokat megvizsgálnánk, érdemes tudni, hogy egy allekérdezés mikor vívja ki a helyét egy utasításban.

Egy allekérdezés olyan kérdésre ad választ, amelynek a szűrőértéke előre nem ismert. Ezt magukból az adatokból kell kiszámítani. A MyFlix Videótárban gyakori vásárlói panasz a filmcímek alacsony száma, és a vezetőség a legkevesebb címmel rendelkező kategóriába tartozó filmeket akarja megvásárolni. Senki sem tudja, hogy melyik kategória az, amíg az adatbázist le nem kérdezik, ezért először az értéket kell kiszámítani, majd szűrőként használni.

Az alkérdések itt találhatók:trachárom gyakorlati okból kifolyólag:

• Olvashatóság: A logika minden része saját szögletes zárójelben található, így az állítás egy összetett kifejezés helyett inkább kis kérdések sorozataként olvasható.
• Szigetelés: Egy belső lekérdezés önmagában is futtatható annak megerősítésére, hogy a várt értéket adja vissza, ami sokkal könnyebbé teszi a tesztelést és a hibakeresést.
• Rugalmasság: Ugyanez a minta működik a WHERE, HAVING, SELECT és FROM záradékokban, valamint az INSERT, UPDATE és DELETE utasításokban is.

A kompromisszum a sebesség, amelyet a cikk későbbi részében, a JOIN összehasonlításban vizsgálunk meg.

Az alkérdések típusai a MySQL

MySQL háromféle alkérést támogat, és a típust a belső lekérdezés által visszaadott eredmény alakja határozza meg. Mindegyik típust az alábbiakban egy myflixdb adatbázison alapuló működő példával ismertetünk.

1) Skaláris alkérdés

A skaláris allekérdezés pontosan egy sort és egy oszlopot ad vissza, ami azt jelenti, hogy egyetlen értéket ad vissza. Mivel az eredmény egyetlen érték, bárhol használható, ahol literálérték megengedett. Visszatérve a fenti MyFlix problémára, használhat egy ehhez hasonló lekérdezést:

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

Eredményt ad:

Lássuk, hogyan működik ez a lekérdezés.

Ahogy a végrehajtási ábra is mutatja, MySQL első futások SELECT MIN(category_id) FROM movies, egy értéket kap, és csak ezután futtatja a külső lekérdezést ezzel az értékkel. Mivel egyetlen értéket ad vissza, a megengedett operátorok a standard összehasonlító halmaz: =, <> (Vagy !=), >, >=, <és <=.

💡 Tipp: Ha egy alkérés a következő után kerül elhelyezésre: = egynél több sort ad vissza, MySQL 1242-es hibát okoz, Az alkérdés egynél több sort ad vissza. Kapcsolja át az operátort erre: IN, vagy szűkítse a belső WHERE záradékot.

2) Sor alkérdés

A sor alkérdés szintén egyetlen sort ad vissza, de ez a sor egynél több oszlopot is tartalmazhat. A külső lekérdezés ezért egy értéksort hasonlít össze egy sorkonstruktorral, nem pedig egyetlen értékkel.

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

Az engedélyezett operátorok ugyanazok az összehasonlító operátorok, mint a fent felsoroltak, de egyszerre az egész sorra alkalmazva.

3) Tábla alkérdés

A tábla alkérdés több sort, és gyakran több oszlopot is visszaad, ezért a külső lekérdezésnek egy halmazoperátort kell használnia, például IN, NOT IN, ANY, ALLvagy EXISTS.

Tegyük fel, hogy szükséged van azoknak a tagoknak a nevére és telefonszámára, akik kikölcsönöztek egy filmet, de még nem vitték vissza, hogy felhívhasd őket emlékeztetőül. Használhatsz egy ehhez hasonló lekérdezést:

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

Lássuk, hogyan működik ez a lekérdezés.

Ebben az esetben a belső lekérdezés egynél több eredményt ad vissza, így a tagsági számok listája átadásra kerül a IN operátor, és minden egyező tag visszaadásra kerül.

Több szint mélyen beágyazott alkérdések

Eddig két szintet láttunk. Egy allekérdezés tartalmazhat egy másik allekérdezést is, amely egy háromszorosan beágyazott utasítást hoz létre.

Tegyük fel, hogy a vezetőség a legjobban fizető tagot szeretné jutalmazni. Futtathatunk egy ehhez hasonló lekérdezést:

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

A legbelső lekérdezés megkeresi a legnagyobb befizetést, a középső lekérdezés ezt az összeget tagsági számmá alakítja, a külső lekérdezés pedig a tagsági számot névvé alakítja. A fenti lekérdezés a következő eredményt adja:

Hogyan használjuk az alkérdéseket az INSERT, UPDATE és DELETE utasításokkal?

Az alkérések nem korlátozódnak a SELECT utasításokra. Ugyanez a szögletes zárójeles minta működik az adatmódosító utasításokon belül is, amely lehetővé teszi egy sor teljes halmazának egyetlen menetben történő módosítását ideiglenes tábla létrehozása nélkül.

INSERT egy allekérdezéssel. Egy allekérdezés képes megadni a beszúrt sorokat, amelyek adatokat másolnak egyik táblából a másikba. A SELECT oszloplistájának egyeznie kell az INSERT oszloplistájával.

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

FRISSÍTÉS egy allekérdezéssel. Itt a belső lekérdezés dönti el, hogy mely sorokat érintse meg. Az alábbi példa minden olyan tagot megjelöl, akinek a bérleti díja még függőben van.

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

TÖRLÉS egy allekérdezéssel. Ugyanez az ötlet eltávolítja azokat a sorokat, amelyek megfelelnek egy második táblázatban szereplő feltételnek.

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

⚠️ Figyelmeztetés: MySQL nem engedélyezi, hogy egy utasítás módosítson egy táblát, és ugyanabból a táblából válasszon egy allekérdezésen belül a FROM záradékban. Ha a 1093-as hiba jelenik meg, akkor a belső lekérdezést egy származtatott táblába kell becsomagolni, például SELECT * FROM (SELECT ...) AS tÚgy, hogy MySQL materializálja az eredményt, mielőtt a módosítás érvénybe lépne. Érdemes először a belső SELECT-et külön lefuttatni, és a sorok számát megerősíteni, mielőtt egy UPDATE vagy DELETE termelésben.

Alkérések vs. illesztések

Mind az alkérések, mind a JOIN-ok több táblából is képesek információkat kombinálni, így természetes kérdés, hogy melyiket érdemes használni.

Az illesztésekhez képest az alkérések egyszerűen használhatók és könnyen olvashatók. Nem olyan bonyolultak, mint csatlakozik, és ezért gyakran használják őket SQL kezdők.

Az alkéréseknek azonban teljesítménybeli problémáik vannak. Az alkérés helyett illesztés használata akár 500-szoros teljesítménynövekedést is eredményezhet, mivel az optimalizáló egyetlen menetben fel tudja oldani az illesztést a belső utasítás ismételt kiértékelése helyett.

Ha van választási lehetőség, javasolt a JOIN használata egy allekérdezésnél. Az alkéréseket csak tartalék megoldásként szabad használni, ha a JOIN művelettel nem lehet a fentieket elérni.

Az alkérések könnyen bonthatók egyetlen logikai komponensre, ami nagyon hasznos, amikor tesztelés és a lekérdezések hibakeresése.

GYIK