Jaka jest różnica między UNION a UNION ALL?


Odpowiedzi:


1734

UNIONusuwa zduplikowane rekordy (gdzie wszystkie kolumny w wynikach są takie same), UNION ALLnie robi.

Użycie UNIONzamiast powoduje pogorszenie wydajności UNION ALL, ponieważ serwer bazy danych musi wykonać dodatkową pracę, aby usunąć zduplikowane wiersze, ale zwykle nie chce się duplikatów (szczególnie podczas opracowywania raportów).

Przykład UNII:

SELECT 'foo' AS bar UNION SELECT 'foo' AS bar

Wynik:

+-----+
| bar |
+-----+
| foo |
+-----+
1 row in set (0.00 sec)

Przykład UNION ALL:

SELECT 'foo' AS bar UNION ALL SELECT 'foo' AS bar

Wynik:

+-----+
| bar |
+-----+
| foo |
| foo |
+-----+
2 rows in set (0.00 sec)

179
Konsekwencją tego jest to, że związek jest znacznie mniej wydajny, ponieważ musi skanować wynik w poszukiwaniu duplikatów
Matthew Watson,

19
UNION ALL rzeczywiście będzie bardziej wydajny, szczególnie ze względu na brak wyraźnego rodzaju. Moja ogólna praktyka polega na stosowaniu UNION ALL, chyba że konkretnie chcę kopii.
Adam Caviness,

6
Zauważyłem, że jest tu wiele dobrych komentarzy / odpowiedzi, więc włączyłem flagę wiki i dodałem notatkę o wydajności ...
Jim Harte

250
UNION ALL może być wolniejszy niż UNION w rzeczywistych przypadkach, w których sieć taka jak Internet jest wąskim gardłem. Koszt przeniesienia wielu zduplikowanych wierszy może przekroczyć korzyść z czasu wykonania zapytania. Należy to przeanalizować indywidualnie dla każdego przypadku.
Charles Burns,

23
@AdamCaviness Twój komentarz nie ma sensu.
kojow7

285

Zarówno UNION, jak i UNION ALL łączą wynik dwóch różnych zapytań SQL. Różnią się sposobem obsługi duplikatów.

  • UNION wykonuje DISTINCT na zestawie wyników, eliminując wszelkie zduplikowane wiersze.

  • UNION ALL nie usuwa duplikatów, a zatem jest szybszy niż UNION.

Uwaga: podczas korzystania z tych poleceń wszystkie wybrane kolumny muszą być tego samego typu danych.

Przykład: jeśli mamy dwie tabele, 1) pracownik i 2) klient

  1. Dane w tabeli pracowników:

wprowadź opis zdjęcia tutaj

  1. Dane tabeli klientów:

wprowadź opis zdjęcia tutaj

  1. Przykład UNION (Usuwa wszystkie zduplikowane rekordy):

wprowadź opis zdjęcia tutaj

  1. Przykład UNION ALL (po prostu łączy rekordy, nie eliminuje duplikatów, więc jest szybszy niż UNION):

wprowadź opis zdjęcia tutaj


3
„wszystkie wybrane kolumny muszą być tego samego typu danych” - w rzeczywistości rzeczy nie są tak surowe (nie jest to dobra rzecz z punktu widzenia modelu relacyjnego!). Standard SQL mówi, że ich odpowiedni deskryptor kolumny musi być taki sam, z wyjątkiem nazwy.
onedaywhe

47

UNIONusuwa duplikaty, podczas UNION ALLgdy nie.

Aby usunąć duplikaty, zestaw wyników musi zostać posortowany, co może mieć wpływ na wydajność UNION, w zależności od ilości sortowanych danych oraz ustawień różnych parametrów RDBMS (dla Oracle PGA_AGGREGATE_TARGETz WORKAREA_SIZE_POLICY=AUTOlub SORT_AREA_SIZEi SOR_AREA_RETAINED_SIZEjeśli WORKAREA_SIZE_POLICY=MANUAL).

Zasadniczo sortowanie jest szybsze, jeśli można je przeprowadzić w pamięci, ale obowiązuje to samo zastrzeżenie dotyczące ilości danych.

Oczywiście, jeśli potrzebujesz danych zwróconych bez duplikatów, musisz użyć UNION, w zależności od źródła danych.

