MySQL Podupit s primjerima

Što je podupit u SQL-u?

A podupit je SELECT upit koji se nalazi unutar drugog upita. Unutarnji SELECT upit obično se koristi za određivanje rezultata vanjskog SELECT upita, pa baza podataka prvo procjenjuje unutarnju naredbu, a zatim prosljeđuje njezin izlaz prema gore.

Unutarnji upit se naziva unutarnji upit ili ugniježđeni upit, a naredba koja ga sadrži naziva se vanjski upitPogledajmo sintaksu podupita.

Gornji dijagram prikazuje opći oblik naredbe: vanjski SELECT daje stupce koje želite vidjeti, a unutarnji SELECT u zagradama daje vrijednost ili popis vrijednosti s kojima se klauzula WHERE uspoređuje.

Zašto koristiti podupit?

Prije nego što pogledamo različite tipove, korisno je znati kada podupit zaslužuje svoje mjesto u izrazu.

Podupit odgovara na pitanje čija vrijednost filtera nije unaprijed poznata. Mora se izračunati iz samih podataka. Uobičajena pritužba kupaca u MyFlix videoteci je mali broj naslova filmova, a uprava želi kupiti filmove za kategoriju koja ima najmanji broj naslova. Nitko ne zna koja je to kategorija dok se ne zatraži od baze podataka, pa se vrijednost prvo mora izračunati, a zatim koristiti kao filter.

Podupiti su natraciz tri praktična razloga:

• Čitljivost: Svaki dio logike nalazi se u vlastitom bloku u zagradama, pa se izjava čita kao niz malih pitanja, a ne kao jedan složeni izraz.
• Izolacija: Unutarnji upit može se pokrenuti samostalno kako bi se potvrdilo da vraća očekivanu vrijednost, što znatno olakšava testiranje i otklanjanje pogrešaka.
• Fleksibilnost: Isti obrazac funkcionira u klauzulama WHERE, HAVING, SELECT i FROM, kao i unutar naredbi INSERT, UPDATE i DELETE.

Kompromis je brzina, što se ispituje u usporedbi JOIN-a kasnije u ovom članku.

Vrste podupita u MySQL

MySQL podržava tri vrste podupita, a vrsta se određuje oblikom rezultata koji vraća unutarnji upit. Svaka vrsta je objašnjena u nastavku s radnim primjerom na bazi podataka myflixdb.

1) Skalarni podupit

A skalarni podupit vraća točno jedan redak i jedan stupac, što znači da vraća jednu vrijednost. Budući da je rezultat jedna vrijednost, može se koristiti bilo gdje gdje je dopuštena doslovna vrijednost. Vraćajući se na gornji problem MyFlix, možete koristiti upit poput ovog:

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

Daje rezultat:

Pogledajmo kako ovaj upit funkcionira.

Kao što dijagram izvršenja pokazuje, MySQL prve vožnje SELECT MIN(category_id) FROM movies, prima jednu vrijednost i tek tada pokreće vanjski upit s tom vrijednošću. Budući da se vraća jedna vrijednost, dopušteni operatori su standardni skup usporedbi: =, <> (Ili !=), >, >=, <i <=.

💡 Savjet: Ako se podupit postavi nakon = vraća više od jednog reda, MySQL javlja grešku 1242, Podupit vraća više od 1 retkaPrebacite operatora na INili skratite unutarnju klauzulu WHERE.

2) Podupit retka

A podupit retka također vraća jedan redak, ali taj redak može sadržavati više od jednog stupca. Vanjski upit stoga uspoređuje redak vrijednosti s konstruktorom retka, a ne s jednom vrijednošću.

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

Dopušteni operatori su isti operatori usporedbe navedeni gore, primijenjeni na cijeli redak odjednom.

3) Podupit tablice

A podupit tablice vraća više redaka, a često i više stupaca, pa vanjski upit mora koristiti operator skupa kao što je IN, NOT IN, ANY, ALL, ili EXISTS.

Pretpostavimo da želite imena i brojeve telefona članova koji su posudili film i još ga nisu vratili, kako biste ih mogli pozvati i podsjetiti. Možete koristiti upit poput ovog:

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

Pogledajmo kako ovaj upit funkcionira.

U ovom slučaju, unutarnji upit vraća više od jednog rezultata, pa se popis brojeva članstva predaje IN operator i vraća se svaki odgovarajući član.

Ugniježđivanje podupita na nekoliko razina dubine

Do sada ste vidjeli dvije razine. Podupit može sadržavati i drugi podupit, koji proizvodi trostruko ugniježđenu naredbu.

Pretpostavimo da uprava želi nagraditi člana koji najviše plaća. Možemo pokrenuti upit poput ovog:

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

Unutarnji upit pronalazi najveću uplatu, srednji upit pretvara taj iznos u članski broj, a vanjski upit pretvara članski broj u ime. Gornji upit daje sljedeći rezultat:

Kako koristiti podupite s INSERT, UPDATE i DELETE

Podupiti nisu ograničeni na SELECT naredbe. Isti obrazac u zagradama funkcionira unutar naredbi za modifikaciju podataka, što omogućuje promjenu cijelog skupa redaka u jednom prolazu bez stvaranja privremene tablice.

INSERT s podupitorom. Podupit može navesti retke koji se umeću, što kopira podatke iz jedne tablice u drugu. Popis stupaca naredbe SELECT mora se poravnati s popisom stupaca naredbe 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);

AŽURIRANJE s podupitorom. Ovdje unutarnji upit odlučuje koji će se retci dotaknuti. Primjer u nastavku označava svakog člana čiji je najam još uvijek nepodmiren.

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

IZBRIŠI s podupitorom. Ista ideja uklanja retke koji zadovoljavaju uvjet sadržan u drugoj tablici.

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

⚠️ Upozorenje: MySQL ne dopušta naredbi da modificira tablicu i odabire iz iste tablice unutar podupita u FROM klauzuli. Ako se pojavi greška 1093, unutarnji upit omotajte u izvedenu tablicu, na primjer SELECT * FROM (SELECT ...) AS t, Tako da MySQL materijalizira rezultat prije nego što se promjena primijeni. Također je mudro prvo pokrenuti unutarnji SELECT samostalno i potvrditi broj redaka prije pokretanja UPDATE ili DELETE u proizvodnji.

Podupiti u odnosu na spajanja

I podupit i JOIN mogu kombinirati informacije iz više tablica, pa se prirodno postavlja pitanje koju odabrati.

U usporedbi sa spajanjima, podupiti su jednostavni za korištenje i lako ih je čitati. Nisu toliko komplicirani kao Pridružuje, i stoga ih često koriste SQL početnici.

Ali podupiti imaju problema s performansama. Korištenje spajanja umjesto podupita ponekad može dati poboljšanje performansi i do 500 puta, jer optimizator može riješiti spajanje u jednom prolazu umjesto da više puta procjenjuje unutarnju naredbu.

Ako imate izbor, preporuča se koristiti JOIN preko pod upita. Podupite treba koristiti samo kao rezervno rješenje kada ne možete koristiti JOIN operaciju za postizanje gore navedenog.

Podupite je također lako razložiti na pojedinačne logičke komponente, što je vrlo korisno kada testiranje i otklanjanje pogrešaka u upitima.

Pitanja i odgovori