The SQL UNION ALL operator combines the result sets of two or more SELECT statements into a single output. Unlike UNION, it does not remove duplicate rows, making it faster when duplicates are acceptable.
- Includes all records, even duplicate rows, from the combined queries
- Requires the same number of columns with compatible data types in each SELECT
- More performant than UNION since no duplicate elimination is performed
Example: First, we create a demo SQL database and tables, on which we will use the UNION ALL command.
Query:
SELECT id, city FROM Table1
UNION ALL
SELECT id, city FROM Table2;
Output:
Syntax:
SELECT columns FROM table1
UNION ALL
SELECT columns FROM table2;
Examples of SQL UNION ALL
Let's look at some examples of the UNION ALL command in SQL to understand its working. First, let's create a demo SQL database and tables on which UNION ALL will be performed.
Example 1: Single Field With Same Name
We want to combine the names from both the STUDENTS and TRIP_DETAIL tables, including all names, even if there are duplicates.
SELECT NAME FROM Students
UNION ALL
SELECT NAME FROM Trip_Details;
Output:
Example 2: Different Field Names
Suppose we want to combine the ROLL_NO from both tables. We can use the UNION operator to merge the results. Although column names do not need to match, aliases can be used to make the output column name consistent.
Query:
SELECT ROLL_NO AS Identifier FROM Students
UNION ALL
SELECT ROLL_NO AS Identifier FROM Trip_Details;
Output:
SQL UNION ALL With WHERE
You can use the WHERE clause with UNION ALL in SQL. The WHERE clause is used to filter records and is added after each SELECT statement
Example: SQL UNION ALL with WHERE
The following SQL statement returns the countries (duplicate values also) from both the "Students" and the "Trip_Details" tables:
Query:
SELECT NAME, AGE
FROM Students
WHERE AGE > 16
UNION ALL
SELECT NAME, AGE
FROM Trip_Details
WHERE AGE >= 18;
Output:
SQL UNION All vs UNION
Here is the comparison between UNION ALL and UNION Operator:
| UNION ALL | UNION |
|---|---|
| Includes all duplicates records | Removes duplicate records |
| Faster, as it doesn't check for duplicates | Slower, as it needs to eliminate duplicates |
| Use when duplicates are acceptable or needed | Use when duplicates need to be removed |
Syntax: SELECT columns FROM table1 UNION ALL SELECT columns FROM table2; | Syntax: SELECT columns FROM table1 UNION SELECT columns FROM table2; |
| Generally lower memory usage, since no extra processing for duplicates | Higher memory usage, due to additional steps for duplicate removal |
Returns combined rows from all SELECT statements, including duplicates | Returns combined rows from all SELECT statements, without duplicates |
| Useful for large datasets where performance is critical and duplicates are acceptable | Useful when data integrity requires unique records in the result set |