Skomentowałbym pierwszy post, aby zakwalifikować komentarz „jest znacznie mniej wydajny”, ale nie mam wystarczającej reputacji (punktów), aby to zrobić.


1
„Aby usunąć duplikaty, zestaw wyników należy posortować” - być może masz na myśli konkretnego dostawcę, ale w pytaniu nie ma tagów specyficznych dla tego dostawcy. Nawet jeśli tak, czy możesz udowodnić, że duplikatów nie można usunąć bez sortowania?
poniedziałek

2
odrębne „niejawnie” posortuje wyniki, ponieważ usuwanie duplikatów jest szybsze w posortowanym zestawie. nie oznacza to, że zwrócony zestaw wyników jest faktycznie sortowany w ten sposób, ale w większości przypadków odrębne (a zatem UNION) wewnętrznie sortują zestaw wyników.
DevilSuichiro



13

Możesz uniknąć duplikatów i nadal działać znacznie szybciej niż UNION DISTINCT (który w rzeczywistości jest taki sam jak UNION), uruchamiając takie zapytanie:

SELECT * FROM mytable WHERE a=X UNION ALL SELECT * FROM mytable WHERE b=Y AND a!=X

Zwróć uwagę na AND a!=Xczęść. Jest to o wiele szybsze niż UNION.


4
Pominie to wiersze, a zatem nie da oczekiwanego wyniku, jeśli zawiera wartości NULL. Ponadto nadal nie zwraca tego samego wyniku co a UNION- UNIONusuwa również duplikaty zwracane przez podzapytania, podczas gdy twoje podejście nie.
Frank Schmitt,

@FrankSchmitt - dziękuję za tę odpowiedź; ten fragment dotyczący podkwerend jest dokładnie tym, co chciałem wiedzieć!
Doradus

11

Wystarczy dodać tutaj moje dwa centy: można zrozumieć UNIONoperatora jako czystą, zorientowaną na SET UNIĘ - np. Zestaw A = {2,4,6,8}, zestaw B = {1,2,3,4 }, A UNION B = {1,2,3,4,6,8}

Gdy mamy do czynienia z zestawami, nie chcesz numery 2 i 4 pojawia się dwukrotnie, jako element albo jest albo nie jest w zestawie.

Jednak w świecie SQL możesz chcieć zobaczyć wszystkie elementy z dwóch zestawów razem w jednym „worku” {2,4,6,8,1,2,3,4}. I w tym celu T-SQL oferuje operatorowi UNION ALL.


2
Nitpick: UNION ALLnie jest „oferowany” przez T-SQL. UNION ALLjest częścią standardu ANSI SQL i nie jest specyficzny dla MS SQL Server.
Frank Schmitt

1
Komentarz „Nitpick” może sugerować, że nie można używać „Union All” w TSQL, ale można. Oczywiście komentarz tego nie mówi , ale ktoś, kto go przeczyta, może wywnioskować.
JosephDoggie

10

UNIA komenda służy do wyboru informacji związanych z dwóch tabel, podobnie jak komendy. Jednak podczas korzystania z polecenia wszystkie wybrane kolumny muszą być tego samego typu danych. Za pomocą wybierane są tylko odrębne wartości.
UNIONJOINUNIONUNION

UNION ALL komenda jest równa poleceniu, poza tym, że wybiera wszystkie wartości.
UNION ALLUNIONUNION ALL

Różnica między Unioni Union allpolega na tym, Union allże nie wyeliminuje zduplikowanych wierszy, zamiast tego wyciąga wszystkie wiersze ze wszystkich tabel pasujących do specyfikacji zapytania i łączy je w tabelę.

UNIONOświadczenie skutecznie robi SELECT DISTINCTna zbiorze wyników. Jeśli wiesz, że wszystkie zwrócone rekordy są unikalne z twojego związku, użyj UNION ALLzamiast tego, daje to szybsze wyniki.


8

Nie jestem pewien, czy ma to znaczenie, która baza danych

UNIONi UNION ALLpowinien działać na wszystkich serwerach SQL.

Należy unikać niepotrzebnych, UNIONponieważ są one ogromnym przeciekiem wydajności. Zasadą jest używanie kciuka, UNION ALLjeśli nie jesteś pewien, którego użyć.


