Obydwa operatory nakładają na siebie wyniki dwóch zapytań, więc SQL UNION vs UNION ALL może wydawać się niewielkim wyborem. Tak nie jest. Jedno dodatkowe słowo zmienia zarówno wynik, jak i intensywność pracy bazy danych.

Uruchomiliśmy każde zapytanie w SQLite 3.50, a pokazane wyniki i czasy pochodzą z tych uruchomień. Podstawowym zachowaniem jest standardowy SQL w głównych bazach danych. Czasy będą się jednak różnić w zależności od Twojej bazy danych, danych i sprzętu, dlatego traktuj nasze czasy jako ilustrację.

Krótka odpowiedź

UNION łączy dwa zestawy wyników i zwraca tylko odrębne wiersze. UNION ALL łączy je i zwraca wszystko, łącznie z duplikatami. Na tym polega cała różnica, a reszta tego artykułu dotyczy tego, dlaczego jest to takie ważne.

UNION vs UNION ALL ramię w ramię

Przykładowe dane

Wyobraź sobie sklep, który przechowuje zamówienia online i zamówienia w sklepie stacjonarnym w oddzielnych tabelach. Pamiętaj, że Asha ma dwa identyczne zamówienia online i jedno identyczne zamówienie w sklepie.

CREATE TABLE online_orders (customer TEXT, total INT);
CREATE TABLE store_orders (customer TEXT, total INT);

INSERT INTO online_orders VALUES ('Asha',100), ('Ben',200), ('Asha',100);
INSERT INTO store_orders VALUES ('Asha',100), ('Dev',300);

Zobacz różnicę

SELECT customer, total FROM online_orders
UNION
SELECT customer, total FROM store_orders;

-- Asha | 100
-- Ben  | 200
-- Dev  | 300

SELECT customer, total FROM online_orders
UNION ALL
SELECT customer, total FROM store_orders;

-- Asha | 100
-- Ben  | 200
-- Asha | 100
-- Asha | 100
-- Dev  | 300

Pięć wierszy wejściowych zamieniło się w trzy w przypadku UNION, a wszystkie pięć pozostało w UNION ALL. Warto zwrócić uwagę na dwie rzeczy:

• UNION usunął duplikaty z całego wyniku, łącznie z tymi z pierwszej tabeli. To nie tylko porównywanie między tabelami.
• Wiersz liczy się jako duplikat tylko wtedy, gdy pasuje każda wybrana kolumna.

Jeśli naprawdę chcesz mieć odrębną listę, UNION jest dokładnie w porządku. Dzięki temu każdy klient, który kupił za pośrednictwem dowolnego kanału:

SELECT customer FROM online_orders
UNION
SELECT customer FROM store_orders;

-- Asha
-- Ben
-- Dev

Błędny-całkowity błąd

To jest powód, dla którego wybór jest naprawdę ważny. Załóżmy, że chcesz uzyskać całkowity przychód. Powyższe zamówienia sumują się do 800. Zobacz co daje każdy operator:

SELECT SUM(total) FROM (
  SELECT total FROM online_orders
  UNION
  SELECT total FROM store_orders
);
-- 600

SELECT SUM(total) FROM (
  SELECT total FROM online_orders
  UNION ALL
  SELECT total FROM store_orders
);
-- 800

Wersja UNION jest błędna o 200. Ponieważ wybraliśmy tylko kolumnę total, trzy oddzielne zamówienia o wartości 100 rupii wyglądały jak duplikaty i zwinęły się w jedno. Nie pojawia się żaden komunikat o błędzie, a liczba 600 wygląda na całkowicie wiarygodną liczbę. Tego typu cichy błąd może siedzieć w raporcie finansowym miesiącami.

Zasady, których muszą przestrzegać obaj

Obaj operatorzy mają takie same wymagania:

1. Każde SELECT musi zwracać tę samą liczbę kolumn.
2. Kolumny są dopasowywane według pozycji, a nie według nazwy, dlatego należy zachować tę samą kolejność.
3. Typy danych na każdej pozycji muszą być kompatybilne.
4. Nazwy kolumn wynikowych pochodzą od pierwszego SELECT.
5. ORDER BY znajduje się na samym końcu i dotyczy całego wyniku.

Złamanie pierwszej zasady powoduje błąd. Oto wiadomość od SQLite:

SELECT customer, total FROM online_orders
UNION
SELECT customer FROM store_orders;

-- SELECTs to the left and right of UNION do not have
-- the same number of result columns

Druga zasada jest bardziej niebezpieczna, ponieważ może zawieść bez błędu. Jeśli zamienisz dwie kolumny tego samego typu, na przykład first_name i last_name, zapytanie zostanie uruchomione i dane wyjdą błędne. SQLite ma również swobodne podejście do typów, podczas gdy bazy danych takie jak PostgreSQL są bardziej rygorystyczne i mogą wymagać jawnego określenia CAST.

Oznacz źródło

Bardzo praktyczną sztuczką jest dodanie stałej kolumny pokazującej, skąd pochodzi każdy wiersz. Ponieważ etykiety są różne, wiersze z różnych tabel nie mogą być już traktowane jako duplikaty.

