Set operators combine the results of two or more SELECT statements. Each SELECT statement within a set operation is called a component query. For set operators to work correctly, the component queries must follow specific rules regarding column count and data types.

UNION (Combines and removes duplicates)

The UNION operator combines the result sets of two or more SELECT statements and eliminates duplicate rows. It returns all distinct rows from all component queries.

Example 1: Oracle 19c SQL UNION to combine unique employee and customer names

SELECT first_name, last_name FROM employees -- Selects first and last names from employees
UNION
SELECT contact_first_name, contact_last_name FROM customers; -- Combines with unique first and last names from customers, duplicates removed

Explanation This query combines the distinct first and last names from both the employees and customers tables into a single result set. UNION automatically removes any duplicate name combinations that appear in both tables.

Example 2: Oracle 19c SQL UNION with different tables sharing common data types

SELECT product_id, product_name FROM products WHERE list_price > 500 -- Products with price > 500
UNION
SELECT category_id, category_name FROM product_categories WHERE category_id IN (1, 2, 3); -- Categories with specific IDs

Explanation This example demonstrates combining distinct product_id and product_name from products with category_id and category_name from product_categories. While different tables, the columns selected (product_id/category_id and product_name/category_name) must be type-compatible for UNION to succeed.

Example 3: Oracle 19c SQL UNION to find unique job titles and department names

SELECT job_id FROM employees -- Selects distinct job IDs
UNION
SELECT department_name FROM departments; -- Combines with distinct department names, assuming type compatibility

Explanation This query attempts to combine unique job_id values from employees and unique department_name values from departments. For UNION to function, the data types of the columns being combined must be implicitly convertible (e.g., VARCHAR2 and VARCHAR2).

UNION ALL (Combines all, retains duplicates – often better for performance)

The UNION ALL operator combines the result sets of two or more SELECT statements, including all duplicate rows. Unlike UNION, UNION ALL does not perform any sorting or duplicate removal, which often makes it more performant for large datasets when duplicates are acceptable or desired.

Example 1: Oracle 19c SQL UNION ALL to list all salaries from two departments

SELECT salary FROM employees WHERE department_id = 50 -- Salaries from department 50
UNION ALL
SELECT salary FROM employees WHERE department_id = 80; -- Salaries from department 80, including any duplicates across or within departments

Explanation This query combines all salaries from department 50 and department 80. UNION ALL includes every row from both result sets, even if the same salary value appears multiple times or in both departments.

Example 2: Oracle 19c SQL UNION ALL to show all product IDs from different categories

SELECT product_id FROM products WHERE category_id = 1 -- Product IDs from category 1
UNION ALL
SELECT product_id FROM products WHERE category_id = 2; -- Product IDs from category 2, retaining any overlaps

Explanation This example lists all product IDs from category 1 and category 2. UNION ALL ensures that if a product_id exists in both categories (though unlikely in this schema), it would appear twice in the final result.

Example 3: Oracle 19c SQL UNION ALL for combining sales data from different regions

SELECT order_date, total FROM orders WHERE customer_id IN (101, 102) -- Orders for specific customers
UNION ALL
SELECT order_date, order_total FROM historical_orders WHERE order_year = 2023; -- Historical orders for a specific year

Explanation This query combines recent order data for specific customers with historical order data from 2023. UNION ALL is used to efficiently merge all records without the overhead of duplicate checking, ideal for large data aggregation.

INTERSECT (Returns common rows)

The INTERSECT operator returns only the rows that are common to all SELECT statements in the set operation. It effectively finds the intersection of the result sets, similar to a logical AND. Duplicate rows within the intersected result are eliminated.

Example 1: Oracle 19c SQL INTERSECT to find employees who are also managers

SELECT employee_id, first_name, last_name FROM employees -- All employees
INTERSECT
SELECT manager_id, first_name, last_name FROM employees WHERE manager_id IS NOT NULL; -- Employees who appear as managers (by manager_id)

Explanation This query identifies employee_ids and names that appear in both the list of all employees and the list of employees who are managers. It effectively finds employees who hold managerial roles and whose employee_id matches their manager_id in some other row, considering that first_name and last_name must also match.

Example 2: Oracle 19c SQL INTERSECT for products available in two different warehouses