W tym pytaniu nie ma znacznika SQL Server. Myślę, że opcja, która zwraca duplikaty tylko dlatego, że zwykle działa najlepiej, jest złą poradą.
onedaywhe

1
@oneday, kiedy myślę, że OP użył wyrażenia „SQL Server” jako synonim wszystkich RDBMS (np. MySQL, PostGreSQL, Oracle, SQL Server). Sformułowanie jest jednak niefortunne (i oczywiście mogę się mylić).
Frank Schmitt

@FrankSchmitt: żaden z wymienionych produktów nie jest prawdziwie RDBMS :)
onedaywhen

1
@onedayKiedy chcesz opracować? Przynajmniej en.wikipedia.org/wiki/Relational_database_management_system wydaje się ze mną zgadzać - wyraźnie wspomina o Microsoft SQL Server, Oracle Database i MySQL. A może jesteś podejrzliwy w kwestii różnicy między Oracle a Oracle Database np.?
Frank Schmitt

8

UNION - wyniki w odrębnych rekordach,

a

UNION ALL - wyniki we wszystkich rekordach, w tym duplikatach.

Oba są operatorami blokującymi, dlatego osobiście wolę używać JOINS niż Blocking Operators (UNION, INTERSECT, UNION ALL itp.) W dowolnym momencie.

Aby zilustrować, dlaczego operacja Unii działa słabo w porównaniu do operacji Union All, sprawdź następujący przykład.

CREATE TABLE #T1 (data VARCHAR(10))

INSERT INTO #T1
SELECT 'abc'
UNION ALL
SELECT 'bcd'
UNION ALL
SELECT 'cde'
UNION ALL
SELECT 'def'
UNION ALL
SELECT 'efg'


CREATE TABLE #T2 (data VARCHAR(10))

INSERT INTO #T2
SELECT 'abc'
UNION ALL
SELECT 'cde'
UNION ALL
SELECT 'efg'

wprowadź opis zdjęcia tutaj

Poniżej przedstawiono wyniki operacji UNION ALL i UNION.

wprowadź opis zdjęcia tutaj

Instrukcja UNION skutecznie WYRÓŻNIA WYBÓR na zestawie wyników. Jeśli wiesz, że wszystkie zwrócone rekordy są unikalne z twojego związku, użyj zamiast tego UNION ALL, daje to szybsze wyniki.

Użycie UNION powoduje operacje Sortowania odrębnego w planie wykonania. Dowód na potwierdzenie tego stwierdzenia pokazano poniżej:

wprowadź opis zdjęcia tutaj


3
Wszystko w tej odpowiedzi zostało już powiedziane, jest zbyt mylące, aby było użyteczne (sugerowanie przyłączeń do związków, gdy robią różne rzeczy, podając „blokowanie” jako przyczynę bez wyjaśnienia, co masz na myśli przez to lub do których serwerów bazy danych ma zastosowanie), lub jest bardzo mylące (twoje wartości procentowe na zrzucie ekranu nie mają zastosowania do rzeczywistego rzeczywistego użycia UNION/ UNION ALL).

Operatory blokujące są dobrze znanymi operatorami w TSQL. Wszystko, co robią operatorzy blokujący, można osiągnąć dzięki łączeniom, ale nie odwrotnie. Operacja Sortowania odrębnego jest zaznaczona na zdjęciu, aby pokazać, dlaczego unia działa lepiej niż unia, a także, aby dokładnie pokazać, gdzie ona istnieje w planie wykonania. Dodaj więcej danych do tabel T1 i T2, aby pobawić się procentami!
DBA,

Technicznie MOŻESZ wygenerować wyniki unionużycia kombinacji joins i niektórych naprawdę paskudnych cases, ale to sprawia, że ​​zapytanie jest prawie niemożliwe do odczytania i utrzymania, a z mojego doświadczenia wynika, że ​​jest to również straszne z punktu widzenia wydajności. Porównaj: select foo.bar from foo union select fizz.buzz from fizzprzeciwselect case when foo.bar is null then fizz.buzz else foo.bar end from foo join fizz where foo.bar is null or fizz.buzz is null
Devin Lamothe

@DBA Twoja odpowiedź dotyczy tylko użytkowników MS SQL Server. OP nigdy nie wspomniał o RDBMS, którego używają - mogą używać MySQL, PostgreSQL, Oracle, SQLite, ...
Frank Schmitt

