SQL Interview Prep · Lekcja

Przepisywanie podzapytań skorelowanych jako złączeń

Spłaszczanie skorelowanej logiki do złączeń lub funkcji okienkowych w celu poprawy wydajności

Przepisywanie podzapytań skorelowanych jako złączeń to bezpłatna lekcja SQL Interview Prep na CoddyKit. To lekcja 4 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.

Po co w ogóle przepisywać zapytanie

Skorelowane podzapytania są czytelne, ale mogą działać wolno: zapytanie wewnętrzne może być uruchamiane raz dla każdego wiersza zewnętrznego. Rekruterzy często proszą o przepisanie podzapytania na złączenie lub funkcję okna w celu poprawy wydajności.

Celem jest uzyskanie tego samego wyniku podczas jednego przejścia przez dane zamiast wielokrotnych skanów zapytania wewnętrznego.

Znajomość dwóch lub trzech wzorców przepisywania oraz wiedza, kiedy każdy z nich zachowuje poprawność, to podstawowa umiejętność na poziomie średniozaawansowanym.

Wzorzec 1: EXISTS na INNER JOIN

Skorelowane EXISTS, które sprawdza istnienie co najmniej jednego dopasowania, często można zastąpić przez INNER JOIN.

Należy jednak uważać: złączenie może utworzyć zduplikowane wiersze zewnętrzne, jeśli pasuje wiele wierszy wewnętrznych. Należy dodać DISTINCT lub agregację, aby przywrócić jeden wiersz na każdy klucz zewnętrzny.

-- Correlated EXISTS
SELECT c.customer_id, c.name
FROM customers c
WHERE EXISTS (SELECT 1 FROM orders o
             WHERE o.customer_id = c.customer_id);

-- Join rewrite (DISTINCT avoids dupes from fan-out)
SELECT DISTINCT c.customer_id, c.name
FROM customers c
JOIN orders o ON o.customer_id = c.customer_id;

Pułapka zwielokrotnienia

Najczęstszym błędem przy przepisywaniu zapytania jest nieuwzględnienie zwielokrotnienia. EXISTS zwraca każdego klienta tylko raz, niezależnie od liczby jego zamówień. Naiwne złączenie zwraca jeden wiersz na każde zamówienie, zawyżając zliczenia.

Jeśli kolejny etap wykona COUNT(*) lub SUM(amount) na takim wyniku złączenia bez starannego grupowania, liczby będą nieprawidłowe.

Zawsze należy zadać sobie pytanie: czy złączenie może zwielokrotnić wiersze? Jeśli tak, należy użyć DISTINCT lub GROUP BY, aby ponownie je scalić.

Wzorzec 2: NOT EXISTS na LEFT JOIN / IS NULL

Przepisanie antyzłączenia to stały element rozmów rekrutacyjnych. Skorelowane NOT EXISTS staje się LEFT JOIN, w którym po prawej stronie sprawdza się wartość NULL.

Niedopasowane wiersze zewnętrzne otrzymują po prawej stronie wartości NULL; filtrowanie po tej wartości NULL zachowuje dokładnie te wiersze, dla których nie ma dopasowania.

-- Correlated NOT EXISTS
SELECT c.customer_id FROM customers c
WHERE NOT EXISTS (SELECT 1 FROM orders o
                  WHERE o.customer_id = c.customer_id);

-- LEFT JOIN / IS NULL rewrite
SELECT c.customer_id
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.customer_id
WHERE o.customer_id IS NULL;

Wybór kolumny NOT NULL do sprawdzenia

W przepisaniu LEFT JOIN / IS NULL należy sprawdzać kolumnę po prawej stronie, która przy rzeczywistym dopasowaniu nigdy nie ma wartości NULL, najlepiej klucz złączenia lub klucz główny.

Jeśli sprawdzana kolumna może przyjmować wartość NULL, nie można odróżnić rzeczywistego braku dopasowania (braku wiersza) od dopasowanego wiersza, który po prostu ma w tej kolumnie wartość NULL. Taki błąd zwraca nieprawidłowe wiersze.

Użycie klucza złączenia (tutaj o.customer_id) lub o.order_id gwarantuje, że NULL oznacza „brak pasującego wiersza”.

Wzorzec 3: agregat skalarny na JOIN + GROUP BY

Skorelowany agregat w SELECT można zastąpić złączeniem z pogrupowanym podzapytaniem (tabelą pochodną).

Najpierw należy obliczyć agregat dla każdej grupy, a następnie dołączyć go z powrotem do wierszy szczegółowych. Zapytanie wewnętrzne zostanie wykonane raz zamiast dla każdego wiersza.

-- Correlated scalar aggregate
SELECT e1.name,
       (SELECT MAX(e2.salary) FROM employees e2
        WHERE e2.dept_id = e1.dept_id) AS dept_max
FROM employees e1;

-- Join + GROUP BY rewrite
SELECT e.name, m.dept_max
FROM employees e
JOIN (SELECT dept_id, MAX(salary) AS dept_max
      FROM employees GROUP BY dept_id) m
  ON m.dept_id = e.dept_id;

Wzorzec 4: przepisanie za pomocą funkcji okna

Często najczytelniejszym przepisaniem jest użycie funkcji okna. MAX(salary) OVER (PARTITION BY dept_id) całkowicie zastępuje skorelowany agregat i nie wymaga złączenia.

