Set Operators in Oracle: Union, Union All, Intersect, and Minus
Set operators in Oracle allow you to combine the results of two or more SELECT statements into a single result set. These operators are useful for data manipulation and analysis.
1. Union
The UNION operator combines the result sets of two or more SELECT statements, eliminating duplicate rows.
Syntax:
SELECT column1, column2, ...
FROM table1
UNION
SELECT column1, column2, ...
FROM table2;
Example:
SELECT employee_id, first_name
FROM employees
WHERE department_id = 10
UNION
SELECT employee_id, first_name
FROM employees
WHERE department_id = 20;
2. Union All
The UNION ALL operator combines the result sets of two or more SELECT statements, including duplicate rows.
Syntax:
SELECT column1, column2, ...
FROM table1
UNION ALL
SELECT column1, column2, ...
FROM table2;
Example:
SELECT employee_id, first_name
FROM employees
WHERE department_id = 10
UNION ALL
SELECT employee_id, first_name
FROM employees
WHERE department_id = 20;
3. Intersect
The INTERSECT operator returns the rows that are present in both result sets of two SELECT statements.
Syntax:
SELECT column1, column2, ...
FROM table1
INTERSECT
SELECT column1, column2, ...
FROM table2;
Example:
SELECT employee_id
FROM employees
WHERE salary > 50000
INTERSECT
SELECT employee_id
FROM employees
WHERE department_id = 10;
4. Minus
The MINUS operator returns the rows that are present in the first result set but not in the second result set.
Syntax:
SELECT column1, column2, ...
FROM table1
MINUS
SELECT column1, column2, ...
FROM table2;
Example:
SELECT employee_id
FROM employees
WHERE department_id = 10
MINUS
SELECT employee_id
FROM employees
WHERE salary > 50000;
Important Considerations:
The number and data types of columns in the SELECT statements must be compatible.
The UNION, INTERSECT, and MINUS operators can be combined with other SQL clauses like WHERE, ORDER BY, and GROUP BY.
It's important to consider performance implications when using set operators, especially with large datasets. Indexing can significantly improve query performance.
By understanding and effectively using these set operators, you can perform powerful data analysis and manipulation tasks in Oracle Database.
Comments