SQL UNION Clause ~ niit post

• UNION combines the result sets of two queries.
• Column data types in the two queries must match.
• UNION combines by column position rather than column name.

The SQL UNION syntax

The general syntax is: 

1.  SELECT column-names

2.    FROM table-name

3.   UNION

4.  SELECT column-names

5.    FROM table-name

SUPPLIER

Id

CompanyName

ContactName

City

Country

Phone

Fax

CUSTOMER

Id

FirstName

LastName

City

Country

Phone

SQL UNION Examples

Problem: List all contacts, i.e., suppliers and customers. 

1.  SELECT 'Customer' As Type, 

2.         FirstName + ' ' + LastName AS ContactName, 

3.         City, Country, Phone

4.    FROM Customer

5.  UNION

6.  SELECT 'Supplier', 

7.         ContactName, City, Country, Phone

8.    FROM Supplier

This is a simple example in which the table alias would be useful 

Results: 

Type	ContactName	City	Country	Phone
Customer	Alejandra Camino	Madrid	Spain	(91) 745 6200
Customer	Alexander Feuer	Leipzig	Germany	0342-023176
Customer	Ana Trujillo	México D.F.	Mexico	(5) 555-4729
Customer	Anabela Domingues	Sao Paulo	Brazil	(11) 555-2167
Supplier	Anne Heikkonen	Lappeenranta	Finland	(953) 10956
Supplier	Antonio del Valle Saavedra	Oviedo	Spain	(98) 598 76 54
Supplier	Beate Vileid	Sandvika	Norway	(0)2-953010
Supplier	Carlos Diaz	Sao Paulo	Brazil	(11) 555 4640
Supplier	Chandra Leka	Singapore	Singapore	555-8787
Supplier	Chantal Goulet	Ste-Hyacinthe	Canada	(514) 555-2955
Supplier	Charlotte Cooper	London	UK	(171) 555-2222
...