/*===============================================================
SET OPERATORS
Author : Vinayak Vishweshwara Dabgar
www.dabgarvinayakv.com
Oracle Apps DBA - Consultant | DBA - Developer | Database Architect | Performance Tuning Expert
PART 08A β SET OPERATORS
Topics Covered
--------------
β’ Set operator fundamentals
β’ UNION
β’ UNION ALL
β’ INTERSECT
β’ MINUS
β’ Query compatibility rules
===============================================================*/
/*---------------------------------------------------------------
SET OPERATOR INTRODUCTION
---------------------------------------------------------------*/
/*
Set operators combine results of multiple queries.
Common operators
UNION
UNION ALL
INTERSECT
MINUS
Rules
1 Same number of columns
2 Same data types
3 Same column order
*/
/*---------------------------------------------------------------
QUESTION 1
Employees in department 10 or 20
---------------------------------------------------------------*/
SELECT employee_id
FROM hr.employees
WHERE department_id = 10
UNION
SELECT employee_id
FROM hr.employees
WHERE department_id = 20;
/*---------------------------------------------------------------
QUESTION 2
Employees in department 30 or 40
---------------------------------------------------------------*/
SELECT employee_id
FROM hr.employees
WHERE department_id = 30
UNION
SELECT employee_id
FROM hr.employees
WHERE department_id = 40;
/*---------------------------------------------------------------
QUESTION 3
Employees hired before 2005 or after 2010
---------------------------------------------------------------*/
SELECT employee_id, hire_date
FROM hr.employees
WHERE hire_date < DATE '2005-01-01'
UNION
SELECT employee_id, hire_date
FROM hr.employees
WHERE hire_date > DATE '2010-01-01';
/*---------------------------------------------------------------
UNION ALL
---------------------------------------------------------------*/
/*
UNION ALL keeps duplicates.
*/
/*---------------------------------------------------------------
QUESTION 4
Employees in departments 50 and 60
---------------------------------------------------------------*/
SELECT employee_id
FROM hr.employees
WHERE department_id = 50
UNION ALL
SELECT employee_id
FROM hr.employees
WHERE department_id = 60;
/*---------------------------------------------------------------
QUESTION 5
Department ids from employees and departments
---------------------------------------------------------------*/
SELECT department_id
FROM hr.employees
UNION ALL
SELECT department_id
FROM hr.departments;
/*---------------------------------------------------------------
QUESTION 6
Department ids without removing duplicates
---------------------------------------------------------------*/
SELECT department_id
FROM hr.employees
UNION ALL
SELECT department_id
FROM hr.departments;
/*---------------------------------------------------------------
INTERSECT
---------------------------------------------------------------*/
/*
INTERSECT returns rows common to both queries.
*/
/*---------------------------------------------------------------
QUESTION 7
Department ids present in both tables
---------------------------------------------------------------*/
SELECT department_id
FROM hr.employees
INTERSECT
SELECT department_id
FROM hr.departments;
/*---------------------------------------------------------------
QUESTION 8
Jobs present in both job tables
---------------------------------------------------------------*/
SELECT job_id
FROM hr.employees
INTERSECT
SELECT job_id
FROM hr.jobs;
/*---------------------------------------------------------------
MINUS
---------------------------------------------------------------*/
/*
MINUS returns rows in first query not in second.
*/
/*---------------------------------------------------------------
QUESTION 9
Departments without employees
---------------------------------------------------------------*/
SELECT department_id
FROM hr.departments
MINUS
SELECT department_id
FROM hr.employees;
/*---------------------------------------------------------------
QUESTION 10
Jobs without employees
---------------------------------------------------------------*/
SELECT job_id
FROM hr.jobs
MINUS
SELECT job_id
FROM hr.employees;
/*---------------------------------------------------------------
QUESTION 11
Employees not in department 50
---------------------------------------------------------------*/
SELECT employee_id
FROM hr.employees
MINUS
SELECT employee_id
FROM hr.employees
WHERE department_id = 50;
/*---------------------------------------------------------------
QUESTION 12
Departments with employees
---------------------------------------------------------------*/
SELECT department_id
FROM hr.departments
INTERSECT
SELECT department_id
FROM hr.employees;
/*---------------------------------------------------------------
QUESTION 13
Department ids combined
---------------------------------------------------------------*/
SELECT department_id
FROM hr.departments
UNION
SELECT department_id
FROM hr.employees;
/*---------------------------------------------------------------
QUESTION 14
Department ids without duplicates
---------------------------------------------------------------*/
SELECT DISTINCT department_id
FROM
(
SELECT department_id FROM hr.employees
UNION ALL
SELECT department_id FROM hr.departments
);
/*---------------------------------------------------------------
QUESTION 15
Employee ids union example
---------------------------------------------------------------*/
SELECT employee_id
FROM hr.employees
WHERE department_id = 10
UNION
SELECT employee_id
FROM hr.employees
WHERE department_id = 90;
/*---------------------------------------------------------------
QUESTION 16
Employee ids intersect example
---------------------------------------------------------------*/
SELECT employee_id
FROM hr.employees
WHERE salary > 10000
INTERSECT
SELECT employee_id
FROM hr.employees
WHERE department_id = 90;
/*---------------------------------------------------------------
QUESTION 17
Employee ids minus example
---------------------------------------------------------------*/
SELECT employee_id
FROM hr.employees
WHERE department_id = 60
MINUS
SELECT employee_id
FROM hr.employees
WHERE salary > 10000;
/*---------------------------------------------------------------
QUESTION 18
Department union example
---------------------------------------------------------------*/
SELECT department_id
FROM hr.departments
UNION
SELECT department_id
FROM hr.employees;
/*---------------------------------------------------------------
QUESTION 19
Union employee salary ranges
---------------------------------------------------------------*/
SELECT employee_id, salary
FROM hr.employees
WHERE salary < 5000
UNION
SELECT employee_id, salary
FROM hr.employees
WHERE salary > 15000;
/*---------------------------------------------------------------
QUESTION 20
Union employees by hire date
---------------------------------------------------------------*/
SELECT employee_id
FROM hr.employees
WHERE hire_date < DATE '2003-01-01'
UNION
SELECT employee_id
FROM hr.employees
WHERE hire_date > DATE '2012-01-01';
/*---------------------------------------------------------------
END OF PART 08A
---------------------------------------------------------------*/
/*===============================================================
PART 08B β ADVANCED SET OPERATORS
Continuation of PART 08A
Topics Covered
--------------
β’ Combining multiple set operators
β’ Ordering results
β’ Data comparison queries
β’ ETL validation queries
β’ Performance considerations
===============================================================*/
/*---------------------------------------------------------------
MULTIPLE SET OPERATORS
---------------------------------------------------------------*/
/*
Set operators can be chained together.
Execution follows left-to-right order unless
parentheses are used.
*/
/*---------------------------------------------------------------
QUESTION 21
Employees in dept 10 or 20 but not in dept 30
---------------------------------------------------------------*/
(
SELECT employee_id
FROM hr.employees
WHERE department_id = 10
UNION
SELECT employee_id
FROM hr.employees
WHERE department_id = 20
)
MINUS
SELECT employee_id
FROM hr.employees
WHERE department_id = 30;
/*---------------------------------------------------------------
QUESTION 22
Employees in dept 50 or 60 and earning > 10000
---------------------------------------------------------------*/
(
SELECT employee_id
FROM hr.employees
WHERE department_id = 50
UNION
SELECT employee_id
FROM hr.employees
WHERE department_id = 60
)
INTERSECT
SELECT employee_id
FROM hr.employees
WHERE salary > 10000;
/*---------------------------------------------------------------
ORDERING SET OPERATOR RESULTS
---------------------------------------------------------------*/
/*
ORDER BY can appear only once
at the end of the combined query.
*/
/*---------------------------------------------------------------
QUESTION 23
Union employee ids sorted
---------------------------------------------------------------*/
SELECT employee_id
FROM hr.employees
WHERE department_id = 10
UNION
SELECT employee_id
FROM hr.employees
WHERE department_id = 20
ORDER BY employee_id;
/*---------------------------------------------------------------
QUESTION 24
Union salaries sorted descending
---------------------------------------------------------------*/
SELECT salary
FROM hr.employees
WHERE department_id = 50
UNION
SELECT salary
FROM hr.employees
WHERE department_id = 60
ORDER BY salary DESC;
/*---------------------------------------------------------------
SET OPERATORS WITH MULTIPLE COLUMNS
---------------------------------------------------------------*/
/*---------------------------------------------------------------
QUESTION 25
Employee and department union
---------------------------------------------------------------*/
SELECT employee_id, department_id
FROM hr.employees
WHERE department_id = 10
UNION
SELECT employee_id, department_id
FROM hr.employees
WHERE department_id = 20;
/*---------------------------------------------------------------
QUESTION 26
Employee salary comparison
---------------------------------------------------------------*/
SELECT employee_id, salary
FROM hr.employees
WHERE salary > 15000
UNION
SELECT employee_id, salary
FROM hr.employees
WHERE salary < 3000;
/*---------------------------------------------------------------
DATA COMPARISON QUERIES
---------------------------------------------------------------*/
/*
Set operators are often used in
data reconciliation tasks.
*/
/*---------------------------------------------------------------
QUESTION 27
Departments in departments table but not employees
---------------------------------------------------------------*/
SELECT department_id
FROM hr.departments
MINUS
SELECT department_id
FROM hr.employees;
/*---------------------------------------------------------------
QUESTION 28
Departments present in both tables
---------------------------------------------------------------*/
SELECT department_id
FROM hr.departments
INTERSECT
SELECT department_id
FROM hr.employees;
/*---------------------------------------------------------------
QUESTION 29
Departments appearing in either table
---------------------------------------------------------------*/
SELECT department_id
FROM hr.departments
UNION
SELECT department_id
FROM hr.employees;
/*---------------------------------------------------------------
ETL VALIDATION EXAMPLES
---------------------------------------------------------------*/
/*
Useful for comparing staging vs production data.
*/
/*---------------------------------------------------------------
QUESTION 30
Employee ids missing from staging table
---------------------------------------------------------------*/
SELECT employee_id
FROM hr.employees
MINUS
SELECT employee_id
FROM hr.employee_staging;
/*---------------------------------------------------------------
QUESTION 31
Employee ids present in staging but not production
---------------------------------------------------------------*/
SELECT employee_id
FROM hr.employee_staging
MINUS
SELECT employee_id
FROM hr.employees;
/*---------------------------------------------------------------
QUESTION 32
Employees common to both tables
---------------------------------------------------------------*/
SELECT employee_id
FROM hr.employees
INTERSECT
SELECT employee_id
FROM hr.employee_staging;
/*---------------------------------------------------------------
QUESTION 33
Union employees from production and staging
---------------------------------------------------------------*/
SELECT employee_id
FROM hr.employees
UNION
SELECT employee_id
FROM hr.employee_staging;
/*---------------------------------------------------------------
QUESTION 34
Union all employees from production and staging
---------------------------------------------------------------*/
SELECT employee_id
FROM hr.employees
UNION ALL
SELECT employee_id
FROM hr.employee_staging;
/*---------------------------------------------------------------
SET OPERATORS WITH SUBQUERIES
---------------------------------------------------------------*/
/*---------------------------------------------------------------
QUESTION 35
Departments with high salary employees
---------------------------------------------------------------*/
SELECT department_id
FROM
(
SELECT department_id
FROM hr.employees
WHERE salary > 10000
)
UNION
SELECT department_id
FROM hr.departments;
/*---------------------------------------------------------------
QUESTION 36
Employees in high salary departments
---------------------------------------------------------------*/
SELECT employee_id
FROM hr.employees
WHERE department_id IN
(
SELECT department_id
FROM hr.employees
WHERE salary > 10000
)
UNION
SELECT employee_id
FROM hr.employees
WHERE salary > 20000;
/*---------------------------------------------------------------
QUESTION 37
Compare employee salaries with job salary ranges
---------------------------------------------------------------*/
SELECT employee_id
FROM hr.employees
WHERE salary > 20000
UNION
SELECT employee_id
FROM hr.employees
WHERE salary < 3000;
/*---------------------------------------------------------------
QUESTION 38
Employees hired before 2000 or after 2015
---------------------------------------------------------------*/
SELECT employee_id
FROM hr.employees
WHERE hire_date < DATE '2000-01-01'
UNION
SELECT employee_id
FROM hr.employees
WHERE hire_date > DATE '2015-01-01';
/*---------------------------------------------------------------
QUESTION 39
Departments with salary extremes
---------------------------------------------------------------*/
SELECT department_id
FROM hr.employees
WHERE salary > 15000
UNION
SELECT department_id
FROM hr.employees
WHERE salary < 3000;
/*---------------------------------------------------------------
QUESTION 40
Employees with extreme salaries
---------------------------------------------------------------*/
SELECT employee_id
FROM hr.employees
WHERE salary > 20000
UNION
SELECT employee_id
FROM hr.employees
WHERE salary < 2000;
/*---------------------------------------------------------------
SET OPERATOR PERFORMANCE NOTES
---------------------------------------------------------------*/
/*
Optimization Tips
1 UNION removes duplicates (slower)
2 UNION ALL is faster
3 Ensure proper indexes on compared columns
4 Avoid unnecessary sorting
5 Use UNION ALL when duplicates are acceptable
*/
/*---------------------------------------------------------------
END OF PART 08
Disclaimer and Limitation of Liability
The author provides all scripts, queries, and related materials βas isβ, without any express
or implied warranties, including but not limited to warranties of accuracy, completeness,
reliability, merchantability, or fitness for a particular purpose.
By using, executing, or implementing any part of this material, the user acknowledges
and agrees that they do so at their own risk. The author shall not be held liable for any
direct, indirect, incidental, consequential, special, or exemplary damages, including but
not limited to loss of data, loss of profits, system failures, security breaches, or any
other damages or losses arising from the use or misuse of these materials.
Users are strongly advised to thoroughly review, validate, and test all scripts, queries,
and configurations in a controlled, non-production (test) environment prior to deploying
them in any live or production systems.
It is the sole responsibility of the user to ensure that the use of these materials
complies with all applicable local, national, and international laws, regulations,
and organizational policies.
By proceeding to use these materials, the user agrees to indemnify and hold harmless
the author from any claims, damages, liabilities, or expenses arising from their use.
---------------------------------------------------------------*/