UNION ALL in SQL

UNION ALL in SQL

Introduction

The UNION ALL operator is used in SQL to combine the results of two or more SELECT statements into a single result set. It is used to retrieve data from multiple tables or queries and consolidate them into one result set. The UNION ALL operator differs from the UNION operator in that it includes all rows from all queries, including duplicate rows, while the UNION operator removes duplicate rows from the result set.

Syntax

The basic syntax of the UNION ALL operator is as follows:

sql
SELECT column1, column2, ...
FROM table1
UNION ALL
SELECT column1, column2, ...
FROM table2;

Explanation

• The SELECT statements within the UNION ALL operator should have the same number of columns.
• The column data types in the corresponding positions of each SELECT statement must be compatible.
• The column names in the result set are taken from the first SELECT statement.
• The UNION ALL operator combines the rows from both SELECT statements, including duplicate rows.
• The order of the columns in the result set is based on the order of columns in the first SELECT statement.
• The UNION ALL operator does not remove duplicate rows from the result set.

Example

Consider two tables, customers and employees, with the following structures:

Table: customers

diff
+----+----------+-----+
| ID |   Name   | Age |
+----+----------+-----+
| 1  | John     | 30  |
| 2  | Alice    | 25  |
| 3  | Michael  | 35  |
+----+----------+-----+

Table: employees

diff
+----+----------+-----+
| ID |   Name   | Age |
+----+----------+-----+
| 1  | Mark     | 40  |
| 4  | Jennifer | 28  |
+----+----------+-----+

To combine the records from both tables using UNION ALL, you can use the following SQL query:

sql
SELECT ID, Name, Age
FROM customers
UNION ALL
SELECT ID, Name, Age
FROM employees;

Result:

diff
+----+----------+-----+
| ID |   Name   | Age |
+----+----------+-----+
| 1  | John     | 30  |
| 2  | Alice    | 25  |
| 3  | Michael  | 35  |
| 1  | Mark     | 40  |
| 4  | Jennifer | 28  |
+----+----------+-----+

In the result set, you can see that all the records from both tables are combined, including duplicate rows.

Conclusion

The UNION ALL operator in SQL is a useful tool for combining the results of multiple SELECT statements into a single result set, including duplicate rows. It allows you to consolidate data from different tables or queries into a unified result set, providing flexibility in data retrieval and analysis.