SQL UNION ALL (original) (raw)

Last Updated : 13 Apr, 2026

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.

Table1

Table2

**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.

Students Table

Trip_Details Table

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: