UNION SQL Server: Syntax, Usage, and Example

Introduction

The result sets of two or more SELECT statements are combined into a single result set using the SQL UNION operator.

UNION Operator in SQL

The SQL UNION operator is used to return a single result set by combining the result sets of several SELECT statements.

Use of the SQL UNION operator is subject to certain regulations.

Guidelines for SQL Union

Every table utilized by UNION needs to have an equal quantity of columns.
The data types of the columns must match.
All table columns must be in the same sequence.

Syntax

SELECT column1, column2, [column_n] FROM table1
UNION
SELECT column1, column2, [column_n] FROM table2;

By default, the UNION operator returns unique values.

As an illustration

To better understand the UNION operator, let's take a look at an illustration in SQL.

Let's make "Employee1" and "Employee2" tables.

Employee1 Table

CREATE TABLE Employee1
(
    Id INT PRIMARY KEY,
    [Name] VARCHAR(50),
    Country VARCHAR(50),
    Age TINYINT,
    Mno VARCHAR(10)
);
INSERT INTO Employee1 (Id, [Name], Country, Age, Mno)
VALUES (1, 'Jaimin',  'India','33','7990233523'),
       (2, 'Roshni',  'India','23','436789555'),
       (3, 'Dwisha', 'Canada','21','34873847'),
       (4, 'Dwiti',  'Austria','21','328440934'),
       (5, 'Renith', 'Spain','20','73248679');
SELECT Id, [Name], Country, Age, Mno FROM Employee1;

Employee1 Table

Employee 2 Table

CREATE TABLE Employee2
(
    Id INT PRIMARY KEY,
    [Name] VARCHAR(50),
    Country VARCHAR(50),
    Age TINYINT,
    Mno VARCHAR(10)
);
INSERT INTO Employee2 (Id, [Name], Country, Age, Mno)
VALUES (1, 'James',  'France','33','7990233523'),
       (2, 'Smith',  'Australia','23','436789555'),
       (3, 'Dwisha', 'Canada','21','34873847'),
       (4, 'Dwiti',  'Austria','21','328440934'),
       (5, 'Renith', 'Spain','20','73248679'),
       (6, 'Jaimin',  'India','33','7990233523'),
       (7, 'Roshni',  'India','23','436789555');
SELECT Id, [Name], Country, Age, Mno FROM Employee2;

Employee 2 Table

SQL UNION Operator

SELECT [Name], Country, Age, Mno FROM Employee1
UNION
SELECT [Name], Country, Age, Mno FROM Employee2;

SQL UNION Operator

SQL UNION with WHERE

SELECT [Name], Country, Age, Mno FROM Employee1 WHERE Age > 21
UNION
SELECT [Name], Country, Age, Mno FROM Employee2 WHERE Age > 21;

SQL UNION

Crucial information regarding SQL UNION Operator.

The result sets of two or more SELECT queries are combined using the SQL UNION operator.
After removing duplicate entries from the result set, UNION returns unique rows.
The result set's columns must have the same data types and be arranged in the same order.
When combining data from several tables or applying various filters to data from the same table, UNION is a helpful tool.

We learned the new technique and evolved together.

Happy coding!