SQL UNION OPERATOR

The UNION operator used to display the result from two or more set of tables from database by using sql SELECT statement. It combines the result of different tables and display without any duplicate records from those tables.

There are two ways to use this operator

  • UNION
  • UNION ALL

    The only difference in both operators is that UNION ALL combine the results and return duplicate record in result set.
  • Rules:
  • The number of columns must be same in both SELECT statement
  • The data type of columns must be same in both SELECT statement
  • The order of columns must be same in both SELECT statement
    Let’s understand each with syntax and examples.


My SQL

Syntax:

SELECT COLUMN1, COLUMN2, COLUMN3, ...
UNION
SELECT COLUMN1, COLUMN2, COLUMN3, ...

SELECT COLUMN1, COLUMN2, COLUMN3, ...
UNION ALL
SELECT COLUMN1, COLUMN2, COLUMN3, ...

Example:

Original Data in tables called “Employee” and “Trainee”


Id Employee Code First Name Last name City Country
1 EMP_1001 John Peter New York USA
2 EMP_1002 Josh Wills San Francisco USA
3 EMP_1003 Jeremy Parkar London UK

Id Trainee Code First Name Last name City Country
1 TRA_1001 Patrick Peter Ahmedabad India
2 TRA_1002 Philemon Wills Bombay India
3 TRA_1003 Martyn Parkar Ahmedabad India


SELECT Country FROM Employee
UNION
SELECT Country FROM Trainee
ORDER BY Country;

Data displayed from table “Employee” after above Sql Query executed


City Country
Ahmedabad India
Bombay India
London UK
New York USA
San Francisco USA


SELECT City,Country FROM Employee
UNION ALL
SELECT City,Country FROM Trainee
ORDER BY Country;

Data displayed from table “Employee” after above Sql Query executed


City Country
Ahmedabad India
Bombay India
Ahmedabad India
London UK
New York USA
San Francisco USA