여러분이 사용하고 계신 브라우저는 HTML5를 지원하지 않기 때문에 몇몇 요소가 제대로 보이도록 JScript를 사용하고 있습니다. 하지만 여러분의 브라우저 설정에서 스크립트 기능이 꺼져있으므로, 현재 페이지를 제대로 확인하시려면 스크립트 기능을 켜주셔야 합니다. SQL - UNION 연산자
SQL – UNION 연산자
2주전 작성
2주전 수정

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
참고
Mingg`s Diary
밍구
공부 목적 블로그