SQL UNION Keyword

The SQL UNION keyword is used to combine the result-set of two or more SQL SELECT statements. While using UNION keyword, the following criteria must be satisfied:

• Number of columns in each SELECT statement should equal.
• Datatype of columns in each SELECT statement should match.
• Order of columns in each SELECT statement should match.

Syntax

The syntax for using UNION keyword is given below:

SELECT column1, column2, ... FROM table1
UNION
SELECT column1, column2, ... FROM table2

Note: UNION keyword only takes distinct values. To combine duplicate values, UNION ALL keyword can be used.

Example:

Consider database tables called Employee and Vendor with the following records:

Table 1: Employee table

Table 2: Vendor table

• To fetch distinct cities from tables Employee and Vendor, the SQL code is given below:

  SELECT City FROM Employee
  UNION
  SELECT City FROM Vendors
  ORDER BY City;
  

  
  

  This will produce the result as shown below:

  City
  London
  New Delhi
  Paris

• To fetch distinct cities from tables Employee and Vendor where age of the person is less than 30, the SQL code is given below:

  SELECT City FROM Employee
  WHERE Age < 30
  UNION
  SELECT City FROM Vendors
  WHERE Age < 30
  ORDER BY City;
  

  
  

  This will produce the following result:

  City
  London
  New Delhi
  Paris