Slide 11.19: Aliases
Slide 11.21: The SQL functions
Home

The UNION Command

The UNION command is used to select related information from two tables. When using the UNION command, all selected columns need to be of the same data type.

LastName FirstName Address City
Hansen Ola Timoteivn 10 Sandnes
Nordmann Anna Neset 18 Sandnes
Svendson Tove Borgvn 23 Sandnes
Pettersen Kari Storgt 20 Stavanger

Note that with UNION, only distinct values are selected. 
 SQL  Statement_1  UNION
   SQL Statement_2

List all different cities of specific persons: 

 SQL> select  City  from  Persons
   2    where  FirstName='Tove'  union
   3  select  City  from  Persons
   4    where  FirstName='Ola' or FirstName='Kari';
City
Sandnes
Stavanger

Note that this command cannot be used to list all cities of specific persons. In the example above we have two persons with equal cities, and only one of them is listed. The UNION command only selects distinct values.

The UNION ALL command selects all values. 
 SQL  Statement_1  UNION ALL
   SQL Statement_2

List all cities of specific persons: 

 SQL> select  City  from  Persons
   2    where  FirstName='Tove'  union all
   3  select  City  from  Persons
   4    where  FirstName='Ola' or FirstName='Kari';
City
Sandnes
Sandnes
Stavanger


The following is an SQL test area from w3schools.com where the Customers table may be created by the following command: 
   SQL> create table  Customers (
     2    CustomerID   varchar(16),
     3    CompanyName  varchar(32), 
     4    ContactName  varchar(32),
     5    Address      varchar(64),
     6    City         varchar(32), 
     7    PostalCode   varchar(16), 
     8    Country      varchar(32) );
Note that the Customers table is for read only.

      SQL Execution Results