UNION ALL, UNION i INTERSECT: co łączysz w SQL?

Masz dwie listy klientów. Jedna pochodzi ze sprzedaży, druga z obsługi zgłoszeń. Chcesz je połączyć i nagle okazuje się, że „połączyć” może oznaczać kilka różnych rzeczy. Zachować każde wystąpienie? Pokazać każdą osobę raz? A może znaleźć wyłącznie klientów obecnych na obu listach?

Ja zaczynam od tego pytania, bo wybór między UNION ALL, UNION i INTERSECT zmienia znaczenie wyniku. Samo działające zapytanie nie mówi jeszcze, czy policzyłeś zdarzenia, klientów czy kombinacje kolumn. Pokażę różnice na małych danych syntetycznych w PostgreSQL 18, z duplikatami i brakującymi identyfikatorami.

Trzy operatory, trzy różne pytania

UNION ALL dopisuje wszystkie wiersze z obu wyników. UNION łączy wyniki i usuwa powtórzenia całych wierszy. INTERSECT zwraca ich część wspólną, domyślnie również bez powtórzeń. Są to operacje na wynikach zapytań, więc nie musisz ograniczać się do dwóch fizycznych tabel.

Dokumentacja łączenia zapytań opisuje składnię tych operatorów. Ja przekładam ją na pytania: „jakie wystąpienia mam łącznie?”, „jakie różne wiersze mam w którymkolwiek źródle?” oraz „jakie różne wiersze występują w obu źródłach?”. Słowo „wiersz” jest tutaj istotne: baza porównuje to, co rzeczywiście wybierzesz.

Jeżeli wybierzesz tylko identyfikator klienta, porównujesz identyfikatory. Jeśli dodasz datę, porównujesz już pary identyfikator i data. Zmiana jednej kolumny w SELECT może zatem zmienić odpowiedź, mimo że operator pozostaje taki sam. Zanim wybiorę UNION, zapisuję oczekiwany poziom szczegółowości wyniku.

Dane, w których widać powtórzenia

Pierwsza lista, sales_customers, ma pięć wierszy: 101, 101, 101, 102 i NULL. Druga, support_customers, ma również pięć: 101, 101, 103, NULL i NULL. Każda liczba oznacza tutaj identyfikator z tego samego, wspólnego systemu. To założenie potrzebne do sensownego porównania.

Powtórzenia mogą pochodzić z kilku zdarzeń dotyczących jednej osoby. W tym ćwiczeniu zachowuję je celowo, żeby pokazać różnice operatorów. NULL oznacza rekord bez rozpoznanego identyfikatora. Nie zakładam, że wszystkie takie rekordy opisują jedną osobę.

WITH sales_customers(customer_id) AS (
    VALUES (101), (101), (101), (102), (NULL)
), support_customers(customer_id) AS (
    VALUES (101), (101), (103), (NULL), (NULL)
)
SELECT customer_id FROM sales_customers
UNION ALL
SELECT customer_id FROM support_customers
ORDER BY customer_id NULLS LAST;

W kolejnych przykładach korzystam z tych samych źródeł. Jeżeli używasz powyższego CTE, zachowaj część WITH i wymień następujące po niej zapytanie. Dokumentacja WITH wyjaśnia zakres takiej nazwy: istnieje ona na potrzeby jednej instrukcji.

UNION ALL zachowuje każde wystąpienie

Wynik pierwszego zapytania zawiera dziesięć wierszy. Identyfikator 101 występuje pięć razy, 102 raz, 103 raz, a NULL trzy razy. Liczba rekordów jest sumą liczby wierszy obu wejść. Nic nie zostało usunięte dlatego, że wygląda tak samo.

To właściwy wybór, gdy każde wystąpienie ma znaczenie. Jeśli łączę zdarzenia z dwóch kanałów, chcę zwykle zachować każde zdarzenie i jego pochodzenie. Sam identyfikator klienta nie jest wtedy kompletnym opisem rekordu. W rzeczywistym modelu dodałbym identyfikator zdarzenia oraz jego rodzaj.

UNION ALL nie obiecuje, że najpierw zobaczysz wszystkie rekordy pierwszej listy, a potem drugiej. Końcowe ORDER BY ustala kolejność prezentacji. Bez niego wynik jest zbiorem wierszy bez gwarantowanego porządku. Nie wykorzystuję pozycji rekordu do ustalania, z którego źródła pochodzi.

Przy okazji: zachowanie dziesięciu rekordów nie dowodzi, że źródła się nie nakładają. Operator niczego takiego nie sprawdza. Jeśli ta sama transakcja została zaimportowana dwukrotnie, UNION ALL zachowa oba wystąpienia. Rozpoznanie tego problemu wymaga klucza zdarzenia i reguły rozstrzygnięcia duplikatów.

