SQL Functions
TOP
❮ ❯

SQL UNION Statement

SQL UNION Description

The UNION operator is used to combine the result set of two or more SELECT statements.

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

    • UNION Syntax

    SELECT column_name(s) FROM table1
UNION
SELECT column_name(s) FROM table2

    UNION ALL Syntax

    The UNION operator selects only distinct values ​​by default. To allow duplicate values, use UNION ALL:

    SELECT column_name(s) FROM table1
UNION ALL
SELECT column_name(s) FROM table2
    The column names in the result set usually match the column names in the first SELECT statement.

    Demonstration database

    In this tutorial we will use the famous example database "Northwind".

    Below is a sample from the table "Customers" ("Customers"):

    CustomerIDCustomerNameContactNameAddressCityPostalCodeCountry
    1Alfreds FutterkisteMaria AndersObere Str. 57Berlin12209Germany
    2Ana Trujillo Emparedados y heladosAna TrujilloAvda. de la Constitución 2222México D.F.5021Mexico
    3Antonio Moreno TaqueríaAntonio MorenoMataderos 2312México D.F.5023Mexico
    4Around the HornThomas Hardy120 Hanover Sq.LondonWA1 1DPUK
    5Berglunds snabbköpChristina BerglundBerguvsvägen 8LuleåS-958 22Sweden

    And the sample from the table "Suppliers" ("Suppliers"):

    SupplierIDSupplierNameContactNameAddressCityPostalCodeCountryPhone
    1Exotic LiquidCharlotte Cooper49 Gilbert St.LondonaEC1 4SDUK(171) 555-2222
    2New Orleans Cajun DelightsShelley BurkeP.O. Box 78934New Orleans70117USA(100) 555-4822
    3Grandma Kelly's HomesteadRegina Murphy707 Oxford Rd.Ann Arbor48104USA(313) 555-5735
    4Tokyo TradersYoshi Nagase9-8 Sekimai Musashino-shiTokyo100Japan(03) 3555-5011
    5Cooperativa de Quesos 'Las Cabras'Antonio del Valle SaavedraCalle del Rosal 4Oviedo33007Spain(98) 598 76 54

    SQL UNION Example

    The following SQL statement selects cities (different values ​​only) from the Customers and Suppliers tables:

    Run SQLSELECT City FROM Customers 
UNION 
SELECT City FROM Suppliers 
ORDER BY City
    If some customers or suppliers have the same city (City), each city will be specified only once because UNION only selects different values. Use UNION ALL to select duplicate values ​​too!

    SQL UNION ALL Example

    The following SQL statement selects cities (also duplicate values) from both the Customers and Suppliers tables:

    Run SQLSELECT City FROM Customers 
UNION ALL 
SELECT City FROM Suppliers 
ORDER BY City

    SQL UNION together with WHERE

    The following SQL statement selects German cities (different values ​​only) from both the Customers and Suppliers tables:

    Run SQLSELECT City, Country FROM Customers 
WHERE Country = 'Germany' 
UNION 
SELECT City, Country FROM Suppliers 
WHERE Country = 'Germany' 
ORDER BY City

    SQL UNION ALL together with WHERE

    The following SQL statement selects German cities (also duplicate values) from both the Customers and Suppliers tables:

    Run SQLSELECT City, Country FROM Customers 
WHERE Country = 'Germany' 
UNION ALL 
SELECT City, Country FROM Suppliers 
WHERE Country = 'Germany' 
ORDER BY City

    Another SQL UNION Example

    The following SQL statement lists all customers and suppliers:

    Run SQLSELECT 'Customer' AS Type, ContactName, City, Country 
FROM Customers 
UNION 
SELECT 'Supplier', ContactName, City, Country 
FROM Suppliers

    Note the "AS Type" above is an alias. SQL aliases are used to give a table or column a temporary name. The alias exists only for the duration of the request. So, here we have created a temporary column called "Type" that indicates whether the contact is a "Customer" or a "Vendor".

    ❮ Previous Next ❯