MySQL Podzapytanie z przykładami

Czym jest podzapytanie w SQL?

A podzapytanie to zapytanie SELECT zawarte w innym zapytaniu. Wewnętrzne zapytanie SELECT jest zazwyczaj używane do określania wyników zewnętrznego zapytania SELECT, dlatego baza danych najpierw ocenia instrukcję wewnętrzną, a następnie przekazuje jej wynik do góry.

Wewnętrzne zapytanie nazywa się wewnętrzne zapytanie lub zagnieżdżone zapytanie, a polecenie, które je zawiera, nazywa się zapytanie zewnętrznePrzyjrzyjmy się składni podzapytania.

Powyższy diagram przedstawia ogólną formę instrukcji: zewnętrzne polecenie SELECT zwraca kolumny, które chcesz wyświetlić, a wewnętrzne polecenie SELECT w nawiasach kwadratowych zwraca wartość lub listę wartości, z którymi porównuje się klauzula WHERE.

Dlaczego warto używać podzapytania?

Zanim przyjrzymy się różnym typom, warto wiedzieć, kiedy podzapytanie zdobywa swoje miejsce w poleceniu.

Podzapytanie odpowiada na pytanie, którego wartość filtru nie jest znana z góry. Musi zostać obliczona na podstawie samych danych. Częstą skargą klientów w bibliotece wideo MyFlix jest mała liczba tytułów filmowych, a kierownictwo chce kupować filmy z kategorii o najmniejszej liczbie tytułów. Nikt nie wie, o którą kategorię chodzi, dopóki baza danych nie zostanie zapytana, więc wartość musi zostać najpierw obliczona, a następnie użyta jako filtr.

Podzapytania są wtrackorzystne z trzech praktycznych powodów:

• Czytelność: Każda część logiki znajduje się w osobnym bloku w nawiasach, dzięki czemu oświadczenie można odczytać jako sekwencję małych pytań, a nie jako jedno złożone wyrażenie.
• Izolacja: Można uruchomić niezależne zapytanie wewnętrzne, aby potwierdzić, że zwraca ono oczekiwaną wartość, co znacznie ułatwia testowanie i debugowanie.
• Elastyczność: Ten sam schemat działa w klauzulach WHERE, HAVING, SELECT i FROM, a także wewnątrz poleceń INSERT, UPDATE i DELETE.

Kompromisem jest szybkość, która zostanie omówiona w porównaniu JOIN w dalszej części artykułu.

Typy podzapytań w MySQL

MySQL Obsługuje trzy typy podzapytań, a typ jest określany na podstawie kształtu wyniku zwracanego przez zapytanie wewnętrzne. Każdy typ jest wyjaśniony poniżej wraz z działającym przykładem w bazie danych myflixdb.

1) Podzapytanie skalarne

A podzapytanie skalarne Zwraca dokładnie jeden wiersz i jedną kolumnę, co oznacza, że ​​zwraca pojedynczą wartość. Ponieważ wynik jest pojedynczą wartością, można go użyć wszędzie tam, gdzie dozwolona jest wartość literałowa. Wracając do powyższego problemu MyFlix, możesz użyć zapytania takiego jak to:

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

Daje to wynik:

Zobaczmy jak działa to zapytanie.

Jak pokazuje diagram wykonania, MySQL pierwsze biegi SELECT MIN(category_id) FROM movies, otrzymuje jedną wartość i dopiero wtedy uruchamia zapytanie zewnętrzne z tą wartością. Ponieważ zwracana jest jedna wartość, dozwolone operatory to standardowy zestaw porównań: =, <> (lub !=), >, >=, <, <=.

💡 Wskazówka: Jeśli podzapytanie umieszczone po = zwraca więcej niż jeden wiersz, MySQL podnosi błąd 1242, Podzapytanie zwraca więcej niż 1 wierszPrzełącz operatora na INlub zawęż wewnętrzną klauzulę WHERE.

2) Podzapytanie wierszowe

A podzapytanie wierszowe Zwraca również pojedynczy wiersz, ale wiersz ten może zawierać więcej niż jedną kolumnę. Zapytanie zewnętrzne porównuje zatem wiersz wartości z konstruktorem wiersza, a nie z pojedynczą wartością.

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