UNION tworzy listę różnych wierszy

SELECT customer_id FROM sales_customers
UNION
SELECT customer_id FROM support_customers
ORDER BY customer_id NULLS LAST;

Otrzymuję cztery wiersze: 101, 102, 103 i NULL. Powtórzenia 101 zniknęły zarówno wewnątrz źródeł, jak i pomiędzy źródłami. Tak samo trzy wiersze z NULL dały jeden wynikowy wiersz z NULL. To semantyka porównania w operacji zbiorowej, nie dowód tożsamości klientów.

Jeżeli pytanie brzmi „którzy rozpoznani klienci wystąpili w którymkolwiek kanale?”, dodaję WHERE customer_id IS NOT NULL w obu częściach. Dostanę wtedy trzy identyfikatory. Zwróć uwagę, że jeden wiersz NULL w poprzednim wyniku nie powinien być raportowany jako czwarty znany klient.

Takie rozróżnienie bywa istotniejsze od samej składni. Liczba różnych wartości kolumny, liczba osób i liczba zdarzeń to trzy różne miary. Wspólny identyfikator może pozwalać je powiązać, ale nie zwalnia mnie z określenia, co właściwie liczę i jak traktuję nierozpoznane rekordy.

INTERSECT wybiera część wspólną

SELECT customer_id FROM sales_customers
INTERSECT
SELECT customer_id FROM support_customers
ORDER BY customer_id NULLS LAST;

Wynik to dwa wiersze: 101 i NULL. Identyfikator 101 znajduje się na obu listach, natomiast 102 występuje tylko w sprzedaży, a 103 tylko w obsłudze. Wielokrotne wystąpienie 101 nie powoduje wielokrotnego pokazania go w zwykłym INTERSECT.

NULL również pojawia się po obu stronach i uczestniczy w porównaniu wierszy operacji zbiorowej. Nie należy z tego wyciągać wniosku, że znaleziono wspólnego, anonimowego klienta. To jedynie informacja o obecności nierozpoznanej wartości na obu wejściach. Do listy rozpoznanych wspólnych klientów filtruję braki przed operacją.

Właśnie dlatego nie używam imienia i nazwiska jako bezrefleksyjnego klucza osoby. Dwie osoby mogą nazywać się tak samo, a jedna osoba może mieć zapisane różne warianty danych. Przykład z powtarzającym się nazwiskiem dobrze pokazuje porównanie tekstu, ale sam nie rozstrzyga tożsamości pracownika.

NULL w operacji zbiorowej i w warunku JOIN

W zwykłym porównaniu NULL = NULL wynik jest nieznany. W eliminowaniu duplikatów oraz porównywaniu wierszy tych operacji brakujące wartości mogą być traktowane jako nieróżniące się. Instrukcja SELECT opisuje zasady UNION, INTERSECT i ich wariantów, a dokumentacja porównań opisuje logikę NULL.

Z tego powodu prosta próba zastąpienia INTERSECT przez INNER JOIN na równości może zmienić wynik. Braki nie dopasują się przez zwykłe =, a powtarzające się klucze mogą mnożyć wiersze. To dwie osobne różnice, które trzeba uwzględnić, zanim uzna się zapytania za zamienne.

Na naszych danych trzy wystąpienia 101 po lewej i dwa po prawej dałyby sześć par przy takim złączeniu. Zwykły INTERSECT daje jeden wiersz 101. Jedno zapytanie opisuje pary dopasowań, drugie różne wspólne wiersze. Podobieństwo wizualne nazw operatorów nie wystarcza do porównania ich znaczenia.

Porównywane są całe wybrane wiersze

SELECT customer_id, 'sales' AS source
FROM sales_customers
UNION
SELECT customer_id, 'support' AS source
FROM support_customers
ORDER BY customer_id NULLS LAST, source;

Ten wynik ma sześć wierszy: po trzy różne pary dla każdego źródła. Dla 101 pojawi się para ze znacznikiem sales i para ze znacznikiem support. Nie są identyczne, więc UNION zachowa obie. Wewnątrz źródła powtarzające się pary nadal się zredukują.

Znacznik pochodzenia jest przydatny do analizy, ale zmienia klucz porównania. Jeśli chcesz mieć końcową listę klientów niezależną od kanału, musisz wykonać kolejny świadomy wybór kolumn. Nie można jednocześnie wymagać porównania wyłącznie identyfikatorów i oczekiwać, że dodatkowe, różne wartości źródła zostaną zignorowane.

