Merge Oracle Query Results: UNION vs UNION ALL

1 year ago
Ava Mitchell
1 minute

In Oracle, you can merge query results into one result set using the UNION or UNION ALL keywords.

The UNION keyword is used to combine the result sets of two or more SELECT statements and eliminates duplicate rows. The syntax is as follows:

SELECT 列名1, 列名2, ... FROM 表名1
UNION
SELECT 列名1, 列名2, ... FROM 表名2

The UNION ALL keyword is used to combine the result sets of two or more SELECT statements without removing duplicate rows. The syntax is as follows:

SELECT 列名1, 列名2, ... FROM 表名1
UNION ALL
SELECT 列名1, 列名2, ... FROM 表名2

It is important to note that when combining the result sets with UNION or UNION ALL, the number of columns and data types must be the same. If they are different, type conversion functions can be used to handle it.

Here is an example:

SELECT name, age FROM table1
UNION
SELECT name, age FROM table2

This will return a result set that merges and removes duplicate name and age fields.

SELECT name, age FROM table1
UNION ALL
SELECT name, age FROM table2

This will return a result set that combines name and age fields without removing any duplicates.

bannerAds