UNION ALL operator

In SQL, Operators are special character, symbol or a keyword that is used to perform some specific operations. For example : comparing data

SQL operators are commonly used with SQL statements to retrieve, filter, and manipulate data.

UNION ALL operator :

UNION ALL operator is used to combine/merge the result of multiple SELECT statements into a single result by keeping duplicate rows.

While using UNION ALL operator, ensure that column_name(s) and their data types should be in the same order across all SELECT statements.

The syntax of the UNION ALL operator generally looks like this :

SELECT column_name(s)
FROM table1
UNION ALL
SELECT column_name(s)
FROM table2;
        

• Specify the column(s) name after the 'SELECT' keyword.

• Specify Asterisk (*) symbol to selects all columns from the table after the 'SELECT' keyword.

• Specify the table name after the 'FROM' keyword.

• 'table1' and 'table2' refer to the two tables being merged together.

• 'UNION ALL' keyword combines the result set of multiple SELECT statements into a single result by keeping duplicate rows.

Here's an example of how you might use the UNION ALL operator :

Let's suppose we have two tables :

[a] Employees Table :

→ It contains columns [Employee_Id], [Employee_Name], [Gender] and [Salary].

[b] Employees_2 Table :

→ It contains all columns same as '[Employees] table' with same data types and order. Except '[Age]' column.

1. Please ensure that each column_name(s) and their data types are in the same order across all SELECT statements.

For example, let's see what happens if we run the below statement :

SELECT *
FROM [Employees]
UNION ALL
SELECT *
FROM [Employees_2];
        

An error is thrown indicating that while performing 'UNION ALL' operation, number of columns must be the same in all SELECT statements.

The error occurred because we specified 'Asterisk' (*) symbol that selects all columns from the table where [Employees_2] table contains '[Age]' column which is absent in [Employees] table. Therefore, we need to specify column_name(s) explicitly in all SELECT statements.

2. Please run the following SQL statement :

SELECT Employee_Id, Employee_Name, Gender, Salary
FROM [Employees]
UNION ALL
SELECT Employee_Id, Employee_Name, Gender, Salary
FROM [Employees_2];
        

3. Above statement merges the result sets of both employee tables into a single result set for all selected columns. It doesn't eliminate duplicate rows. Hence all 7 rows are returned.

That's it! You have successfully merged data using 'UNION ALL' operator.

To enhance your understanding of 'SQL operators', be sure to check out our next tutorial.

Frequently Asked Questions :

The UNION ALL operator in SQL combines the results of two or more SELECT statements, including duplicates.

Whether it's good to use UNION ALL in SQL depends on your specific use case and whether you need to retain duplicate rows or not.

No, UNION ALL does not remove duplicates; it includes all rows from both queries, even if they are duplicates.

To merge two queries in SQL, use the UNION ALL operator if you want to include duplicates or UNION if you want to remove duplicates.