Podobnie działa dopisanie daty, kwoty czy numeru dokumentu. UNION nie ma wbudowanej wiedzy, które kolumny są ważne biznesowo. Wybór tych kolumn jest decyzją autora zapytania. Ja przed redakcją takiego raportu zawsze zapisuję, co ma stanowić jeden niepowtarzalny wiersz.

Kolumny dopasowują się według pozycji

Obie części muszą zwracać taką samą liczbę kolumn, a odpowiadające sobie typy muszą dać się uzgodnić. Nie muszą być zapisane jako dokładnie identyczne typy. PostgreSQL stosuje reguły wyznaczania wspólnego typu osobno dla każdej pozycji.

Najgroźniejsza pomyłka bywa jednak semantyczna. Jeśli pierwsze zapytanie zwraca kraj i miasto, a drugie miasto i kraj, oba pola mogą być tekstowe. Baza może przyjąć takie zapytanie, ale wartości trafią do niewłaściwych kolumn. Zgodność typów nie oznacza zgodności znaczenia.

Dlatego w obu częściach piszę jawną listę kolumn w tej samej kolejności. Przy różnej reprezentacji danych ustalam wspólny typ przez świadome rzutowanie. Nie ukrywam konfliktu przez przypadkową konwersję wszystkiego na tekst. Takie rozwiązanie mogłoby później zepsuć liczenie albo porządkowanie wartości.

Nazwy kolumn całego wyniku w tych przykładach pochodzą z pierwszej części. Dobry alias właśnie tam pomaga odbiorcy. Nie traktuję różnych aliasów po obu stronach jako mechanizmu dopasowania po nazwie: to kolejność wyrażeń decyduje, które wartości stoją naprzeciw siebie.

Wariant ALL dotyczy także części wspólnej

PostgreSQL obsługuje INTERSECT ALL. Dla każdego wiersza zachowuje tyle wystąpień, ile wynosi mniejsza z ich liczebności po obu stronach. W naszym źródle 101 występuje trzy razy i dwa razy, więc zostanie dwa razy. NULL występuje raz i dwa razy, więc zostanie raz. Łącznie wynik ma trzy wiersze.

To inne pytanie niż lista różnych wspólnych klientów. Wariant ALL może opisywać nakładające się wielokrotności wierszy, ale sam nie paruje konkretnych zdarzeń w czasie. Jeśli dwa zgłoszenia mają odpowiadać dwóm konkretnym zamówieniom, potrzebujesz reguły dopasowania tych zdarzeń. Liczba wystąpień identyfikatora nie tworzy takiej relacji.

Warto znać ten wariant, żeby nie uznać słowa ALL za przełącznik dotyczący wyłącznie UNION. Jego dostępność zależy jednak od silnika. Tego zapisu nie przenoszę automatycznie do T-SQL: dokumentacja SQL Server opisuje INTERSECT zwracający różne wiersze, bez wariantu ALL.

EXCEPT pozwala zadać pytanie o różnicę

Dla pełniejszego obrazu przydaje się EXCEPT. Lewa lista EXCEPT prawa lista zwróci u nas tylko 102. To rozpoznana wartość obecna w sprzedaży i nieobecna w obsłudze. Zamiana stron da 103. Kolejność ma więc znaczenie, inaczej niż przy pytaniu o część wspólną.

EXCEPT ALL uwzględnia liczebności. Od trzech wystąpień 101 po lewej odejmuje dwa po prawej, pozostawiając jedno. 102 pozostaje raz, a NULL nie pozostaje, bo prawe wejście zawiera wystarczająco wiele takich wystąpień. Wynik tego wariantu zawiera zatem 101 i 102.

Nie interpretuję braku klienta w obsłudze jako dowodu jego zadowolenia. Może nie było zgłoszenia, mogło nie dojść do dopasowania identyfikatora albo nie objęliśmy właściwego okresu. Operator ustala różnicę wierszy pomiędzy wskazanymi wejściami. Wniosek o zachowaniu klienta wymaga dodatkowego kontekstu.

ORDER BY i LIMIT stosuj na właściwym poziomie

Końcowe ORDER BY porządkuje cały połączony wynik. Sortowanie pojedynczego wejścia nie ustanawia kolejności rezultatu operacji zbiorowej. W przykładach jawnie umieszczam NULL na końcu, żeby łatwo przeczytać listę liczb i osobno zauważyć braki.

Jeśli chcesz ograniczyć liczbę rekordów jednego wejścia przed połączeniem, użyj nawiasów i umieść tam odpowiednie ORDER BY oraz LIMIT. Bez tego możesz ograniczyć całość albo otrzymać inną strukturę polecenia, niż zamierzałeś. Dokumentacja UNION pokazuje, jak nawiasy rozstrzygają taki zakres.