Wartość grupy jest obliczana podczas jednego przejścia przez dane, a każdy wiersz szczegółowy zostaje zachowany. Zwykle właśnie takiej odpowiedzi rekruterzy oczekują najbardziej w przypadku zapytań analitycznych.

SELECT name,
       dept_id,
       salary,
       MAX(salary) OVER (PARTITION BY dept_id) AS dept_max
FROM employees;

Przepisanie wyboru największych N elementów w grupie

Skorelowane podzapytanie wybierające najwyższy wiersz w każdej grupie (salary = MAX per dept) można elegancko przepisać za pomocą ROW_NUMBER.

Należy podzielić dane według grupy, posortować je według miary i zachować wiersze o randze 1. Jeśli mają zostać uwzględnione wszystkie najwyższe wiersze z remisem, należy zamiast tego użyć RANK.

SELECT name, dept_id, salary
FROM (
    SELECT name, dept_id, salary,
           ROW_NUMBER() OVER (PARTITION BY dept_id
                              ORDER BY salary DESC) AS rn
    FROM employees
) t
WHERE rn = 1;

Kiedy nie przepisywać zapytania

Przepisywanie nie zawsze przynosi korzyści. Skorelowane podzapytanie należy pozostawić, gdy:

Zbiór zewnętrzny jest niewielki, więc koszt wykonywania operacji dla każdego wiersza jest pomijalny.
Skorelowana kolumna jest dobrze indeksowana, a optymalizator już przekształca zapytanie w wydajne półzłączenie.
W utrzymywanym kodzie czytelność ma większe znaczenie niż mikrooptymalizacja.

Współczesne optymalizatory często automatycznie przekształcają EXISTS w półzłączenie. Przed założeniem, że przepisanie pomoże, należy zaznaczyć, że trzeba zmierzyć wydajność za pomocą EXPLAIN.

Weryfikowanie równoważności

Po każdym przepisaniu należy potwierdzić, że zwraca ono te same wiersze i tę samą liczność co oryginał.

Należy sprawdzić, czy liczby wierszy są takie same.
Należy sprawdzić, czy złączenie nie wprowadziło duplikatów wskutek zwielokrotnienia.
Należy sprawdzić, czy przypadki brzegowe związane z NULL i pustymi grupami nadal zachowują się poprawnie.

Szybki sposób: należy uruchomić obie wersje i wykonać EXCEPT w obu kierunkach; pusty wynik oznacza, że są równoważne. Rekruterzy doceniają weryfikowanie wyników zamiast przyjmowania równoważności za pewnik.

SELECT customer_id FROM query_a
EXCEPT
SELECT customer_id FROM query_b;
-- and the reverse; both empty => equivalent

Przepisywanie IN na JOIN

Nieskorelowane podzapytanie IN również często można przepisać na złączenie, ale obowiązuje ta sama przestroga dotycząca zwielokrotnienia. IN usuwa duplikaty członkostwa, natomiast złączenie tego nie robi.

Jeśli lista wewnętrzna zawiera zduplikowane klucze, złączenie powtórzy wiersze zewnętrzne. Należy użyć DISTINCT po stronie wewnętrznej lub w końcowym wyniku, aby zachować semantykę IN.

-- IN subquery
SELECT c.name FROM customers c
WHERE c.customer_id IN (SELECT o.customer_id FROM orders o);

-- Join rewrite, de-duplicated to match IN
SELECT DISTINCT c.name
FROM customers c
JOIN orders o ON o.customer_id = c.customer_id;

Szybki test

Należy wybrać poprawne przepisanie złączenia dla skorelowanego antyzłączenia NOT EXISTS.

Podsumowanie: przepisywanie skorelowanych podzapytań na złączenia

Najważniejsze wnioski:

EXISTS → INNER JOIN (należy dodać DISTINCT, aby uniknąć duplikatów wskutek zwielokrotnienia).
NOT EXISTS → LEFT JOIN ... WHERE key IS NULL (należy sprawdzać kolumnę, która nie może mieć wartości NULL).
Skorelowany agregat skalarny → należy wykonać JOIN z pogrupowaną tabelą pochodną lub, jeszcze lepiej, użyć funkcji okna.
Wybór najwyższych elementów w grupie → ROW_NUMBER (lub RANK w przypadku remisów).
Należy zweryfikować równoważność i sprawdzić zapytanie za pomocą EXPLAIN, zanim uzna się, że przepisanie jest szybsze.

Znajomość obu postaci i pułapki zwielokrotnienia to dokładnie to, co sprawdza się podczas rozmów rekrutacyjnych na poziomie średniozaawansowanym.

Często zadawane pytania

Czy lekcja „Przepisywanie podzapytań skorelowanych jako złączeń” jest bezpłatna?

Tak — pełny tekst „Przepisywanie podzapytań skorelowanych jako złączeń” 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 „Przepisywanie podzapytań skorelowanych jako złączeń”?

Spłaszczanie skorelowanej logiki do złączeń lub funkcji okienkowych w celu poprawy wydajności Ć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 4 z 4.

Ile czasu zajmuje lekcja „Przepisywanie podzapytań skorelowanych jako złączeń”?

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ń

← Powrót do SQL Interview Prep