Understanding UNION in Oracle SQL

Key Concepts

The UNION operator in Oracle SQL is used to combine the result sets of two or more SELECT statements into a single result set. Understanding the following key concepts is essential for effectively using UNION:

1. UNION Clause

The UNION clause is used to combine the results of two or more SELECT statements. It removes duplicate rows from the combined result set.

2. UNION ALL Clause

The UNION ALL clause is similar to UNION, but it does not remove duplicate rows. It includes all rows from all SELECT statements, including duplicates.

3. Column Compatibility

For UNION to work, the SELECT statements must have the same number of columns, and corresponding columns must have compatible data types.

4. ORDER BY Clause

The ORDER BY clause can be used to sort the combined result set. It should be placed at the end of the last SELECT statement.

5. Performance Considerations

Using UNION ALL is generally faster than UNION because it avoids the overhead of removing duplicates.

Detailed Explanation

1. UNION Clause

The UNION clause combines the result sets of two or more SELECT statements and removes duplicate rows. For example, to combine the results of two queries from different tables:


        SELECT EmployeeID, FirstName, LastName FROM Employees
        UNION
        SELECT CustomerID, FirstName, LastName FROM Customers;

2. UNION ALL Clause

The UNION ALL clause combines the result sets of two or more SELECT statements without removing duplicate rows. For example:


        SELECT OrderID, OrderDate FROM Orders
        UNION ALL
        SELECT OrderID, OrderDate FROM ArchivedOrders;

3. Column Compatibility

For UNION to work, the SELECT statements must have the same number of columns, and corresponding columns must have compatible data types. For example:


        SELECT EmployeeID, FirstName, Salary FROM Employees
        UNION
        SELECT CustomerID, FirstName, 0 FROM Customers;

4. ORDER BY Clause

The ORDER BY clause can be used to sort the combined result set. It should be placed at the end of the last SELECT statement. For example:


        SELECT EmployeeID, FirstName, Salary FROM Employees
        UNION
        SELECT CustomerID, FirstName, 0 FROM Customers
        ORDER BY Salary DESC;

5. Performance Considerations

Using UNION ALL is generally faster than UNION because it avoids the overhead of removing duplicates. For example:


        SELECT OrderID, OrderDate FROM Orders
        UNION ALL
        SELECT OrderID, OrderDate FROM ArchivedOrders;

Examples and Analogies

Example 1: Combining Employee and Customer Data

Imagine you have two tables, one for employees and one for customers. You want to combine the names and IDs of both groups into a single list. You would use UNION:


        SELECT EmployeeID, FirstName, LastName FROM Employees
        UNION
        SELECT CustomerID, FirstName, LastName FROM Customers;

Example 2: Combining Orders and Archived Orders

If you have a table for current orders and another for archived orders, and you want to see all orders without removing duplicates, you would use UNION ALL:


        SELECT OrderID, OrderDate FROM Orders
        UNION ALL
        SELECT OrderID, OrderDate FROM ArchivedOrders;

Example 3: Sorting Combined Results

To sort the combined results of employees and customers by salary, you would use ORDER BY at the end of the UNION statement:


        SELECT EmployeeID, FirstName, Salary FROM Employees
        UNION
        SELECT CustomerID, FirstName, 0 FROM Customers
        ORDER BY Salary DESC;