9.2 INTERSECT and MINUS Operators

9.2 INTERSECT and MINUS Operators

While the UNION and UNION ALL operators combine and accumulate rows from multiple sources, enterprise SQL querying frequently requires isolating shared data or identifying specific data discrepancies between tables. Oracle SQL provides two specialized set operators for these requirements:

1. INTERSECT: Returns only the distinct rows that are common to both query result sets.
2. MINUS: Returns the distinct rows found in the first query that do not exist in the second query.

Understanding the mathematical logic, ordering constraints, duplicate handling, and NULL mechanics of INTERSECT and MINUS is a core requirement for the Oracle Database SQL Certified Associate (1Z0-071) exam.

---

The INTERSECT Operator

The INTERSECT operator implements mathematical set intersection ($A \cap B$). It processes two component queries and returns only those rows that appear in the result sets of both queries.

+-----------------------------------------------------------------------------------+
|                         INTERSECT VENN DIAGRAM (A INTERSECT B)                    |
|                                                                                   |
|                 /-------------\       /-------------\                             |
|                /               \     /               \                            |
|               /                 \   /                 \                           |
|              |                   | |                   |                          |
|              |                   |#|                   |                          |
|              |                   |#|                   |                          |
|               \                 /   \                 /                           |
|                \               /     \               /                            |
|                 \-------------/       \-------------/                             |
|                     Set A                 Set B                                   |
|                                                                                   |
|         [Only rows present in BOTH Set A AND Set B are returned]                  |
+-----------------------------------------------------------------------------------+

Operational Mechanics of INTERSECT

When Oracle executes an INTERSECT operation:

1. It executes the first SELECT statement and the second SELECT statement.
2. It matches rows across all projected columns.
3. It eliminates all duplicate occurrences, returning each shared row exactly once.
4. It automatically sorts the resulting dataset in ascending order based on the first projected column (and subsequent columns from left to right).

Concrete Example: Dual-Role Employees

Suppose you want to find all job roles that are currently staffed in both Department 50 (Shipping) and Department 80 (Sales):

SELECT job_id 
FROM employees 
WHERE department_id = 50
INTERSECT
SELECT job_id 
FROM employees 
WHERE department_id = 80;

Data Walkthrough:

• SELECT job_id FROM employees WHERE department_id = 50 returns: ['SH_CLERK', 'SH_CLERK', 'ST_CLERK', 'ST_CLERK', 'ST_MAN', 'SA_REP']
• SELECT job_id FROM employees WHERE department_id = 80 returns: ['SA_MAN', 'SA_MAN', 'SA_REP', 'SA_REP', 'SA_REP']

Evaluation:

1. Distinct jobs in Dept 50: {'SA_REP', 'SH_CLERK', 'ST_CLERK', 'ST_MAN'}
2. Distinct jobs in Dept 80: {'SA_MAN', 'SA_REP'}
3. Common distinct job present in both sets: {'SA_REP'}.

RESULT SET:
+---------+
| JOB_ID  |
+---------+
| SA_REP  |
+---------+

Exam Tip: Even if 'SA_REP' appeared 20 times in Department 50 and 50 times in Department 80, INTERSECT returns 'SA_REP' exactly once.

---

The MINUS Operator (Set Difference)

The MINUS operator implements mathematical set subtraction ($A \setminus B$ or $A - B$). It returns all distinct rows that are produced by the first SELECT query but are not present in the result set of the second SELECT query.

+-----------------------------------------------------------------------------------+
|                           MINUS VENN DIAGRAM (A MINUS B)                          |
|                                                                                   |
|                 /-------------\       /-------------\                             |
|                /   ########    \     /               \                            |
|               /   ##########    \   /                 \                           |
|              |   ############    | |                   |                          |
|              |   ############    | |                   |                          |
|              |   ############    | |                   |                          |
|               \   ##########    /   \                 /                           |
|                \   ########    /     \               /                            |
|                 \-------------/       \-------------/                             |
|                     Set A                 Set B                                   |
|                                                                                   |
|         [Shaded area represents rows in Set A that DO NOT exist in Set B]        |
+-----------------------------------------------------------------------------------+

Operational Mechanics of MINUS

When Oracle executes a MINUS operation:

1. It executes Query 1 and Query 2.
2. It takes the distinct rows from Query 1.
3. It checks each distinct row from Query 1 against the result set of Query 2.
4. If the row exists anywhere in Query 2's results, it is completely removed.
5. Any duplicate rows in Query 1 are removed, returning only unique remaining rows.
6. The final output is automatically sorted in ascending order by the first column.

---

Order Sensitivity: The Non-Commutative Property of MINUS

A critical distinction tested on the 1Z0-071 examination is mathematical commutativity:

• Commutative Operators: UNION, UNION ALL, and INTERSECT are commutative. Reversing the order of Query 1 and Query 2 produces the exact same result set rows.
• Non-Commutative Operator: MINUS is non-commutative. Reversing the order of the component queries yields fundamentally different result sets!

$\text{Query}_A \text{ MINUS } \text{Query}_B \neq \text{Query}_B \text{ MINUS } \text{Query}_A$

Sample Dataset Walkthrough

Consider two tables representing product catalogs:

-- STORE_NORTH
-- PROD_ID | PROD_NAME
-- 10      | Laptop
-- 20      | Monitor
-- 30      | Keyboard
-- 40      | Mouse

-- STORE_SOUTH
-- PROD_ID | PROD_NAME
-- 30      | Keyboard
-- 40      | Mouse
-- 50      | Printer
-- 60      | Scanner

