PL/SQL UNION Operator

In PL/SQL (Procedural Language/Structured Query Language), the UNION operator is one of the most commonly used set operators. It combines the result sets of two or more SELECT statements into a single result set while removing any duplicate rows.

In this article, We will learn about PL/SQL UNION Operator with the help of examples and so on.

PL/SQL UNION Operator

• The UNION operator in PL/SQL, as well as in SQL is used to combine the results of two or more SELECT queries into a single result set.
  
• This operator is particularly useful for merging similar datasets from different tables or queries into one comprehensive dataset.

Syntax

The syntax of the UNION operator is straightforward. Here’s how you can use it:

SELECT column1, column2, ...

FROM table1

WHERE condition

UNION

SELECT column1, column2, ...

FROM table2

WHERE condition;

Explanation:

• SELECT column1, column2, ... FROM table1 WHERE condition: The first SELECT statement retrieves data from table1 based on a specified condition.
• UNION: The UNION operator combines the result of the first query with the second.
• SELECT column1, column2, ... FROM table2 WHERE condition: The second SELECT statement retrieves data from table2.

Example of PL/SQL UNION Operator

Step 1: Create Two Tables: employees and contractors

-- Create the employees table
CREATE TABLE employees (
    employee_id NUMBER(5),
    name VARCHAR2(50)
);

-- Create the contractors table
CREATE TABLE contractors (
    contractor_id NUMBER(5),
    name VARCHAR2(50)
);

Step 2. Insert Sample Data into the Tables

-- Insert data into the employees table
INSERT INTO employees (employee_id, name) VALUES (101, 'Alice');
INSERT INTO employees (employee_id, name) VALUES (102, 'Bob');
INSERT INTO employees (employee_id, name) VALUES (103, 'Charlie');

-- Insert data into the contractors table
INSERT INTO contractors (contractor_id, name) VALUES (201, 'Alice');
INSERT INTO contractors (contractor_id, name) VALUES (202, 'David');
INSERT INTO contractors (contractor_id, name) VALUES (203, 'Eve');

Example 1: Combining Results from Two Tables

Suppose we have two tables: employees and contractors. Both have columns for employee IDs and names. We want to get a list of all people working in our organization.

SELECT employee_id, name FROM employees
UNION
SELECT contractor_id, name FROM contractors;

Output:

Explanation: This query selects the employee_id and name from both tables and combines them. The UNION operator ensures that duplicate names are removed, resulting in a unique list of all workers, whether they are full-time employees or contractors. The result will include:

• All unique names from both tables.
• The combined list sorted in ascending order by default.

Example 2: Using UNION with Different Conditions

Let’s extend our query by adding conditions. Assume we want to list only those employees and contractors whose names start with the letter "A".

SELECT employee_id, name FROM employees WHERE name LIKE 'A%'
UNION
SELECT contractor_id, name FROM contractors WHERE name LIKE 'A%';

Output

Explanation: The query filters out names starting with "A" from both tables and merges the results. The UNION operator ensures that duplicates are removed while combining the results. The output will:

• Include only names that start with "A".
• Exclude any duplicates between the two tables.
• Present the final list sorted in ascending order.

Conclusion

The UNION operator is a powerful tool in PL/SQL that allows for the combination of multiple result sets while eliminating duplicates. It is especially useful when you want to merge data from different tables that share similar structures. Remember, the number and types of columns must match across the combined queries.