How to use union in oracle

The UNION operator in Oracle combines the result sets of multiple queries to create a new result set, retaining unique rows or all rows. It must ensure that the columns are of the same number and type and can be used in combination with ALL, INTERSECT, MINUS operators to further filter the results.

Usage of UNION operator in Oracle

UNION operator is used in Oracle to combine values ​​from two or the result sets of multiple queries, thereby creating a new result set. It keeps all unique rows from both queries, ignoring duplicates.

Syntax

<code>SELECT column_list
FROM table1
UNION
SELECT column_list
FROM table2;</code>

Usage instructions

• Must ensure that the two queries return the same number and data type List.
• The UNION operator retains all unique rows in two queries.
• If needed, you can use the ALL keyword before the UNION operator, which will retain all rows, including duplicates.
• UNION can be used in combination with other operators, such as INTERSECT and MINUS, to further filter the result set.

Example

The following example shows the usage of the UNION operator:

<code>SELECT name
FROM employees
UNION
SELECT name
FROM customers;</code>

This query will return all employees and a list of unique names of customers.

Other options

• UNION ALL: Similar to UNION, but keep all rows, including duplicates .
• INTERSECT: Return only rows that exist in both queries.
• MINUS: Returns rows that exist only in the first query and not in the second query.

Conclusion

The UNION operator is used in Oracle to combine result sets from multiple queries to create a new result set. It retains unique rows or all rows, depending on the options used. Understanding the usage of UNION is essential to efficiently obtain data from the database.

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