Anatomia podzapytania skorelowanego
Jak zapytanie wewnętrzne odwołuje się do wiersza zewnętrznego oraz na czym polega wykonywanie go dla każdego wiersza
Anatomia podzapytania skorelowanego to bezpłatna lekcja SQL Interview Prep na CoddyKit. To lekcja 1 z 4. Możesz przeczytać całą lekcję poniżej za darmo — a potem ćwiczyć ją interaktywnie w przeglądarce z wbudowanym edytorem kodu i tutorem AI dostępnym 24/7. To część ścieżki edukacyjnej SQL Interview Prep, a Twój postęp synchronizuje się między webem a aplikacją CoddyKit. Kurs SQL Interview Prep zawiera 4 lekcji w sumie.
Co sprawia, że podzapytanie jest skorelowane
Rekruterzy dzielą podzapytania na dwie grupy. Zwykłe (nieskorelowane) podzapytanie może działać samodzielnie. Skorelowane podzapytanie odwołuje się do kolumny z zapytania zewnętrznego, dlatego nie może działać niezależnie.
- Nieskorelowane: oceniane raz, a wynik jest ponownie używany dla każdego wiersza zewnętrznego.
- Skorelowane: oceniane ponownie dla każdego wiersza zewnętrznego, ponieważ zależy od tego wiersza.
Znakiem rozpoznawczym jest kolumna z tabeli zewnętrznej pojawiająca się wewnątrz zapytania wewnętrznego. Jeśli ją Państwo zauważą, mogą od razu nazwać ten wzorzec.
Model wykonywania wiersz po wierszu
Proszę wyobrazić sobie, że silnik przechodzi w pętli po wierszach zewnętrznych. Dla każdego wiersza zewnętrznego podstawia jego wartości do zapytania wewnętrznego, wykonuje je, a następnie wykorzystuje wynik do podjęcia decyzji lub wykonania obliczenia.
To właśnie model mentalny, który podczas rozmowy rekrutacyjnej należy umieć wyjaśnić: „zapytanie wewnętrzne wykonuje się raz dla każdego wiersza zewnętrznego”.
To sformułowanie sugeruje również typowe pytanie dodatkowe: podzapytania skorelowane mogą działać wolno, ponieważ zapytanie wewnętrzne może wykonać się tysiące razy. Naprawimy to w lekcji 4.
Rozpoznawanie odwołania zewnętrznego
Tutaj tabela employees i alias zewnętrznego zapytania e1 sterują zapytaniem wewnętrznym, które odczytuje e1.dept_id. To odwołanie do wiersza zewnętrznego jest właśnie korelacją.
Po usunięciu prefiksu aliasu zapytanie wewnętrzne nie skompiluje się samodzielnie. Ta zależność dokładnie decyduje o tym, że jest ono skorelowane.
SELECT e1.name, e1.salary
FROM employees e1
WHERE e1.salary > (
SELECT AVG(e2.salary)
FROM employees e2
WHERE e2.dept_id = e1.dept_id
);Czytanie zapytania na głos
Proszę przetłumaczyć poprzednie zapytanie na prosty język, tak jak podczas rozmowy rekrutacyjnej:
„Dla każdego pracownika e1 znajdź średnie wynagrodzenie w jego dziale i pozostaw pracownika tylko wtedy, gdy zarabia więcej niż średnia w tym dziale.”
Klauzula WHERE e2.dept_id = e1.dept_id w zapytaniu wewnętrznym wiąże średnią z działem tego pracownika. Bez tego wiersza porównywaliby Państwo wszystkich pracowników ze średnią dla całej firmy.
Aliasy są obowiązkowe
Gdy zapytanie wewnętrzne i zewnętrzne korzystają z tej samej tabeli, należy nadać aliasy obu tabelom, aby silnik wiedział, do którego wiersza należy dana kolumna.
e1= sprawdzany wiersz zewnętrzny.e2= wewnętrzne skanowanie tabeli.
Po usunięciu aliasów dept_id staje się niejednoznaczne. Wiele silników po cichu powiąże je wtedy z tabelą wewnętrzną, zrywając korelację. Rekruterzy celowo umieszczają dokładnie ten błąd.
Podzapytanie skorelowane w SELECT
Podzapytania skorelowane nie ograniczają się do WHERE. Na liście SELECT tworzą wyliczaną kolumnę, również obliczaną dla każdego wiersza zewnętrznego.
Poniżej każde zamówienie pokazuje, ile innych zamówień złożył ten sam klient. Zapytanie wewnętrzne zliczające jest skorelowane przez o.customer_id.
SELECT o.order_id,
o.customer_id,
(SELECT COUNT(*)
FROM orders o2
WHERE o2.customer_id = o.customer_id) AS customer_order_count
FROM orders o;Skalar oznacza dokładnie jedną wartość
Podzapytanie skorelowane użyte w SELECT lub porównywane za pomocą =, >, < musi zwracać jedną wartość skalarną dla każdego wiersza zewnętrznego.
Jeśli zwróci więcej niż jeden wiersz, baza danych zgłosi błąd, na przykład „podzapytanie zwraca więcej niż jeden wiersz”.
Funkcje agregujące, takie jak COUNT, MAX czy AVG, gwarantują jedną wartość, dlatego często stosuje się je wewnątrz skalarnych podzapytań skorelowanych. Znajomość tej zasady pozwala uniknąć częstej niespodzianki w czasie działania programu.
Gdy podzapytanie zwraca NULL
Skalarne podzapytanie skorelowane może nie znaleźć żadnego wiersza wewnętrznego. Funkcja agregująca zwróci wtedy NULL (natomiast funkcja COUNT zwróci 0).
Ta wartość NULL przechodzi do wyrażenia zewnętrznego. Porównania z NULL dają UNKNOWN, więc wiersz zewnętrzny może zostać po cichu wykluczony.
Jeśli potrzebna jest wartość zastępcza, należy opakować podzapytanie w COALESCE. Rekruterzy lubią pytać, co się dzieje, gdy żaden wiersz wewnętrzny nie pasuje, i oczekują wzmianki o zachowaniu wartości NULL.
SELECT c.customer_id,
COALESCE((SELECT MAX(o.amount)
FROM orders o
WHERE o.customer_id = c.customer_id), 0) AS biggest_order
FROM customers c;Przykład: data najnowszego zamówienia
Częste zadanie: wyświetlić każdego klienta wraz z datą jego najnowszego zamówienia. Podzapytanie skorelowane w SELECT realizuje to bezpośrednio.
Dla każdego wiersza klienta zapytanie wewnętrzne znajduje maksymalną datę zamówienia dla tego klienta za pomocą o.customer_id = c.customer_id.
SELECT c.customer_id,
c.name,
(SELECT MAX(o.order_date)
FROM orders o
WHERE o.customer_id = c.customer_id) AS last_order_date
FROM customers c;Dlaczego może działać wolno
Ponieważ zapytanie wewnętrzne wykonuje się raz dla każdego wiersza zewnętrznego, podzapytanie skorelowane działające na dużej tabeli zewnętrznej może uruchomić miliony wykonań wewnętrznych.
- Indeks na kolumnie używanej do korelacji (tutaj
orders.customer_id) pozwala szybko zakończyć każde wykonanie wewnętrzne. - Bez indeksu każde wykonanie może skanować całą tabelę, co daje w przybliżeniu pracę O(n*m).
Podczas rozmowy rekrutacyjnej należy zawsze wspomnieć o indeksie i przepisaniu zapytania na złączenie jako sposobach poprawy wydajności.
Podzapytania skorelowane i nieskorelowane — porównanie
Różnica sprowadza się do jednego wiersza. Wersja nieskorelowana porównuje wszystkich ze średnią dla całej firmy, a wersja skorelowana porównuje każdą osobę z jej własnym działem.
Proszę przeczytać oba zapytania i zauważyć, jak pojedynczy wiersz WHERE e2.dept_id = e1.dept_id zmienia całe znaczenie.
-- Uncorrelated: one global average, computed once
SELECT name FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);
-- Correlated: per-department average, recomputed per row
SELECT e1.name FROM employees e1
WHERE e1.salary > (
SELECT AVG(e2.salary) FROM employees e2
WHERE e2.dept_id = e1.dept_id
);Szybkie sprawdzenie
Proszę sprawdzić, czy rozumieją Państwo, co definiuje podzapytanie skorelowane.
Podsumowanie: budowa podzapytania skorelowanego
Najważniejsze wnioski:
- Podzapytanie skorelowane odwołuje się do wiersza zewnętrznego i wykonuje się raz dla każdego wiersza zewnętrznego.
- Gdy obie tabele są tą samą tabelą, należy nadać aliasy obu, aby korelacja była jednoznaczna.
- Użycie skalarne musi zwracać dokładnie jedną wartość; brak dopasowań daje NULL, dlatego należy użyć
COALESCE. - Podzapytanie może znajdować się w SELECT lub WHERE, a wydajność zależy przede wszystkim od indeksowania kolumny używanej do korelacji.
Jeśli podczas rozmowy rekrutacyjnej powiedzą Państwo „wykonuje się raz dla każdego wiersza zewnętrznego”, będzie to trafne ujęcie najważniejszej zasady.
Ucz się SQL dzięki korepetycjom AI — za darmo
Pisz i uruchamiaj kod w przeglądarce, otrzymuj natychmiastową pomoc od korepetytora AI dostępnego 24/7 i kontynuuj naukę w sieci lub w aplikacji.
- Kursy
- 30
- Lekcje
- 120
Często zadawane pytania
Czy lekcja „Anatomia podzapytania skorelowanego” jest bezpłatna?
Tak — pełny tekst „Anatomia podzapytania skorelowanego” jest dostępny za darmo tutaj w sieci. Aby ćwiczyć ją interaktywnie (wbudowany edytor kodu i tutor AI dostępny 24/7) i odblokować resztę kursu SQL Interview Prep, przejdź na CoddyKit PRO. Kurs SQL Interview Prep zawiera 4 lekcji w sumie.
Co nauczysz się w „Anatomia podzapytania skorelowanego”?
Jak zapytanie wewnętrzne odwołuje się do wiersza zewnętrznego oraz na czym polega wykonywanie go dla każdego wiersza Ćwiczysz SQL Interview Prep z praktycznym kodem, który uruchamiasz bezpośrednio w przeglądarce, a tutor AI dostępny 24/7 odpowiada na Twoje pytania podczas pracy nad lekcją.
Czy potrzebuję doświadczenia, aby zacząć SQL Interview Prep?
Nie wymagamy żadnego doświadczenia. SQL Interview Prep w CoddyKit jest strukturyzowany dla początkujących i zaawansowanych użytkowników, więc możesz zacząć tutaj lub od początku i uczyć się w swoim tempie. To lekcja 1 z 4.
Ile czasu zajmuje lekcja „Anatomia podzapytania skorelowanego”?
Większość lekcji CoddyKit trwa około 5–10 minut. Każda lekcja to mały, interaktywny krok, dzięki czemu robisz systematyczne postępy i zawsze wracasz dokładnie do tego samego miejsca — na webie i w aplikacji.
Czy mogę pisać i uruchamiać kod w tej lekcji SQL Interview Prep?
Tak. Każda lekcja SQL Interview Prep zawiera wbudowany edytor kodu, więc piszesz i uruchamiasz prawdziwy kod bezpośrednio w przeglądarce i od razu otrzymujesz sprzężenie zwrotne od AI — bez konfiguracji na komputerze.
Wszystkie lekcje w tym kursie
- Anatomia podzapytania skorelowanego
- Agregaty dla grup bez GROUP BY
- Skorelowane EXISTS i NOT EXISTS
- Przepisywanie podzapytań skorelowanych jako złączeń