UNION ALL SQL Server: Syntax, Usage, and Example

Introduction

SQL Cooperation In SQL, the ALL command aggregates the output of two or more SELECT statements.

Both SELECT statements must contain the same number of columns or fields in order to perform the UNION ALL operation; otherwise, an error will occur in the resultant expression.

Syntax

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

Operators UNION and UNION ALL operate in the same way. The UNION operator removes duplicate rows from the result set as the only difference. Duplicate rows are not eliminated from the query result set by UNION ALL.

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 ALL Operator

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

SQL UNION ALL with WHERE

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

Keep in mind that the SELECT clauses of both queries must have an equal number of phrases. There will be a problem with the following query.

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

Crucial Information Regarding SQL UNION All

• Combining the results of two or more SELECT operations from separate tables is made easier with the UNION ALL command.
• While the UNION command does not include duplicate records if both are used exactly, the UNION ALL command includes duplicate records from the SELECT queries.
• Both SELECT queries must have the same number of columns in order to perform the UNION ALL operation; otherwise, an error will occur in the resultant expression.

We learned the new technique and evolved together.

Happy coding!