![](https://maz.kr/wp-content/uploads/2022/09/sql-union-thumbnail.png)
UNION
UNION 연산자는 두 개 이상의 SELECT 문의 결과 집합을 결합하는 데 사용된다.
- UNION 내의 모든 SELECT 문에는 동일한 수의 열이 있어야한다.
- 열에도 유사한 데이터 형식이 있어야한다.
- 모든 SELECT 명령문의 열도 같은 순서여야 한다.
구문
UNION 구문
UNION 연산자는 기본적으로 고유한 값만 선택한다.
SELECT column_name(s) FROM table1 UNION SELECT column_name(s) FROM table2;
UNION ALL 구문
중복 값을 허용하려면 UNION ALL을 사용한다.
SELECT column_name(s) FROM table1 UNION ALL SELECT column_name(s) FROM table2;
데이터베이스 예시
고객 테이블
CustomerID | CustomerName | ContactName | Address | City | PostalCode | Country |
---|---|---|---|---|---|---|
1 | Alfreds Futterkiste | Maria Anders | Obere Str. 57 | Berlin | 12209 | Germany |
2 | Ana Trujillo Emparedados y helados | Ana Trujillo | Avda. de la Constitución 2222 | México D.F. | 05021 | Mexico |
3 | Antonio Moreno Taquería | Antonio Moreno | Mataderos 2312 | México D.F. | 05023 | Mexico |
공급 업체 테이블
SupplierID | SupplierName | ContactName | Address | City | PostalCode | Country |
---|---|---|---|---|---|---|
1 | Exotic Liquid | Charlotte Cooper | 49 Gilbert St. | London | EC1 4SD | UK |
2 | New Orleans Cajun Delights | Shelley Burke | P.O. Box 78934 | New Orleans | 70117 | USA |
3 | Grandma Kelly’s Homestead | Regina Murphy | 707 Oxford Rd. | Ann Arbor | 48104 | USA |
“고객” 및 “공급자” 테이블에서 도시(고유 값만 반환)를 반환
일부 고객 또는 공급 업체가 동일한 도시를 갖는 경우 고유 한 값 만 선택하기 때문에 각 도시는 한 번만 나열된다.
SELECT City FROM Customers UNION SELECT City FROM Suppliers ORDER BY City;
기본 예시
City |
---|
Aachen |
Albuquerque |
Anchorage |
Ann Arbor |
Annecy |
Barcelona |
Barquisimeto |
Bend |
Bergamo |
Berlin |
Bern |
Boise |
Boston |
Brandenburg |
Bruxelles |
Bräcke |
Buenos Aires |
Butte |
Campinas |
Caracas |
Charleroi |
Cork |
Cowes |
Cunewalde |
Cuxhaven |
Elgin |
Eugene |
Frankfurt |
Frankfurt a.M. |
Genève |
Graz |
Göteborg |
Helsinki |
I. de Margarita |
Kirkland |
Köln |
København |
Lander |
Lappeenranta |
Leipzig |
Lille |
Lisboa |
London |
Londona |
Luleå |
Lyngby |
Lyon |
Madrid |
Manchester |
Mannheim |
Marseille |
Melbourne |
Montceau |
Montréal |
México D.F. |
München |
Münster |
Nantes |
New Orleans |
Osaka |
Oulu |
Oviedo |
Paris |
Portland |
Ravenna |
Reggio Emilia |
Reims |
Resende |
Rio de Janeiro |
Salerno |
Salzburg |
San Cristóbal |
San Francisco |
Sandvika |
Seattle |
Sevilla |
Singapore |
Stavern |
Ste-Hyacinthe |
Stockholm |
Strasbourg |
Stuttgart |
Sydney |
São Paulo |
Tokyo |
Torino |
Toulouse |
Tsawassen |
Vancouver |
Versailles |
Walla |
Walla Walla |
Zaandam |
Århus |
“고객” 및 “공급자” 테이블 모두에서 도시(중복 값)를 반환
SELECT City FROM Customers UNION ALL SELECT City FROM Suppliers ORDER BY City;
기본 예시
City |
---|
Aachen |
Albuquerque |
Anchorage |
Ann Arbor |
Annecy |
Barcelona |
Barquisimeto |
Bend |
Bergamo |
Berlin |
Berlin |
Bern |
Boise |
Boston |
Brandenburg |
Bruxelles |
Bräcke |
Buenos Aires |
Buenos Aires |
Buenos Aires |
Butte |
Campinas |
Caracas |
Charleroi |
Cork |
Cowes |
Cunewalde |
Cuxhaven |
Elgin |
Eugene |
Frankfurt |
Frankfurt a.M. |
Genève |
Graz |
Göteborg |
Helsinki |
I. de Margarita |
Kirkland |
Köln |
København |
Lander |
Lappeenranta |
Leipzig |
Lille |
Lisboa |
Lisboa |
London |
London |
London |
London |
London |
London |
Londona |
Luleå |
Lyngby |
Lyon |
Madrid |
Madrid |
Madrid |
Manchester |
Mannheim |
Marseille |
Melbourne |
Montceau |
Montréal |
Montréal |
México D.F. |
México D.F. |
México D.F. |
México D.F. |
México D.F. |
München |
Münster |
Nantes |
Nantes |
New Orleans |
Osaka |
Oulu |
Oviedo |
Paris |
Paris |
Paris |
Portland |
Portland |
Ravenna |
Reggio Emilia |
Reims |
Resende |
Rio de Janeiro |
Rio de Janeiro |
Rio de Janeiro |
Salerno |
Salzburg |
San Cristóbal |
San Francisco |
Sandvika |
Seattle |
Sevilla |
Singapore |
Stavern |
Ste-Hyacinthe |
Stockholm |
Strasbourg |
Stuttgart |
Sydney |
São Paulo |
São Paulo |
São Paulo |
São Paulo |
São Paulo |
Tokyo |
Torino |
Toulouse |
Tsawassen |
Vancouver |
Versailles |
Walla |
Walla Walla |
Zaandam |
Århus |
“고객” 및 “공급자” 테이블에서 독일 도시(고유 값만 반환)를 반환
SELECT City, Country FROM Customers WHERE Country='Germany' UNION SELECT City, Country FROM Suppliers WHERE Country='Germany' ORDER BY City;
기본 예시
City | Country |
---|---|
Aachen | Germany |
Berlin | Germany |
Brandenburg | Germany |
Cunewalde | Germany |
Cuxhaven | Germany |
Frankfurt | Germany |
Frankfurt a.M. | Germany |
Köln | Germany |
Leipzig | Germany |
Mannheim | Germany |
München | Germany |
Münster | Germany |
Stuttgart | Germany |
“고객” 및 “공급자” 테이블에서 독일 도시(중복 값도 반환함)를 반환
SELECT City, Country FROM Customers WHERE Country='Germany' UNION ALL SELECT City, Country FROM Suppliers WHERE Country='Germany' ORDER BY City;
기본 예시
City | Country |
---|---|
Aachen | Germany |
Berlin | Germany |
Berlin | Germany |
Brandenburg | Germany |
Cunewalde | Germany |
Cuxhaven | Germany |
Frankfurt | Germany |
Frankfurt a.M. | Germany |
Köln | Germany |
Leipzig | Germany |
Mannheim | Germany |
München | Germany |
Münster | Germany |
Stuttgart | Germany |
모든 고객 및 공급자를 나열
“AS 유형”은 별칭이다.
링크은 테이블이나 열에 임시 이름을 지정하는 데 사용된다.
별칭은 쿼리 기간 동안만 존재한다.
그래서 여기서 “유형”이라는 임시 열을 만들었다.
이 열은 담당자가 “고객”또는 “공급 업체”인지 여부를 나열한다.
SELECT 'Customer' AS Type, ContactName, City, Country FROM Customers UNION SELECT 'Supplier', ContactName, City, Country FROM Suppliers;
기본 예시
Type | ContactName | City | Country |
---|---|---|---|
Customer | Alejandra Camino | Madrid | Spain |
Customer | Alexander Feuer | Leipzig | Germany |
Customer | Ana Trujillo | México D.F. | Mexico |
Customer | Anabela Domingues | São Paulo | Brazil |
Customer | André Fonseca | Campinas | Brazil |
Customer | Ann Devon | London | UK |
Customer | Annette Roulet | Toulouse | France |
Customer | Antonio Moreno | México D.F. | Mexico |
Customer | Aria Cruz | São Paulo | Brazil |
Customer | Art Braunschweiger | Lander | USA |
Customer | Bernardo Batista | Rio de Janeiro | Brazil |
Customer | Carine Schmitt | Nantes | France |
Customer | Carlos González | Barquisimeto | Venezuela |
Customer | Carlos Hernández | San Cristóbal | Venezuela |
Customer | Catherine Dewey | Bruxelles | Belgium |
Customer | Christina Berglund | Luleå | Sweden |
Customer | Daniel Tonini | Versailles | France |
Customer | Diego Roel | Madrid | Spain |
Customer | Dominique Perrier | Paris | France |
Customer | Eduardo Saavedra | Barcelona | Spain |
Customer | Elizabeth Brown | London | UK |
Customer | Elizabeth Lincoln | Tsawassen | Canada |
Customer | Felipe Izquierdo | I. de Margarita | Venezuela |
Customer | Fran Wilson | Portland | USA |
Customer | Francisco Chang | México D.F. | Mexico |
Customer | Frédérique Citeaux | Strasbourg | France |
Customer | Georg Pipps | Salzburg | Austria |
Customer | Giovanni Rovelli | Bergamo | Italy |
Customer | Guillermo Fernández | México D.F. | Mexico |
Customer | Hanna Moos | Mannheim | Germany |
Customer | Hari Kumar | London | UK |
Customer | Helen Bennett | Cowes | UK |
Customer | Helvetius Nagy | Kirkland | USA |
Customer | Henriette Pfalzheim | Köln | Germany |
Customer | Horst Kloss | Cunewalde | Germany |
Customer | Howard Snyder | Eugene | USA |
Customer | Isabel de Castro | Lisboa | Portugal |
Customer | Jaime Yorres | San Francisco | USA |
Customer | Janete Limeira | Rio de Janeiro | Brazil |
Customer | Janine Labrune | Nantes | France |
Customer | Jean Fresnière | Montréal | Canada |
Customer | John Steel | Walla Walla | USA |
Customer | Jonas Bergulfsen | Stavern | Norway |
Customer | Jose Pavarotti | Boise | USA |
Customer | José Pedro Freyre | Sevilla | Spain |
Customer | Jytte Petersen | København | Denmark |
Customer | Karin Josephs | Münster | Germany |
Customer | Karl Jablonski | Seattle | USA |
Customer | Laurence Lebihans | Marseille | France |
Customer | Lino Rodriguez | Lisboa | Portugal |
Customer | Liu Wong | Butte | USA |
Customer | Liz Nixon | Portland | USA |
Customer | Lúcia Carvalho | São Paulo | Brazil |
Customer | Manuel Pereira | Caracas | Venezuela |
Customer | Maria Anders | Berlin | Germany |
Customer | Maria Larsson | Bräcke | Sweden |
Customer | Marie Bertrand | Paris | France |
Customer | Mario Pontes | Rio de Janeiro | Brazil |
Customer | Martine Rancé | Lille | France |
Customer | Martín Sommer | Madrid | Spain |
Customer | Mary Saveley | Lyon | France |
Customer | Matti Karttunen | Helsinki | Finland |
Customer | Maurizio Moroni | Reggio Emilia | Italy |
Customer | Michael Holz | Genève | Switzerland |
Customer | Miguel Angel Paolino | México D.F. | Mexico |
Customer | Palle Ibsen | Århus | Denmark |
Customer | Paolo Accorti | Torino | Italy |
Customer | Pascale Cartrain | Charleroi | Belgium |
Customer | Patricia McKenna | Cork | Ireland |
Customer | Patricio Simpson | Buenos Aires | Argentina |
Customer | Paul Henriot | Reims | France |
Customer | Paula Parente | Resende | Brazil |
Customer | Paula Wilson | Albuquerque | USA |
Customer | Pedro Afonso | São Paulo | Brazil |
Customer | Peter Franken | München | Germany |
Customer | Philip Cramer | Brandenburg | Germany |
Customer | Pirkko Koskitalo | Oulu | Finland |
Customer | Renate Messner | Frankfurt a.M. | Germany |
Customer | Rene Phillips | Anchorage | USA |
Customer | Rita Müller | Stuttgart | Germany |
Customer | Roland Mendel | Graz | Austria |
Customer | Sergio Gutiérrez | Buenos Aires | Argentina |
Customer | Simon Crowther | London | UK |
Customer | Sven Ottlieb | Aachen | Germany |
Customer | Thomas Hardy | London | UK |
Customer | Victoria Ashworth | London | UK |
Customer | Yang Wang | Bern | Switzerland |
Customer | Yoshi Latimer | Elgin | USA |
Customer | Yoshi Tannamuri | Vancouver | Canada |
Customer | Yvonne Moncada | Buenos Aires | Argentina |
Customer | Zbyszek | Walla | Poland |
Supplier | Anne Heikkonen | Lappeenranta | Finland |
Supplier | Antonio del Valle Saavedra | Oviedo | Spain |
Supplier | Beate Vileid | Sandvika | Norway |
Supplier | Carlos Diaz | São Paulo | Brazil |
Supplier | Chandra Leka | Singapore | Singapore |
Supplier | Chantal Goulet | Ste-Hyacinthe | Canada |
Supplier | Charlotte Cooper | Londona | UK |
Supplier | Cheryl Saylor | Bend | USA |
Supplier | Dirk Luchte | Zaandam | Netherlands |
Supplier | Eliane Noz | Annecy | France |
Supplier | Elio Rossi | Ravenna | Italy |
Supplier | Giovanni Giudici | Salerno | Italy |
Supplier | Guylène Nodier | Paris | France |
Supplier | Ian Devling | Melbourne | Australia |
Supplier | Jean-Guy Lauzon | Montréal | Canada |
Supplier | Lars Peterson | Göteborg | Sweden |
Supplier | Marie Delamare | Montceau | France |
Supplier | Martin Bein | Frankfurt | Germany |
Supplier | Mayumi Ohno | Osaka | Japan |
Supplier | Michael Björn | Stockholm | Sweden |
Supplier | Niels Petersen | Lyngby | Denmark |
Supplier | Peter Wilson | Manchester | UK |
Supplier | Petra Winkler | Berlin | Germany |
Supplier | Regina Murphy | Ann Arbor | USA |
Supplier | Robb Merchant | Boston | USA |
Supplier | Shelley Burke | New Orleans | USA |
Supplier | Sven Petersen | Cuxhaven | Germany |
Supplier | Wendy Mackenzie | Sydney | Australia |
Supplier | Yoshi Nagase | Tokyo | Japan |
참고
W3C School - SQL UNION Operator
W3C School - SQL Tutorial