SQL Interview Prep · Lekcja

Odwzorowywanie operacji na zbiorach za pomocą złączeń

Przepisywanie EXCEPT i INTERSECT w dialektach, które ich nie obsługują

Odwzorowywanie operacji na zbiorach za pomocą 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.

Dlaczego emulować operacje zbiorowe

Nie każda baza danych obsługuje INTERSECT i EXCEPT. Na przykład starsze wersje MySQL nie obsługiwały ich w ogóle. Rekruterzy sprawdzają, czy potrafią Państwo odtworzyć logikę operacji zbiorowych za pomocą złączeń i podzapytań, gdy dany operator jest niedostępny.

Znajomość zarówno operatora zbiorowego, jak i jego odpowiednika opartego na złączeniu dowodzi, że rozumieją Państwo, co ten operator faktycznie oblicza.

INTERSECT jako INNER JOIN

INTERSECT wyszukuje wiersze wspólne dla obu zbiorów. Odpowiednikiem opartym na złączeniu jest INNER JOIN wykonane dla wszystkich porównywanych kolumn oraz użycie DISTINCT w celu odtworzenia działania usuwającego duplikaty.

Każda kolumna uwzględniana w porównaniu staje się częścią predykatu złączenia.

-- A INTERSECT B emulated:
SELECT DISTINCT a.customer_id
FROM orders_2023 a
JOIN orders_2024 b
  ON a.customer_id = b.customer_id;

Dlaczego DISTINCT jest potrzebne w INTERSECT

Zwykłe INNER JOIN może zwielokrotniać wiersze: jeśli wartość występuje wielokrotnie po którejkolwiek stronie, złączenie powiela wiersze. Standardowy INTERSECT zwraca każdy wspólny wiersz tylko raz, dlatego należy dodać DISTINCT, aby zredukować duplikaty wprowadzone przez złączenie.

Zapomnienie o DISTINCT w tym miejscu jest częstym błędem podczas rozmów kwalifikacyjnych.

-- without DISTINCT, a customer with 3 orders in each year
-- would appear 9 times from the join

EXCEPT jako LEFT JOIN / IS NULL

EXCEPT (A, ale nie B) to anti-join. Przenośną postacią jest LEFT JOIN z A do B po wszystkich kolumnach, z zachowaniem wyłącznie tych wierszy, w których po stronie B znajduje się NULL (brak dopasowania), a następnie zastosowaniem DISTINCT.

Ten wzorzec LEFT JOIN / IS NULL jest jednym z najczęściej wykorzystywanych trików podczas rozmów kwalifikacyjnych dotyczących SQL.

SELECT DISTINCT a.customer_id
FROM orders_2023 a
LEFT JOIN orders_2024 b
  ON a.customer_id = b.customer_id
WHERE b.customer_id IS NULL;

EXCEPT z NOT EXISTS

Równie przenośną wersją EXCEPT jest użycie NOT EXISTS. Taki zapis oznacza: „zachowaj każdy wiersz A, dla którego nie istnieje pasujący wiersz B” i poprawnie obsługuje wartości NULL.

Wielu inżynierów preferuje NOT EXISTS, ponieważ jego intencja jest jednoznaczna, a ponadto pozwala uniknąć pułapki NOT IN + NULL.

SELECT DISTINCT a.customer_id
FROM orders_2023 a
WHERE NOT EXISTS (
  SELECT 1 FROM orders_2024 b
  WHERE b.customer_id = a.customer_id
);

INTERSECT z EXISTS

Analogicznie INTERSECT można zapisać za pomocą EXISTS: zachowaj każdy unikatowy wiersz A, dla którego istnieje pasujący wiersz B.

EXISTS kończy działanie po znalezieniu pierwszego dopasowania, więc może być wydajny i pozwala uniknąć rozrostu liczby wierszy po złączeniu, czasem eliminując potrzebę użycia DISTINCT po stronie złączenia.

SELECT DISTINCT a.customer_id
FROM orders_2023 a
WHERE EXISTS (
  SELECT 1 FROM orders_2024 b
  WHERE b.customer_id = a.customer_id
);

Pułapka NULL w NOT IN

Naturalną próbą emulowania EXCEPT jest użycie NOT IN, ale to rozwiązanie jest niebezpieczne: jeśli podzapytanie zwróci dowolną wartość NULL, NOT IN nie zwróci żadnych wierszy, ponieważ porównanie przyjmuje wartość UNKNOWN.

