SQL | UNION Operator

Pre-requisites: SQL operators

The UNION operator could be used to find the result set or combination of two or more tables. 

Terms and Conditions for using UNION 

• Each table used within UNION must have the same number of columns.
• The columns must have the same data types.
• The columns in each table must be in the same order.

Syntax: 

SELECT columnnames FROM table1

UNION

SELECT columnnames FROM table2;

UNION operator provides only unique values by default. To find duplicate values, use UNION ALL.

 Syntax:

SELECT columnnames FROM table1

UNION ALL

SELECT columnnames FROM table2;

Let’s assume we have two tables “Emp1” and “Emp2”;

Table1:

CREATE TABLE Emp1(
    EmpID INT PRIMARY KEY,
    Name VARCHAR(50),
    Country VARCHAR(50),
    Age int(2),
  mob int(10)
);
-- Insert some sample data into the Customers table
INSERT INTO Emp1 (EmpID, Name,Country, Age, mob)
VALUES (1, 'Shubham',  'India','23','738479734'),
       (2, 'Aman ',  'Australia','21','436789555'),
       (3, 'Naveen', 'Sri lanka','24','34873847'),
       (4, 'Aditya',  'Austria','21','328440934'),
       (5, 'Nishant', 'Spain','22','73248679');
       Select * from Emp1;

Output:

Table2

CREATE TABLE Emp2(
    EmpID INT PRIMARY KEY,
    Name VARCHAR(50),
    Country VARCHAR(50),
    Age int(2),
  mob int(10)
);
-- Insert some sample data into the Customers table
INSERT INTO Emp2 (EmpID, Name,Country, Age, mob)
VALUES (1, 'Tommy',  'England','23','738985734'),
       (2, 'Allen',  'France','21','43678055'),
       (3, 'Nancy', 'India','24','34873847'),
       (4, 'Adi',  'Ireland','21','320254934'),
       (5, 'Sandy', 'Spain','22','70248679');
       Select * from Emp2;

Output:

 SQL UNION Example

 The below SQL statement finds the cities (only unique values) from both the “Table1” and the “Table2” tables: 

Query: 

SELECT Country
FROM Emp1

UNION

SELECT Country
FROM Emp2
ORDER BY Country;

Output:

SQL UNION ALL Example 

The below SQL statement finds the cities (duplicate values also) from both the “Emp1” and the “Emp2” tables: 

Query: 

SELECT Country FROM Emp1 UNION SELECT Country FROM Emp2 ORDER BY Country;

Output: 

SQL UNION ALL With WHERE

The following SQL statement returns the cities (duplicate values also) from both the “Geeks1” and the “Geeks2” tables:

Query: 

SELECT Country, Name FROM Emp1
WHERE Name='Aditya'
UNION ALL
SELECT Country, Name FROM Emp2
WHERE Country='Ireland'
ORDER BY Country;

Output:

My Personal Notes arrow_drop_up

Save