How to use union all in oracle

UNION ALL is used in Oracle to merge result sets from different tables or subqueries while retaining duplicate rows. The specific usage is as follows: merge rows in different tables: SELECT FROM table 1 UNION ALL SELECT FROM table 2 merge duplicate rows in the same table: SELECT FROM table UNION ALL SELECT FROM table

Usage of UNION ALL syntax in Oracle

UNION ALL is an operator in Oracle used to merge multiple SELECT statement result sets. It allows you to combine results from multiple tables or subqueries into a single result set without eliminating duplicate rows.

Syntax:

SELECT ...
UNION ALL
SELECT ...
[UNION ALL
SELECT ...]

Usage:

The UNION ALL operator has two main uses:

• Merge rows from different tables: You can use UNION ALL to combine rows from different tables into a single result set. For example:

SELECT * FROM employees
UNION ALL
SELECT * FROM customers;

This will return a result set containing all rows from the employees table and customers table.

• Merge duplicate rows from the same table: You can also use UNION ALL to merge duplicate rows from the same table. For example:

SELECT * FROM employees
UNION ALL
SELECT * FROM employees;

This will return a result set containing all rows in the employees table, including duplicate rows.

Note:

• UNION ALL retains duplicate rows from result sets from different queries.
• UNION ALL operator does not sort the result set.
• UNION ALL does not support the DISTINCT keyword.
• UNION ALL cannot be used in different tables or subqueries with different numbers of columns.

The above is the detailed content of How to use union all in oracle. For more information, please follow other related articles on the PHP Chinese website!