6

union służy do wybierania różnych wartości z dwóch tabel, a jako union all służy do wybierania wszystkich wartości, w tym duplikatów z tabel


6

Dobrze jest to zrozumieć za pomocą diagramu Venna.

tutaj jest link do źródła. Jest dobry opis.

wprowadź opis zdjęcia tutaj


5
Twoje drugie zdjęcie sugeruje, że oba są wykluczające się wzajemnie, jeśli nie są. Zdjęcie powinno raczej pokazywać to samo co pierwsze, ale z „elipsą przecięcia” ()pokazaną po raz drugi. Właściwie, po namyśle, ponieważ union allwynik nie jest zbiorem, nie powinieneś próbować rysować go za pomocą diagramu Venna!
onedaywhen

5

(Z Microsoft SQL Server Book Online)

UNIA [WSZYSTKO]

Określa, że ​​wiele zestawów wyników ma być łączonych i zwracanych jako pojedynczy zestaw wyników.

WSZYSTKO

Uwzględnia wszystkie wiersze w wynikach. Obejmuje to duplikaty. Jeśli nie zostanie określony, zduplikowane wiersze zostaną usunięte.

UNIONpotrwa zbyt długo, gdy DISTINCTw wynikach zostaną zastosowane duplikaty znalezienia podobnych wierszy .

SELECT * FROM Table1
UNION
SELECT * FROM Table2

odpowiada:

SELECT DISTINCT * FROM (
    SELECT * FROM Table1
    UNION ALL
    SELECT * FROM Table2) DT

Efektem ubocznym nakładania DISTINCTwyników jest operacja sortowania wyników.

UNION ALLwyniki będą wyświetlane w dowolnej kolejności według wyników, ale UNIONwyniki zostaną pokazane tak, jak ORDER BY 1, 2, 3, ..., n (n = column number of Tables)zastosowano do wyników. Możesz zobaczyć ten efekt uboczny, gdy nie masz duplikatu wiersza.


5

Dodaję przykład

UNION , łączy się z wyraźnym -> wolniejszym, ponieważ wymaga porównania (w deweloperze Oracle SQL wybierz zapytanie, naciśnij F10, aby zobaczyć analizę kosztów).

UNION ALL , łączy się bez wyraźnego -> szybciej.

SELECT to_date(sysdate, 'yyyy-mm-dd') FROM dual
UNION
SELECT to_date(sysdate, 'yyyy-mm-dd') FROM dual;

i

SELECT to_date(sysdate, 'yyyy-mm-dd') FROM dual
UNION ALL
SELECT to_date(sysdate, 'yyyy-mm-dd') FROM dual;

2

UNION scala zawartość dwóch strukturalnie zgodnych tabel w jeden połączony stół.

  • Różnica:

Różnica między UNIONi UNION ALLpolega na tym, że UNION willpomijamy zduplikowane rekordy, podczas gdy UNION ALLbędą one zawierać zduplikowane rekordy.

UnionZestaw wyników jest sortowany w porządku rosnącym, natomiast UNION ALLzestaw wyników nie jest sortowany

UNIONwykonuje a DISTINCTna swoim zestawie wyników, aby wyeliminować wszelkie zduplikowane wiersze. Natomiast UNION ALLnie usunie duplikatów i dlatego jest szybszy niż UNION. *

Uwaga : Wydajność UNION ALLzwykle będzie lepsza niż UNION, ponieważ UNIONwymaga od serwera dodatkowej pracy w celu usunięcia wszelkich duplikatów. Tak więc w przypadkach, w których jest pewne, że nie będzie żadnych duplikatów, lub gdy posiadanie duplikatów nie stanowi problemu, użycie UNION ALLbyłoby zalecane ze względu na wydajność.


1
„Zestaw wyników Unii jest sortowany w porządku rosnącym” - chyba że istnieje ORDER BY, posortowane wyniki nie są gwarantowane. Być może masz na myśli konkretnego dostawcę SQL (nawet wtedy, w porządku rosnącym, co dokładnie ...?), Ale to pytanie nie ma dostawcy = określone tagi.
onedaywhe

„scala zawartość dwóch strukturalnie kompatybilnych tabel” - myślę, że dobrze to określiłeś :)
onedaywhen