Przy mieszaniu operatorów również stosuję nawiasy. W PostgreSQL INTERSECT wiąże silniej niż UNION i EXCEPT. Zapis bez nawiasów może więc odpowiadać innemu drzewu operacji niż czytanie kolejno od lewej. Czytelnik raportu nie powinien zgadywać, które dwie listy miały być porównane najpierw.

Przed operacją uzgodnij znaczenie obu wejść

Wspólny typ identyfikatora jeszcze nie dowodzi, że ten sam numer oznacza tego samego klienta. Dwa niezależne systemy mogą osobno nadawać numer 101. Jeżeli jeden opisuje hurtownię, a drugi sklep, zwykłe INTERSECT pokaże wspólną liczbę bez potwierdzenia wspólnej osoby. Potrzebuję mapowania identyfikatorów albo rzeczywiście wspólnego klucza.

Tak samo uzgadniam zakres czasu. Lista klientów sprzedaży z października i lista klientów obsługi ze wszystkich lat odpowiedzą na inne pytanie niż dwie listy z października. Każdy filtr przygotowujący wejście jest częścią definicji wyniku. W gotowym raporcie warto wskazać te okresy osobno, jeśli celowo się różnią.

Przy porównywaniu kwot sprawdzam jednostki i definicje. Dwie liczbowe kolumny mogą opisywać cenę za sztukę i kwotę całego dokumentu. Dwie daty mogą oznaczać utworzenie i zakończenie zdarzenia. SQL potrafi złożyć zgodne typy, ale nie sprawdzi, czy takie zestawienie odpowiada na sensowne pytanie analityczne.

Osobno przyglądam się brakującemu wejściu. Pusta tabela i tabela pełna NULL nie są tym samym. Puste źródło nie wnosi żadnych wierszy do UNION ALL. Pięć rekordów z NULL wnosi pięć wystąpień, nawet jeśli po późniejszym UNION zobaczysz tylko jeden wiersz. Ten szczegół pomaga odróżnić brak zdarzeń od braku ich identyfikacji.

Na końcu zapisuję, jaką informację świadomie tracę. UNION z samym identyfikatorem usuwa liczebność zdarzeń i ich pochodzenie. To może być dokładnie właściwa operacja dla listy odbiorców. Jeśli później zechcę odtworzyć liczbę zgłoszeń na osobę, potrzebuję zachowanego źródła zdarzeń. Gotowa lista różnych klientów nie zawiera już tej szczegółowości.

Wydajność i przenoszenie między silnikami

UNION ALL nie musi eliminować duplikatów tak jak UNION. To uzasadnia rozważenie go, gdy powtórzenia mają zostać, ale nie dowodzi, że każde zapytanie z ALL będzie zawsze szybsze. Znaczenie wyniku ustalam przed optymalizacją, a sposób wykonania sprawdzam w planie.

Rozmiar wejść, filtry, typy i pozostałe operacje mogą mieć większy wpływ na całość niż zmiana jednego słowa. EXPLAIN pozwala badać konkretny plan PostgreSQL. Nie podaję procentowego przyspieszenia wywnioskowanego z małej listy liczb.

MySQL 8.4 ma własną dokumentację operacji zbiorowych, obejmującą UNION, INTERSECT i EXCEPT. Nie rozszerzam tego stwierdzenia na wszystkie historyczne wersje MySQL. Przed przeniesieniem zapytania sprawdzam wersję, składnię wariantów ALL oraz zasady typów i porównań w docelowym silniku.

Ćwiczenie: wybierz właściwy poziom szczegółowości

Najpierw usuń braki z obu wejść i przygotuj listę rozpoznanych klientów obecnych w dowolnym kanale. Oczekiwane wartości to 101, 102 i 103. Następnie wybierz wspólnych klientów: zostanie 101. Wyjaśnij, dlaczego liczba jego zdarzeń nie zwiększa długości tej listy.

Teraz zachowaj każde wystąpienie z obu list, ale wyklucz braki. Wynik powinien mieć siedem wierszy: pięć wystąpień 101 oraz po jednym 102 i 103. Na końcu dodaj znacznik źródła i zastanów się, jaka dodatkowa informacja byłaby potrzebna, żeby rozpoznać pojedyncze zdarzenie.

Jeśli umiesz nazwać różnicę między tymi trzema wynikami, rozumiesz praktyczny wybór operatora. Dalej przydadzą się SELECT, WHERE oraz rodzaje JOIN. Przy każdym kolejnym zapytaniu wracaj do pytania, co oznacza jeden wynikowy wiersz.

Film o UNION i INTERSECT

Zachowuję polski materiał wideo. Tekst rozszerza temat o NULL, zgodność typów i warianty ALL na samodzielnych danych.