Union - ( Oracle Operators )

The union operator in Oracle is used to combine the result sets of two or more SELECT statements into a single result set. The result set contains all the rows that are returned by either of the SELECT statements.

Syntax

The syntax for using the union operator in Oracle is as follows:

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

Here, column1, column2, ... are the columns that we want to select from the tables, and table1 and table2 are the tables from which we want to select the columns.

Example

Consider the following two tables:

Table 1: Employee

EmpID	EmpName	EmpSalary
1	John Smith	45000
2	Jane Doe	55000

Table 2: Customer

CustomerID	CustomerName	City
1001	James Anderson	New York
1002	Emily Jones	London

We can use the UNION operator to combine the result sets of two SELECT statements as follows:

SELECT EmpName AS Name, 'Employee' AS Type FROM Employee
UNION
SELECT CustomerName AS Name, 'Customer' AS Type FROM Customer;

Here, we are selecting the EmpName column from the Employee table and renaming it as Name. We are also adding a column called Type which will contain the string 'Employee' for rows from the Employee table and 'Customer' for rows from the Customer table. The result set will contain all the rows from both SELECT statements.

Output

Name           Type
-------------- --------
John Smith     Employee
Jane Doe       Employee
James Anderson Customer
Emily Jones    Customer

Explanation

In the above example, we are using the UNION operator to combine the result sets of two SELECT statements. The first SELECT statement selects the EmpName column from the Employee table and renames it as Name. The Type column is added with a string value 'Employee'. The second SELECT statement selects the CustomerName column from the Customer table and renames it as Name. The Type column is added with a string value 'Customer'. The result set contains all the rows from the two SELECT statements.

Use

The union operator in Oracle is useful when we need to combine the result sets of multiple SELECT statements into a single result set. This allows us to present the data in a different way or to perform additional operations on the combined data.

Important Points

• The union operator in Oracle is used to combine the result sets of two or more SELECT statements into a single result set.
• The SELECT statements used with the union operator must have the same number of columns in the same order.
• The union operator removes duplicates from the result set unless the UNION ALL operator is used.

Summary

The union operator in Oracle is used to combine the result sets of two or more SELECT statements into a single result set. It is useful when we need to present data in a different way or perform additional operations on the combined data. The SELECT statements used with the union operator must have the same number of columns in the same order. The union operator removes duplicates from the result set, unless the UNION ALL operator is used.