Query A: Finding Products Exclusive to Store North (North MINUS South)

SELECT prod_id, prod_name FROM store_north
MINUS
SELECT prod_id, prod_name FROM store_south;

RESULT SET (Store North MINUS Store South):
+---------+-----------+
| PROD_ID | PROD_NAME |
+---------+-----------+
| 10      | Laptop    |
| 20      | Monitor   |
+---------+-----------+

Query B: Finding Products Exclusive to Store South (South MINUS North)

SELECT prod_id, prod_name FROM store_south
MINUS
SELECT prod_id, prod_name FROM store_north;

RESULT SET (Store South MINUS Store North):
+---------+-----------+
| PROD_ID | PROD_NAME |
+---------+-----------+
| 50      | Printer   |
| 60      | Scanner   |
+---------+-----------+

Exam Trap: Query A and Query B return mutually exclusive result sets. On the 1Z0-071 exam, pay close attention to which query appears first when analyzing MINUS questions.

---

Handling NULL Values in INTERSECT and MINUS

In standard SQL conditional filtering (WHERE col1 = col2), comparing NULL = NULL evaluates to UNKNOWN, which evaluates to FALSE in predicate testing. However, set operators adhere to special relational equality rules regarding NULLs:

The Set Operator NULL Rule: For the purposes of UNION, INTERSECT, and MINUS, Oracle treats two NULL values in corresponding columns as identical matching values.

Practical Demonstration:

-- Sample Table: T1 (Contains rows: (1, 'A'), (2, NULL), (3, 'C'))
-- Sample Table: T2 (Contains rows: (2, NULL), (4, 'D'))

-- 1. INTERSECT with NULLs
SELECT id, code FROM t1
INTERSECT
SELECT id, code FROM t2;

INTERSECT Result: (2, NULL)
-- Oracle matches row (2, NULL) from T1 with row (2, NULL) from T2 and returns it.

-- 2. MINUS with NULLs
SELECT id, code FROM t1
MINUS
SELECT id, code FROM t2;

MINUS Result:
+----+------+
| ID | CODE |
+----+------+
| 1  | A    |
| 3  | C    |
+----+------+
-- Row (2, NULL) was matched in T2 and successfully subtracted from T1.

This behavior makes MINUS far safer and more predictable than NOT IN subqueries when datasets contain NULL values (avoiding the infamous NOT IN (NULL) trap where subqueries return 0 rows).

---

Practical Enterprise Applications: Data Auditing & Reconciliation

In real-world database administration and data engineering, MINUS and INTERSECT are among the most powerful diagnostic tools for schema comparison, data migration audits, and reconciliation.

+-----------------------------------------------------------------------------------+
|                         DATA RECONCILIATION WORKFLOW                              |
|                                                                                   |
|   SOURCE TABLE                                      TARGET TABLE                  |
|   (STAGING_CUSTOMERS)                               (PROD_CUSTOMERS)              |
|   +-------------------+                             +-------------------+         |
|   | Cust 101 | $500   |                             | Cust 101 | $500   |         |
|   | Cust 102 | $750   |  ---- STAGE MINUS PROD ---> | Cust 102 | $750   | (Missing|
|   | Cust 103 | $900   |                             | Cust 103 | $920   |  in Prod|
|   +-------------------+                             +-------------------+  or Diff|
|             ^                                                 |                   |
|             +---------------- PROD MINUS STAGE ---------------+                   |
|                               (Missing in Staging)                                |
+-----------------------------------------------------------------------------------+

Application 1: Identifying Missing Child Records

To find all departments that currently have no assigned employees without writing an outer join or subquery:

-- Find Department IDs with ZERO employees
SELECT department_id FROM departments
MINUS
SELECT department_id FROM employees;

Application 2: Bi-Directional Symmetrical Difference Audit

When migrating data from a staging table STAGE_ORDERS to a production table PROD_ORDERS, auditors must verify that every row in staging made it to production without alterations, and that no rogue records exist in production. The Symmetrical Difference pattern accomplishes this in a single query:

-- Symmetrical Difference: Detects differences in either direction
(SELECT order_id, customer_id, order_total, order_date, 'IN_STAGE_NOT_PROD' AS issue_type
 FROM stage_orders
 MINUS
 SELECT order_id, customer_id, order_total, order_date, 'IN_STAGE_NOT_PROD' AS issue_type
 FROM prod_orders)
UNION ALL
(SELECT order_id, customer_id, order_total, order_date, 'IN_PROD_NOT_STAGE' AS issue_type
 FROM prod_orders
 MINUS
 SELECT order_id, customer_id, order_total, order_date, 'IN_PROD_NOT_STAGE' AS issue_type
 FROM stage_orders);

• If this query returns 0 rows, the two tables are perfectly identical in structure and data content.
• If any rows are returned, the issue_type column immediately indicates whether the discrepancy is an un-migrated record or an orphaned production record.

Application 3: Schema Comparison via Data Dictionary

Database administrators frequently compare table structures across development, test, and production environments using Oracle data dictionary views:

-- Identify columns existing in DEV but missing in PROD
SELECT column_name, data_type, data_length
FROM all_tab_columns
WHERE table_name = 'CUSTOMERS' AND owner = 'DEV_SCHEMA'
MINUS
SELECT column_name, data_type, data_length
FROM all_tab_columns
WHERE table_name = 'CUSTOMERS' AND owner = 'PROD_SCHEMA';

---

Comparison Matrix: All Four Oracle Set Operators