Union All - (Oracle Operators)

In Oracle, the UNION ALL operator is used to combine the result sets of two or more SELECT statements into a single result set. The UNION ALL operator returns all rows from both SELECT statements, including duplicate rows.

Syntax

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

Here, table1 and table2 are the names of the tables, and column1, column2, ... are the column names in the tables for which we want to retrieve data.

Example

Consider the following two tables in an Oracle database:

Table1

id	name
1	John
2	Sarah
3	Andrew

Table2

id	name
1	John
4	Peter
5	Stella

We can use the UNION ALL operator to combine the rows of these tables to get a single result set with duplicate rows:

SELECT id, name FROM table1
UNION ALL
SELECT id, name FROM table2;

Output

The output for the above query will be:

id	name
1	John
2	Sarah
3	Andrew
1	John
4	Peter
5	Stella

Explanation

In the above example, we have used the UNION ALL operator to combine the rows of table1 and table2 to form a single result set. The SELECT statements retrieve the id and name columns from each table.

Since both tables have the same column names and data types, we are able to combine them using UNION ALL. The resulting output includes all rows from both tables, including duplicates.

Use

The UNION ALL operator is useful when we need to combine data from two or more tables into a single result set. This can be helpful when we need to analyze data from multiple sources or when we want to combine similar data that is stored in different tables.

Important Points

• The UNION ALL operator combines the result sets of two or more SELECT statements.
• The UNION ALL operator returns all rows from both SELECT statements, including duplicate rows.
• The columns in the SELECT statements must have the same data types.
• UNION ALL is faster than UNION, but it may return duplicate rows.

Summary

In summary, the UNION ALL operator is used in Oracle to combine the results of two or more SELECT statements into a single result set. It returns all rows from both SELECT statements, including duplicates. This operator can be helpful when we need to analyze or compare data from multiple tables.