Dopuszczalne operatory to takie same operatory porównania, jak te wymienione powyżej, stosowane jednocześnie do całego wiersza.

3) Podzapytanie tabeli

A podzapytanie tabeli zwraca wiele wierszy, a często także wiele kolumn, dlatego zapytanie zewnętrzne musi używać operatora zbiorów, takiego jak IN, NOT IN, ANY, ALLlub EXISTS.

Załóżmy, że chcesz poznać nazwiska i numery telefonów osób, które wypożyczyły film i jeszcze go nie zwróciły, aby móc do nich zadzwonić i przywołać przypomnienie. Możesz użyć zapytania takiego jak to:

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

Zobaczmy jak działa to zapytanie.

W tym przypadku zapytanie wewnętrzne zwraca więcej niż jeden wynik, więc lista numerów członkostwa jest przekazywana do IN operator i zwracany jest każdy pasujący element.

Zagnieżdżanie podzapytań na kilku poziomach głębokości

Do tej pory widziałeś dwa poziomy. Podzapytanie może również zawierać inne podzapytanie, które generuje potrójnie zagnieżdżone polecenie.

Załóżmy, że zarząd chce nagrodzić najlepiej płacącego członka. Możemy uruchomić takie zapytanie:

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

Zapytanie najbardziej wewnętrzne znajduje największą płatność, zapytanie środkowe konwertuje tę kwotę na numer członkostwa, a zapytanie zewnętrzne konwertuje numer członkostwa na nazwę. Powyższe zapytanie daje następujący wynik:

Jak używać podzapytań z poleceniami INSERT, UPDATE i DELETE

Podzapytania nie ograniczają się do instrukcji SELECT. Ten sam schemat w nawiasach działa wewnątrz instrukcji modyfikacji danych, co pozwala na zmianę całego zestawu wierszy w jednym przebiegu bez tworzenia tabeli tymczasowej.

INSERT z podzapytaniem. Podzapytanie może dostarczyć wstawianych wierszy, co powoduje skopiowanie danych z jednej tabeli do drugiej. Lista kolumn instrukcji SELECT musi być zgodna z listą kolumn instrukcji 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);

UPDATE z podzapytaniem. W tym przypadku zapytanie wewnętrzne decyduje, które wiersze zostaną dotknięte. Poniższy przykład oznacza każdego członka, którego wynajem jest nadal nierozliczony.

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

DELETE z podzapytaniem. Ta sama idea usuwa wiersze, które spełniają warunek przechowywany w drugiej tabeli.

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

⚠️ Ostrzeżenie: MySQL Nie pozwala na modyfikację tabeli i wybór z tej samej tabeli w podzapytaniu w klauzuli FROM. Jeśli pojawi się błąd 1093, należy umieścić zapytanie wewnętrzne w tabeli pochodnej, na przykład SELECT * FROM (SELECT ...) AS t, Tak aby MySQL materializuje wynik przed zastosowaniem zmiany. Warto również najpierw uruchomić wewnętrzną instrukcję SELECT i potwierdzić liczbę wierszy przed uruchomieniem Aktualizacja lub DELETE w produkcji.

Podzapytania a łączenia

Zarówno podzapytanie, jak i JOIN mogą łączyć informacje z więcej niż jednej tabeli, więc naturalnym pytaniem jest, którą opcję wybrać.

W porównaniu z łączeniami, podzapytania są proste w użyciu i czytelne. Nie są tak skomplikowane jak Łączyi dlatego są często używane przez Początkujący SQL.

Jednak podzapytania mają problemy z wydajnością. Użycie sprzężenia zamiast podzapytania może czasami przynieść nawet 500-krotny wzrost wydajności, ponieważ optymalizator może rozwiązać sprzężenie w jednym przebiegu, zamiast wielokrotnie oceniać wewnętrzne polecenie.

Mając wybór, zaleca się użycie JOIN zamiast zapytania podrzędnego. Podzapytania należy stosować wyłącznie jako rozwiązanie awaryjne, gdy nie można osiągnąć powyższego celu za pomocą operacji JOIN.

Podzapytania można również łatwo rozbić na pojedyncze logiczne komponenty, co jest bardzo przydatne, gdy testowanie i debugowanie zapytań.

FAQ