2

Załóżmy, że masz dwa stołowego Teacher & Student

Oba mają 4 kolumny z inną nazwą jak ta

Teacher - ID(int), Name(varchar(50)), Address(varchar(50)), PositionID(varchar(50))

wprowadź opis zdjęcia tutaj

Student- ID(int), Name(varchar(50)), Email(varchar(50)), PositionID(int)

wprowadź opis zdjęcia tutaj

Możesz zastosować UNION lub UNION ALL do tych dwóch tabel, które mają tę samą liczbę kolumn. Ale mają inną nazwę lub typ danych.

Zastosowanie UNIONoperacji na 2 tabelach powoduje pominięcie wszystkich zduplikowanych wpisów (wartość wszystkich kolumn wiersza w tabeli jest taka sama jak innej tabeli). Lubię to

SELECT * FROM Student
UNION
SELECT * FROM Teacher

wynik będzie

wprowadź opis zdjęcia tutaj

Zastosowanie UNION ALLoperacji na 2 tabelach powoduje zwrócenie wszystkich pozycji z duplikatem (jeśli istnieje jakakolwiek różnica między wartością kolumny w wierszu w 2 tabelach). Lubię to

SELECT * FROM Student
UNION ALL
SELECT * FROM Teacher

Wynik wprowadź opis zdjęcia tutaj

Wydajność:

Oczywiście wydajność UNION ALL jest lepsza niż UNION, ponieważ wykonują dodatkowe zadanie, aby usunąć zduplikowane wartości. Możesz to sprawdzić w Szacowanym czasie wykonania, naciskając ctrl + L w MSSQL


Naprawdę? Aby uzyskać wynik czterorzędowy ?! Sądzę, że jest to scenariusz, w którym chciałbyś wykorzystać UNIONprzekazanie zamiaru (tj. Brak duplikatów), ponieważ UNION ALLjest mało prawdopodobne, aby uzyskać rzeczywisty wzrost wydajności w wartościach bezwzględnych.
onedaywhe

2

W bardzo prostych słowach różnica między UNION a UNION ALL polega na tym, że UNION pominie zduplikowane rekordy, podczas gdy UNION ALL będzie zawierać zduplikowane rekordy.


1

Jeszcze jedną rzecz, którą chciałbym dodać

Unia : - Zestaw wyników jest sortowany w porządku rosnącym.

Połącz wszystkie : - Zestaw wyników nie jest sortowany. dwa dane wyjściowe zapytania są właśnie dołączane.


Prawdziwe ! UNION może zmienić kolejność dwóch wyników cząstkowych.
gracchus

6
To jest źle. UNIONBędzie NIE sortowania wynik w porządku rosnącym. Każde zamówienie, które zobaczysz w wyniku bez użycia, order byjest czystym przypadkiem. DBMS może swobodnie korzystać ze strategii, które jej zdaniem są skuteczne w usuwaniu duplikatów. Może to być sortowanie, ale może to być również algorytm mieszający lub coś zupełnie innego - a strategia zmieni się wraz z liczbą wierszy. A, unionktóry wydaje się posortowany ze 100 wierszami, może nie być ze 100 000 wierszy
a_horse_w_no_name 27.04.16

2
Bez klauzuli ORDER BY w zapytaniu RDBMS może dowolnie zwracać wiersze w dowolnej kolejności. Obserwacja, że ​​zestaw wyników operacji UNION jest zwracany „w porządku rosnącym”, jest jedynie produktem ubocznym operacji „sortuj unikalne” wykonywanej przez bazę danych. Zachowane zachowanie nie jest gwarantowane. Więc nie polegaj na tym. Jeśli specyfikacja ma zwracać wiersze w określonej kolejności, dodaj odpowiednią ORDER BYklauzulę.
spencer7593,

1

Różnica między Union Vs Union ALL W Sql

Co to jest Union In SQL?

Operator UNION służy do łączenia zestawu wyników dwóch lub więcej zbiorów danych.

Each SELECT statement within UNION must have the same number of columns
The columns must also have similar data types
The columns in each SELECT statement must also be in the same order

Union Vs Union Wszystko z przykładem


1

