PL/SQL UNION ALL Operator

In PL/SQL, the UNION ALL operator is a powerful tool that allows us to combine the results of two or more SELECT queries into a single result set. Unlike the UNION operator, which eliminates duplicate rows, UNION ALL includes all rows, including duplicates. This makes it faster and more efficient when we need to retain all records from the combined queries.

What is the PL/SQL UNION ALL Operator?

The UNION ALL operator in PL/SQL combines the result sets of two or more SELECT statements. Each SELECT statement within the UNION ALL must have the same number of columns in the result sets and the columns must have similar data types.

Syntax:

The basic syntax for using UNION ALL is as follows:

SELECT column1, column2, ...

FROM table1

UNION ALL

SELECT column1, column2, ...

FROM table2;

Explanation :

• The UNION ALL operator retains all records from the combined queries, including duplicates.
• All SELECT statements combined with UNION ALL must have the same number of columns.
• The data types of the columns must be compatible across all SELECT statements.

Examples of PL/SQL UNION ALL Operator

To understand the PL/SQL UNION ALL Operator we will use below two table called employees and contractors which help us to perform various examples with output and explanations.

Contractors Table:

employees Table:

Example 1: Combining All Employees and Contractors

In this query, we are using the UNION ALL operator to combine the names and departments of employees and contractors. The SELECT statement retrieves employee_name from the employees table and contractor_name from the contractors table.

SELECT employee_name AS name, department
FROM employees
UNION ALL
SELECT contractor_name AS name, department
FROM contractors;

Output:

Explanation: The query combines the results from two different tables, employees and contractors, into a single result set. The UNION ALL operator is used to concatenate the results without removing duplicates. The employee_name and contractor_name columns are both renamed as name, and the corresponding department columns are included.

Example 2: Including Duplicates in the Result Set

This example uses the UNION ALL operator to combine the department column from both the employees and contractors tables. Since the UNION ALL operator does not remove duplicates, the output will include repeated department names if they appear in both tables.

SELECT department
FROM employees
UNION ALL
SELECT department
FROM contractors;

Output:

Explanation: This query retrieves and combines the department values from both the employees and contractors tables into a single result set. By using the UNION ALL operator, it includes all department records from both tables, retaining duplicates if any departments are listed in both tables.

Example 3: Adding Extra Columns

In this query, we add an extra column named role to differentiate between employees and contractors. The role column is hard-coded as 'Employee' for records from the employees table and 'Contractor' for records from the contractors table.

SELECT employee_id AS id, employee_name AS name, 'Employee' AS role
FROM employees
UNION ALL
SELECT contractor_id AS id, contractor_name AS name, 'Contractor' AS role
FROM contractors;

Output:

Explanation: This query combines the employee_id and contractor_id columns from the employees and contractors tables into a single result set, with both columns renamed as id. Similarly, the employee_name and contractor_name columns are renamed as name. Additionally, a new column role is introduced, with the value 'Employee' for records from the employees table and 'Contractor' for records from the contractors table.

Example 4: Filtering Results with WHERE Clause

Explanation : This query uses the WHERE clause to filter results so that only those belonging to the "Finance" department are included in the output. The UNION ALL operator then combines these filtered results from the employees and contractors tables into a single result set.

SELECT employee_name AS name, department
FROM employees
WHERE department = 'Finance'
UNION ALL
SELECT contractor_name AS name, department
FROM contractors
WHERE department = 'Finance';

Output:

Explanation: This query retrieves the names and departments of individuals specifically from the Finance department, combining data from both the employees and contractors tables. The UNION ALL operator merges the results from the two tables without eliminating duplicates.

Conclusion

The UNION ALL operator in PL/SQL is an efficient way to combine multiple result sets, including all duplicates. It is particularly useful when you need a full dataset from combined queries without worrying about removing duplicates. This guide provides practical examples to help you understand how to leverage UNION ALL in your SQL queries.