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;

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;

SQL UNION Operator

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

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;

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!