MySQL Poddotaz s příklady

Co je to poddotaz v SQL?

A poddotaz je dotaz SELECT, který je obsažen uvnitř jiného dotazu. Vnitřní výběrový dotaz se obvykle používá k určení výsledků vnějšího výběrového dotazu, takže databáze nejprve vyhodnotí vnitřní příkaz a poté jeho výstup předává dál.

Vnitřní dotaz se nazývá vnitřní dotaz nebo vnořený dotaz a příkaz, který jej obsahuje, se nazývá vnější dotazPodívejme se na syntaxi poddotazů.

Výše uvedený diagram znázorňuje obecný tvar příkazu: vnější SELECT poskytuje sloupce, které chcete zobrazit, a vnitřní SELECT v hranatých závorkách poskytuje hodnotu nebo seznam hodnot, se kterými klauzule WHERE porovnává.

Proč používat poddotaz?

Než se podíváme na různé typy, je užitečné vědět, kdy si poddotaz zaslouží své místo v příkazu.

Poddotaz odpovídá na otázku, jejíž hodnota filtru není předem známa. Musí být vypočítána ze samotných dat. Častou stížností zákazníků ve videotéce MyFlix je nízký počet filmových titulů a vedení chce nakupovat filmy pro kategorii, která má nejmenší počet titulů. Nikdo neví, o jakou kategorii se jedná, dokud se databáze nedotazuje, takže je nutné nejprve vypočítat hodnotu a poté ji použít jako filtr.

Poddotazy jsou natracze tří praktických důvodů:

• Čitelnost: Každá část logiky je umístěna ve vlastním bloku v hranatých závorkách, takže se příkaz čte spíše jako posloupnost malých otázek než jako jeden složitý výraz.
• Izolace: Vnitřní dotaz lze spustit samostatně, aby se ověřilo, že vrací očekávanou hodnotu, což značně usnadňuje testování a ladění.
• Flexibilita: Stejný vzorec funguje v klauzulích WHERE, HAVING, SELECT a FROM a také uvnitř příkazů INSERT, UPDATE a DELETE.

Nevýhodou je rychlost, která je zkoumána v porovnání JOIN dále v tomto článku.

Typy poddotazů v MySQL

MySQL podporuje tři typy poddotazů a typ je určen tvarem výsledku, který vnitřní dotaz vrací. Každý typ je vysvětlen níže s funkčním příkladem pro databázi myflixdb.

1) Skalární poddotaz

A skalární poddotaz vrací přesně jeden řádek a jeden sloupec, což znamená, že vrací jednu hodnotu. Protože výsledkem je jedna hodnota, lze ji použít kdekoli, kde je povolena literální hodnota. Vrátíme-li se k výše uvedenému problému MyFlix, můžete použít dotaz podobný tomuto:

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

Dává to výsledek:

Podívejme se, jak tento dotaz funguje.

Jak ukazuje prováděcí diagram, MySQL první běhy SELECT MIN(category_id) FROM movies, přijme jednu hodnotu a teprve poté spustí vnější dotaz s touto hodnotou. Protože je vrácena jedna hodnota, povolené operátory jsou standardní sada porovnání: =, <> (nebo !=), >, >=, <, a <=.

💡 Tip: Pokud je poddotaz umístěn za = vrací více než jeden řádek, MySQL vyvolá chybu 1242, Poddotaz vrací více než 1 řádekPřepněte operátora na INnebo zpřesněte vnitřní klauzuli WHERE.

2) Poddotaz na řádek

A poddotaz řádku také vrací jeden řádek, ale tento řádek může obsahovat více než jeden sloupec. Vnější dotaz proto porovnává řádek hodnot s konstruktorem řádků, nikoli s jednou hodnotou.

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

Povolené operátory jsou stejné porovnávací operátory uvedené výše, aplikované na celý řádek najednou.

3) Poddotaz tabulky

A poddotaz tabulky vrací více řádků a často i více sloupců, takže vnější dotaz musí použít operátor množiny, například IN, NOT IN, ANY, ALLnebo EXISTS.

Předpokládejme, že chcete znát jména a telefonní čísla členů, kteří si film půjčili a ještě ho nevrátili, abyste jim mohli zavolat a připomenout jim to. Můžete použít dotaz podobný tomuto:

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

Podívejme se, jak tento dotaz funguje.

V tomto případě vnitřní dotaz vrátí více než jeden výsledek, takže seznam čísel členství je předán IN operátor a každý odpovídající člen je vrácen.

Vnořování poddotazů do několika úrovní

Doposud jste viděli dvě úrovně. Poddotaz může také obsahovat další poddotaz, který vygeneruje trojitě vnořený příkaz.

Předpokládejme, že vedení chce odměnit nejlépe platícího člena. Můžeme spustit dotaz podobný tomuto:

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

Vnitřní dotaz vyhledá nejvyšší platbu, prostřední dotaz převede tuto částku na členské číslo a vnější dotaz převede členské číslo na jméno. Výše ​​uvedený dotaz dává následující výsledek:

Jak používat poddotazy s příkazy INSERT, UPDATE a DELETE

Poddotazy nejsou omezeny pouze na příkazy SELECT. Stejný vzorec závorek funguje i uvnitř příkazů pro modifikaci dat, což umožňuje změnit celou sadu řádků v jednom průchodu bez vytvoření dočasné tabulky.

INSERT s poddotazem. Poddotaz může poskytnout vkládané řádky, které kopírují data z jedné tabulky do druhé. Seznam sloupců příkazu SELECT se musí shodovat se seznamem sloupců příkazu 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);

AKTUALIZACE s poddotazem. Zde vnitřní dotaz rozhoduje, kterých řádků se dotkne. Následující příklad označuje každého člena, jehož nájemné je stále nevyřízené.

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

DELETE s poddotazem. Stejný postup odstraňuje řádky, které splňují podmínku uvedenou v druhé tabulce.

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

Warning️ Varování: MySQL neumožňuje příkazu modifikovat tabulku a vybírat ze stejné tabulky uvnitř poddotazu v klauzuli FROM. Pokud se zobrazí chyba 1093, zabalte vnitřní dotaz do odvozené tabulky, například SELECT * FROM (SELECT ...) AS t, Tak, že MySQL materializuje výsledek před provedením změny. Je také moudré nejprve spustit vnitřní SELECT samostatně a ověřit počet řádků před spuštěním UPDATE nebo DELETE ve výrobě.

Poddotazy vs. spojení

Jak poddotaz, tak i operace JOIN mohou kombinovat informace z více než jedné tabulky, takže je přirozenou otázkou, po které z nich sáhnout.

Ve srovnání se spojeními se poddotazy snadno používají a snadno se čtou. Nejsou tak složité jako Připojuje, a proto jsou často používány SQL začátečníci.

Poddotazy však mají problémy s výkonem. Použití spojení místo poddotazu může někdy poskytnout až 500násobné zvýšení výkonu, protože optimalizátor dokáže vyřešit spojení v jednom průchodu, místo aby opakovaně vyhodnocoval vnitřní příkaz.

Pokud je na výběr, doporučuje se použít JOIN přes dílčí dotaz. Poddotazy by se měly používat pouze jako záložní řešení, pokud nelze k dosažení výše uvedeného použít operaci JOIN.

Poddotazy lze také snadno rozdělit na jednotlivé logické komponenty, což je velmi užitečné při Testování a ladění dotazů.

Nejčastější dotazy