SELECT customer, total, 'online' AS channel FROM online_orders
UNION ALL
SELECT customer, total, 'store' FROM store_orders
ORDER BY customer, channel;

-- Asha | 100 | online
-- Asha | 100 | online
-- Asha | 100 | store
-- Ben  | 200 | online
-- Dev  | 300 | store

Gdy dane otrzymają etykietę, możesz je pogrupować, aby raportować sprzedaż według kanałów, korzystając z opcji GROUP BY. Jeśli chcesz połączyć kilka kroków, zawiń związek w CTE, aby był czytelny.

Wydajność: co wydarzyło się w naszym teście

Aby usunąć duplikaty, baza danych musi porównać wiersze, zwykle sortując je lub mieszając. UNION ALL może pominąć ten krok. Stworzyliśmy dwie tabele po 500 000 wierszy każda, zawierające losowe liczby od 0 do 50 000, więc duplikatów było mnóstwo. Każde zapytanie uruchamialiśmy kilka razy i utrzymaliśmy najlepszy czas.

Najpierw policzyliśmy wyniki, aby czas spędzony na wysyłaniu wierszy z powrotem do naszego programu nie miał wpływu na porównanie:

Ogromna luka wynika częściowo z tego, że SQLite może obliczyć tę liczbę bez czytania każdego wiersza, więc proszę nie czytać tego jako „tysiące razy szybciej”. Pokazuje jednak podstawową ideę: UNION musiał wykonać prawdziwą pracę, a UNION ALL nie.

Następnie pobraliśmy wszystkie wiersze do Pythona i wynik się odwrócił:

W tym przypadku UNION ALL trwało dłużej, ponieważ dostarczyło dwadzieścia razy więcej wierszy. Deduplikacja nie jest tym, co ją spowalnia. Obsługa miliona wierszy była. Zatem uczciwą zasadą jest to, że obliczenia UNION ALL są tańsze dla bazy danych, ale wielkość wyniku też ma znaczenie. Wiele wykresów online redukuje to do „UNION ALL jest zawsze szybszy”, a nasz test pokazuje, dlaczego nie jest to takie proste.

Nie wybieraj też wyłącznie na podstawie prędkości. Wybierz operatora, który jako pierwszy podał poprawną odpowiedź, a następnie dostrój. Jeśli zapytanie działa wolno, przeczytaj nasz przewodnik po optymalizacji zapytań SQL.

Kiedy używać każdego z nich

Użyj UNION ALL, gdy:

• Łączysz dane z podobnych tabel, np. partycji miesięcznych.
• Później obliczysz sumy lub zliczenia.
• Wiadomo, że dane wejściowe nie mogą się nakładać.
• Duplikaty mają znaczenie, podobnie jak w dziennikach lub zapisach audytu.

Użyj UNION, gdy:

• Potrzebujesz unikalnej listy, takiej jak każdy adres e-mail w dwóch systemach.
• Nakładające się źródła podają ten sam fakt więcej niż jeden raz.

Traktuj UNION ALL jako wartość domyślną i używaj UNION celowo. Dodanie klauzuli WHERE do każdej gałęzi w celu wczesnego filtrowania pomaga w obu przypadkach.

PRZECIĘCIE i WYJĄTEK

Dwa powiązane operatory zbiorowe odpowiadają na różne pytania. W obu przypadkach obowiązują te same zasady dotyczące kolumn:

SELECT customer FROM online_orders
INTERSECT
SELECT customer FROM store_orders;
-- Asha  (bought in both channels)

SELECT customer FROM online_orders
EXCEPT
SELECT customer FROM store_orders;
-- Ben   (online only)

Oracle tradycyjnie używał MINUS zamiast EXCEPT, a obsługa tych operatorów różni się nieco w zależności od bazy danych i wersji.

UNIA kontra DOŁĄCZENIE

Często są one mylone, ale łączą dane w różnych kierunkach:

Typowe błędy

• Używanie UNION z przyzwyczajenia. Może to kosztować prędkość lub usunięcie prawidłowych wierszy.
• Wybór zbyt małej liczby kolumn. Pominięcie identyfikatora powoduje, że różne wiersze wyglądają identycznie.
• Niezgodna kolejność kolumn. Wartości mogą trafić do niewłaściwej kolumny bez żadnego błędu.
• Dodanie ORDER BY do każdej gałęzi. Sortuj raz, na końcu.
• Użycie UNION do załatania złego złączenia. Jeśli w wyniku złączenia pojawiają się duplikaty, napraw to złączenie.
• Zakładając kolejność wyników. Bez ORDER BY kolejność wierszy nie jest gwarantowana dla żadnego operatora.

Podsumowanie

Za każdym razem zadawaj jedno pytanie: czy identyczne wiersze są tym samym faktem, czy też faktami odrębnymi? Jeśli są one oddzielne lub jeśli nakładanie się nie jest możliwe, użyj UNION ALL. Jeśli potrzebujesz unikalnej listy, użyj UNION. Aby sprawdzić własne raporty, wyszukaj w swoim kodzie UNION w pobliżu SUM lub COUNT i potwierdź, że te sumy naprawdę są prawidłowe.

Najczęstsze pytania

Ucz się dalej