To często sprawdzany podstępny przypadek. Należy preferować NOT EXISTS albo LEFT JOIN / IS NULL, które bezpiecznie obsługują wartości NULL.

-- RISKY if orders_2024.customer_id can be NULL:
SELECT DISTINCT customer_id FROM orders_2023
WHERE customer_id NOT IN (
  SELECT customer_id FROM orders_2024
);

Dopasowanie po wielu kolumnach

Gdy porównanie zbiorów obejmuje kilka kolumn, każda z nich musi znaleźć się w predykacie złączenia. W przypadku anti-join trzeba również uwzględnić możliwość wystąpienia wartości NULL w tych kolumnach — i właśnie tutaj NOT EXISTS sprawdza się szczególnie dobrze.

Należy jawnie wymienić każdą kolumnę w klauzuli ON; pominięcie jednej z nich po cichu zmienia znaczenie „równego wiersza”.

SELECT DISTINCT a.id, a.city
FROM a
LEFT JOIN b
  ON a.id = b.id AND a.city = b.city
WHERE b.id IS NULL;

Emulowanie UNION bez operatora

UNION ALL to po prostu konkatenacja, którą każdy dialekt obsługuje bezpośrednio. Aby w razie potrzeby emulować UNION eliminujący duplikaty, należy wykonać konkatenację za pomocą UNION ALL wewnątrz podzapytania, a następnie opakować ją w SELECT DISTINCT lub GROUP BY obejmujące wszystkie kolumny.

Pokazuje to, że UNION jest po prostu połączeniem UNION ALL z etapem usuwania duplikatów.

SELECT DISTINCT * FROM (
  SELECT city FROM a
  UNION ALL
  SELECT city FROM b
) combined;

Wybór właściwej emulacji

Przewodnik decyzyjny:

INTERSECT → EXISTS lub INNER JOIN + DISTINCT.
EXCEPT → NOT EXISTS lub LEFT JOIN / IS NULL.
Należy unikać NOT IN, gdy możliwe są wartości NULL.
UNION → UNION ALL opakowane w DISTINCT.

EXISTS / NOT EXISTS są najbardziej przenośne i bezpieczne względem wartości NULL, dlatego stanowią najbezpieczniejszą odpowiedź podczas rozmowy kwalifikacyjnej.

Zebranie wszystkiego w całość

Umiejętność przekształcania operatorów zbiorowych w złączenia pokazuje, że rozumieją Państwo ich logikę zbiorów, a nie tylko składnię. Anti-join (LEFT JOIN / IS NULL lub NOT EXISTS) to wzorzec o największej wartości: pojawia się przy emulowaniu EXCEPT, znajdowaniu sierot oraz w pytaniach o brakujące rekordy.

Warto zacząć od NOT EXISTS ze względu na poprawność, a następnie wspomnieć o wersji ze złączeniem w kontekście wydajności.

Szybki test

Baza danych nie obsługuje EXCEPT. Należy znaleźć wartości customer_ids w orders_2023, których nie ma w orders_2024, przy czym kolumna może zawierać wartości NULL.

Podsumowanie

Najważniejsze wnioski:

INTERSECT → INNER JOIN + DISTINCT albo EXISTS.
EXCEPT → LEFT JOIN / IS NULL albo NOT EXISTS (anti-join).
Należy dodać DISTINCT, aby zachować sposób usuwania duplikatów stosowany przez operatory zbiorowe i ograniczyć rozrost liczby wierszy po złączeniu.
Należy unikać NOT IN, gdy możliwe są wartości NULL; lepiej użyć NOT EXISTS.
UNION = UNION ALL opakowane w DISTINCT.

Często zadawane pytania

Czy lekcja „Odwzorowywanie operacji na zbiorach za pomocą złączeń” jest bezpłatna?

Tak — pełny tekst „Odwzorowywanie operacji na zbiorach za pomocą 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 „Odwzorowywanie operacji na zbiorach za pomocą złączeń”?

Przepisywanie EXCEPT i INTERSECT w dialektach, które ich nie obsługują Ć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 „Odwzorowywanie operacji na zbiorach za pomocą 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

UNION a UNION ALL
Liczba kolumn i zgodność typów
INTERSECT i EXCEPT do porównywania
Odwzorowywanie operacji na zbiorach za pomocą złączeń

← Powrót do SQL Interview Prep