Set Operators in Oracle SQL

Set operators in Oracle SQL are used to combine the results of two or more queries into a single result set. These operators are essential for performing complex queries that involve multiple datasets. Understanding these operators is crucial for effectively querying and analyzing data in Oracle databases.

Key Concepts

1. UNION

The UNION operator combines the results of two or more SELECT statements into a single result set. It removes duplicate rows from the combined result set.

Example:

Combining the results of two queries to get a list of unique employees and contractors:


        SELECT EmployeeID, FirstName, LastName FROM Employees
        UNION
        SELECT ContractorID, FirstName, LastName FROM Contractors;

2. UNION ALL

The UNION ALL operator is similar to UNION, but it does not remove duplicate rows. It simply combines all rows from the queries.

Example:

Combining the results of two queries to get a list of all employees and contractors, including duplicates:


        SELECT EmployeeID, FirstName, LastName FROM Employees
        UNION ALL
        SELECT ContractorID, FirstName, LastName FROM Contractors;

3. INTERSECT

The INTERSECT operator returns only the rows that are common to both result sets of the SELECT statements.

Example:

Finding employees who are also listed as contractors:


        SELECT EmployeeID FROM Employees
        INTERSECT
        SELECT ContractorID FROM Contractors;

4. MINUS

The MINUS operator returns the rows from the first SELECT statement that are not present in the second SELECT statement.

Example:

Finding employees who are not listed as contractors:


        SELECT EmployeeID FROM Employees
        MINUS
        SELECT ContractorID FROM Contractors;

5. ORDER BY Clause

The ORDER BY clause can be used with set operators to sort the combined result set. It must be placed at the end of the combined query.

Example:

Combining and sorting the results of two queries:


        SELECT EmployeeID, FirstName, LastName FROM Employees
        UNION
        SELECT ContractorID, FirstName, LastName FROM Contractors
        ORDER BY LastName;

6. Column Compatibility

When using set operators, the number and data types of the columns in all SELECT statements must be compatible. This ensures that the combined result set is meaningful.

Example:

Combining queries with compatible columns:


        SELECT EmployeeID, FirstName, LastName FROM Employees
        UNION
        SELECT ContractorID, FirstName, LastName FROM Contractors;

7. Aliases

Column aliases defined in the first SELECT statement are used in the combined result set. Aliases defined in subsequent SELECT statements are ignored.

Example:

Using aliases in the combined result set:


        SELECT EmployeeID AS ID, FirstName, LastName FROM Employees
        UNION
        SELECT ContractorID, FirstName, LastName FROM Contractors;

8. Nested Set Operators

Set operators can be nested within each other to perform complex queries. This allows for multiple levels of combining and filtering data.

Example:

Nesting set operators to find common employees and contractors:


        SELECT EmployeeID FROM Employees
        INTERSECT
        (SELECT ContractorID FROM Contractors
        UNION
        SELECT EmployeeID FROM Employees);

9. Performance Considerations

Using set operators can impact query performance, especially with large datasets. It is important to optimize queries and consider indexing strategies to ensure efficient execution.

Example:

Optimizing a query using set operators:


        SELECT EmployeeID FROM Employees
        WHERE EmployeeID IN (SELECT ContractorID FROM Contractors)
        UNION
        SELECT ContractorID FROM Contractors
        WHERE ContractorID IN (SELECT EmployeeID FROM Employees);

By mastering these set operators, you can effectively combine and analyze data from multiple queries in Oracle SQL, making your queries more powerful and flexible.