View video tutorial
ORACLE Set Operators
ORACLE
Oracle set operators combine the results of two or more independent SELECT statements to create a single result set.
ORACLE Set Operators
➔ The primary Oracle set operators are:
- UNION: Returns all distinct rows selected by the query on both sides.
- UNION ALL: Returns all rows selected by both side queries, including all duplicates.
- INTERSECT: Returns only the distinct rows that are common in the result sets of both queries.
- MINUS: Returns all distinct rows selected by the first query that are not present in the result set of the second query (subtracting the results of the second query for the first).
Example: UNION
/* UNION: Returns all unique employee IDs from either table */
SELECT employee_id FROM employees
UNION
SELECT employee_id FROM job_history
ORDER BY employee_id;
Example: UNION ALL
/* UNION ALL: Returns all employee IDs from either table, including duplicate values. */
SELECT employee_id FROM employees
UNION ALL
SELECT employee_id FROM job_history
ORDER BY employee_id;
Example: INTERSECT
/* INTERSECT: Returns only employee IDs present in both tables */
SELECT employee_id FROM employees
INTERSECT
SELECT employee_id FROM job_history
ORDER BY employee_id;
Example: MINUs
/* MINUS: Returns employee IDs in the employees table that are not in the job_history table */
SELECT employee_id FROM employees
MINUS
SELECT employee_id FROM job_history
ORDER BY employee_id;