SELECT product_id FROM inventory WHERE warehouse_id = 1 -- Products in warehouse 1
INTERSECT
SELECT product_id FROM inventory WHERE warehouse_id = 2; -- Products in warehouse 2

Explanation This example finds product_ids that are present in both warehouse_id 1 and warehouse_id 2. INTERSECT returns only those product IDs that are common to both inventory lists.

Example 3: Oracle 19c SQL INTERSECT to find customers who placed orders in both 2022 and 2023

SELECT customer_id FROM orders WHERE EXTRACT(YEAR FROM order_date) = 2022 -- Customers with orders in 2022
INTERSECT
SELECT customer_id FROM orders WHERE EXTRACT(YEAR FROM order_date) = 2023; -- Customers with orders in 2023

Explanation This query identifies customer_ids that appear in the orders placed in 2022 AND in 2023. INTERSECT effectively shows customers who were active in both specified years.

MINUS (Returns rows from first query not in second)

The MINUS operator (or EXCEPT in some other SQL dialects) returns all unique rows from the first SELECT statement that are not found in the second SELECT statement. It provides a way to find differences between two result sets.

Example 1: Oracle 19c SQL MINUS to find employees who are not assigned to a department

SELECT employee_id FROM employees -- All employee IDs
MINUS
SELECT employee_id FROM employees WHERE department_id IS NOT NULL; -- Employee IDs assigned to a department

Explanation This query finds employee_ids that exist in the employees table but do not have a department_id assigned. It effectively lists employees who are unassigned.

Example 2: Oracle 19c SQL MINUS to list products only sold in the US region

SELECT product_id FROM sales WHERE region = 'US' -- Products sold in the US
MINUS
SELECT product_id FROM sales WHERE region != 'US'; -- Products sold in any region other than US

Explanation This example identifies product_ids that have sales records in the 'US' region but have no sales records in any other region. It lists products exclusively sold in the US.

Example 3: Oracle 19c SQL MINUS to find departments with no employees

SELECT department_id FROM departments -- All department IDs
MINUS
SELECT department_id FROM employees WHERE department_id IS NOT NULL; -- Department IDs that have employees

Explanation This query finds department_ids that exist in the departments table but do not have any corresponding entries in the employees table. It effectively identifies empty departments.

Rules for Using Set Operators (Column count, data types)

For UNION, UNION ALL, INTERSECT, and MINUS to work correctly, the component SELECT statements must adhere to specific rules:

Number of Columns: Each SELECT statement in the set operation must have the same number of columns in their select lists.

Data Types: The data type of each column in the first SELECT statement must be compatible (or implicitly convertible) with the data type of the corresponding column in all subsequent SELECT statements. The names of the columns in the result set are derived from the column names of the first SELECT statement.

Example 1: Oracle 19c SQL Rules for Set Operators - Matching Column Count and Types

SELECT employee_id, first_name FROM employees -- Two columns: NUMBER, VARCHAR2
UNION
SELECT product_id, product_name FROM products; -- Two columns: NUMBER, VARCHAR2 (compatible types)

Explanation This demonstrates a valid use of UNION where both component queries select two columns, and their corresponding data types (NUMBER with NUMBER, VARCHAR2 with VARCHAR2) are compatible, allowing the set operation to succeed.

Example 2: Oracle 19c SQL Rules for Set Operators - Mismatching Column Count (Error)

-- This code will produce an ORA-01789: query block has incorrect number of result columns
SELECT employee_id, first_name FROM employees
UNION
SELECT product_id FROM products; -- Mismatched column count (2 vs 1)

Explanation This example illustrates an incorrect use of UNION. The first SELECT statement has two columns, while the second has only one. This violates the rule that all component queries must have the same number of columns, leading to an ORA-01789 error.

Example 3: Oracle 19c SQL Rules for Set Operators - Compatible Data Types with implicit conversion

SELECT department_id, department_name FROM departments -- NUMBER, VARCHAR2
UNION ALL
SELECT employee_id, first_name || ' ' || last_name FROM employees WHERE employee_id < 110; -- NUMBER, VARCHAR2 (concatenation makes it VARCHAR2)

Explanation This example shows UNION ALL with compatible data types. department_id (NUMBER) aligns with employee_id (NUMBER). department_name (VARCHAR2) aligns with the concatenated first_name || ' ' || last_name (VARCHAR2). Oracle performs implicit conversion if necessary and possible, ensuring the operation is valid.