Ważny! Różnica między Oracle a MySQL: powiedzmy, że t1 t2 nie mają między sobą zduplikowanych wierszy, ale mają pojedyncze zduplikowane wiersze. Przykład: t1 ma sprzedaż od 2017 r., A t2 od 2018 r

SELECT T1.YEAR, T1.PRODUCT FROM T1

UNION ALL

SELECT T2.YEAR, T2.PRODUCT FROM T2

W ORACLE UNION ALL pobiera wszystkie wiersze z obu tabel. To samo będzie miało miejsce w MySQL.

Jednak:

SELECT T1.YEAR, T1.PRODUCT FROM T1

UNION

SELECT T2.YEAR, T2.PRODUCT FROM T2

W ORACLE UNION pobiera wszystkie wiersze z obu tabel, ponieważ między t1 i t2 nie ma zduplikowanych wartości. Z drugiej strony w MySQL zestaw wyników będzie miał mniej wierszy, ponieważ w tabeli t1, a także w tabeli t2 będą zduplikowane wiersze!


0

Z drugiej strony UNION usuwa zduplikowane rekordy. UNION ALL tego nie robi. Ale trzeba sprawdzić większość danych, które będą przetwarzane, a kolumna i typ danych muszą być takie same.

ponieważ związek używa wewnętrznie „wyraźnego” zachowania do wybierania wierszy, dlatego jest to bardziej kosztowne pod względem czasu i wydajności. lubić

select project_id from t_project
union
select project_id from t_project_contact  

daje mi to 2020 rekordów

z drugiej strony

select project_id from t_project
union all
select project_id from t_project_contact

daje mi ponad 17402 wierszy

w perspektywie pierwszeństwa oba mają takie same pierwszeństwo.


0

Jeśli nie ORDER BY, a UNION ALLmoże przywołać wiersze z powrotem, gdy jedzie, a a UNIONsprawi, że poczekasz do samego końca zapytania, zanim podasz cały zestaw wyników naraz. Może to mieć znaczenie w sytuacji UNION ALLprzekroczenia limitu czasu - utrzymuje połączenie przy życiu.

Więc jeśli masz problem z czasem oczekiwania i nie ma sortowania, a duplikaty nie są problemem, UNION ALLmoże być raczej pomocne.


Ale twoja pierwsza część wyników może być wielokrotnie powtarzana w jednym wierszu: jak to jest przydatne ?!
onedaywhe

0

UNION i UNION ALL służą do łączenia dwóch lub więcej wyników zapytań.

Polecenie UNION wybiera odrębne i powiązane informacje z dwóch tabel, co eliminuje duplikaty wierszy.

Z drugiej strony polecenie UNION ALL wybiera wszystkie wartości z obu tabel, które wyświetlają wszystkie wiersze.


0

Jako nawyk zawsze używaj UNION ALL . Używaj tylko UNION w szczególnych przypadkach, gdy musisz wyeliminować duplikaty, które mogą być bardzo nieuporządkowane i możesz przeczytać o tym w innych komentarzach tutaj.


0

UNION ALLdziała również na większej liczbie typów danych. Na przykład podczas próby połączenia typów danych przestrzennych. Na przykład:

select a.SHAPE from tableA a
union
select b.SHAPE from tableB b

rzuci

The data type geometry cannot be used as an operand to the UNION, INTERSECT or EXCEPT operators because it is not comparable.

Jednak union allnie będzie.


-1

Jedyną różnicą jest:

„UNION” usuwa zduplikowane wiersze.

„UNION ALL” nie usuwa duplikatów wierszy.


13
Jak to wnosi jakąkolwiek wartość w porównaniu do zaakceptowanej odpowiedzi?
Nick

@Nick Jest to krótsza odpowiedź.
Mostafa Vatanpour

Krótszy może być zaletą, jeśli musisz przeczytać znaczną część zaakceptowanej odpowiedzi, aby uzyskać te dane. Ale w tym przypadku zaakceptowana odpowiedź zawiera wszystkie te informacje w pierwszym zdaniu, po którym następuje szczegółowe omówienie implikacji różnicy.
dmckee --- były moderator kociak
Korzystając z naszej strony potwierdzasz, że przeczytałeś(-aś) i rozumiesz nasze zasady używania plików cookie i zasady ochrony prywatności.
Licensed under cc by-sa 3.0 with attribution required.