SQL Server - UNION Clause

The SQL Server (Transact-SQL) UNION clause is used to combine the result-set of two or more SELECT statements. While using UNION clause, 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 clause in SQL Server (Transact-SQL) is given below:

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

The syntax for using UNION ALL clause in SQL Server (Transact-SQL) is given below:

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

Example:

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

Table 1: Employee table

Table 2: Vendor table

• Using UNION clause: To fetch distinct cities from tables Employee and Vendor, the query is given below:

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

  
  

  This will produce the result as shown below:

  City
  London
  New Delhi
  Paris

• Using UNION ALL clause: To fetch all cities from tables Employee and Vendor, the query is given below:

  SELECT City FROM Employee
  UNION ALL
  SELECT City FROM Vendor
  ORDER BY City;
  

  
  

  This result of the above code will be:

  City
  London
  London
  London
  New Delhi
  New Delhi
  Paris

• Using UNION clause with WHERE clause: To fetch distinct names and cities from tables Employee and Vendor where age of the person is less than 30, the following query can be used:

  SELECT Name, City FROM Employee
  WHERE Age < 30
  UNION
  SELECT Name, City FROM Vendor
  WHERE Age < 30
  ORDER BY City;
  

  
  

  This will produce the following result:

  Name	City
  John	London
  Marry	London
  Ramesh	New Delhi
  Jo	Paris

• Using UNION ALL clause with WHERE clause: To fetch all names and cities from tables Employee and Vendor where age of the person is less than 30, the following query can be used:

  SELECT Name, City FROM Employee
  WHERE Age < 30
  UNION ALL
  SELECT Name, City FROM Vendor
  WHERE Age < 30
  ORDER BY City;
  

  
  

  This will produce the result as shown below:

  Name	City
  John	London
  Marry	London
  John	London
  Ramesh	New Delhi
  Jo	Paris

• Using Alias: Using alias is a good way to identify records of different tables. See the example below:

  SELECT 'Employee' AS Type, Name, City FROM Employee
  WHERE Age < 30
  UNION ALL
  SELECT 'Vendor', Name, City FROM Vendor
  WHERE Age < 30
  ORDER BY City;
  

  
  

  This will produce the result as shown below:

  Type	Name	City
  Employee	John	London
  Employee	Marry	London
  Vendor	John	London
  Vendor	Ramesh	New Delhi
  Employee	Jo	Paris