SQL Server Tutorial SQL Server Advanced SQL Server Database SQL Server References

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

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

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

EmpIDNameCityAgeSalary
1JohnLondon253000
2MarryLondon242750
3JoParis272800

Table 2: Vendor table

VendorIDNameCityAge
1JohnLondon29
2RameshNew Delhi28
3SureshNew Delhi31

  • 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:

    NameCity
    JohnLondon
    MarryLondon
    RameshNew Delhi
    JoParis
  • 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:

    NameCity
    JohnLondon
    MarryLondon
    JohnLondon
    RameshNew Delhi
    JoParis
  • 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:

    TypeNameCity
    EmployeeJohnLondon
    EmployeeMarryLondon
    VendorJohnLondon
    VendorRameshNew Delhi
    EmployeeJoParis