ExamFinch.com My Study

Study guide

9.2 INTERSECT and MINUS Operators

Practice this exam

Oracle Database SQL Certified Associate 1Z0-071

Track your progress as you read

Finished sections get a check mark.

1 Chapter 1: Relational Database Concepts & Logical Modeling

1.1 Relational Database Architecture & Terminology 1.2 Entity Relationship Modeling & SQL Mapping 1.3 Database Normalization & Design Fundamentals

2 Chapter 2: Retrieving Data with SQL SELECT

2.1 SQL SELECT Fundamentals & Projection 2.2 Literal Values, Concatenation, and Quote Operators 2.3 Handling NULLs & Distinct Rows in Projection

3 Chapter 3: Restricting and Sorting Query Data

3.1 WHERE Clause Filtering & Comparison Operators 3.2 Logical Conditions & Operator Precedence 3.3 Sorting Data with ORDER BY 3.4 Substitution Variables & Session Directives

4 Chapter 4: Customizing Output with Single-Row Functions

4.1 Character Manipulation & Case Conversion 4.2 Numeric & Mathematical Functions 4.3 Date Arithmetic & Date Manipulation Functions 4.4 Nesting Single-Row Functions

5 Chapter 5: Conversion Functions and Conditional Expressions

5.1 Implicit vs Explicit Datatype Conversion 5.2 TO_CHAR, TO_NUMBER, and TO_DATE Functions 5.3 General NULL-Handling Functions 5.4 Conditional Expressions: CASE and DECODE

6 Chapter 6: Reporting Aggregated Data with Group Functions

6.1 Aggregate Functions & NULL Mechanics 6.2 GROUP BY Clause & Aggregation Rules 6.3 Filtering Groups with the HAVING Clause

7 Chapter 7: Displaying Data from Multiple Tables (Joins)

7.1 ANSI/ISO SQL Equijoins & Natural Joins 7.2 Non-Equijoins, Self Joins, and Cartesian Products 7.3 Outer Joins: LEFT, RIGHT, and FULL

8 Chapter 8: Solving Queries with Subqueries

8.1 Single-Row Subqueries 8.2 Multiple-Row Subqueries & Inline Views 8.3 Correlated Subqueries & EXISTS Logic 8.4 Subquery Cardinality & NULL Traps

9 Chapter 9: Set Operators

9.1 UNION and UNION ALL Operators 9.2 INTERSECT and MINUS Operators 9.3 Set Operator Guidelines & ORDER BY Rules

10 Chapter 10: Managing Tables with DML & Transaction Control

10.1 INSERT, UPDATE, and DELETE Statements 10.2 MERGE and Multitable Inserts 10.3 Transaction Management: COMMIT, ROLLBACK, and SAVEPOINT

11 Chapter 11: Managing Tables and Relationships with DDL

11.1 CREATE TABLE & Oracle Built-In Datatypes 11.2 Integrity Constraints 11.3 Modifying Tables & Managing Constraints 11.4 Global Temporary Tables & External Tables

12 Chapter 12: Views

12.1 Simple vs Complex Views 12.2 DML Operations on Views & View Constraints

13 Chapter 13: Indexes, Synonyms, and Sequences

13.1 Sequences 13.2 Indexes 13.3 Synonyms

14 Chapter 14: Controlling User Access & Privileges

14.1 System Privileges 14.2 Object Privileges 14.3 Roles & Privilege Management

15 Chapter 15: Oracle Data Dictionary Views

15.1 Data Dictionary Structure & View Prefixes 15.2 Querying Schema & Object Metadata 15.3 Inspecting Constraints, Privileges, and Comments

16 Chapter 16: Managing Data in Different Time Zones

16.1 Datetime Data Types & Time Zone Offsets 16.2 Interval Data Types & Arithmetic 16.3 Time Zone Functions & Session Environment

9.2 INTERSECT and MINUS Operators

Key Takeaways Explain This Topic AI

The INTERSECT operator returns only distinct rows common to both query result sets, automatically eliminating duplicates and sorting output in ascending order.

The MINUS operator performs set difference (subtraction), returning distinct rows present in the first query result set that do not exist in the second query result set.

INTERSECT is mathematically commutative (A INTERSECT B = B INTERSECT A), whereas MINUS is non-commutative (A MINUS B != B MINUS A); query order is critical for MINUS.

Both INTERSECT and MINUS handle NULL values identically to non-null values for row matching, treating two NULL values in corresponding columns as matching duplicates.

The symmetrical difference pattern (A MINUS B) UNION ALL (B MINUS A) provides a comprehensive audit mechanism for detecting bi-directional data and schema discrepancies.

Last updated: August 2026

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:

INTERSECT : Returns only the distinct rows that are common to both query result sets.

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 ∩ B A \cap B A ∩ 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:

It executes the first SELECT statement and the second SELECT statement.

It matches rows across all projected columns.

It eliminates all duplicate occurrences , returning each shared row exactly once.

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:

  • Distinct jobs in Dept 50: {'SA_REP', 'SH_CLERK', 'ST_CLERK', 'ST_MAN'}
  • Distinct jobs in Dept 80: {'SA_MAN', 'SA_REP'}

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 ∖ B A \setminus B A ∖ B or A − B A - B 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:

It executes Query 1 and Query 2.

It takes the distinct rows from Query 1.

It checks each distinct row from Query 1 against the result set of Query 2.

If the row exists anywhere in Query 2's results, it is completely removed.

Any duplicate rows in Query 1 are removed, returning only unique remaining rows.

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! Query A MINUS Query B ≠ Query B MINUS Query A \text{Query}_A \text{ MINUS } \text{Query}_B \neq \text{Query}_B \text{ MINUS } \text{Query}_A Query A ​ MINUS Query B ​  = Query B ​ MINUS 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

Operator Mathematical Operation Duplicate Handling Default Output Sorting Commutative? Common Use Case

UNION A ∪ B A \cup B A ∪ B Eliminates all duplicates Yes (Ascending by col 1, 2...) Yes ( A ∪ B = B ∪ A A \cup B = B \cup A A ∪ B = B ∪ A ) Combining disjoint datasets with unique constraint requirements

UNION ALL A + B A + B A + B (Multiset) Preserves all duplicates No (Natural query order) Yes (Same multiset rows) High-performance consolidation of large logs or tables

INTERSECT A ∩ B A \cap B A ∩ B Eliminates all duplicates Yes (Ascending by col 1, 2...) Yes ( A ∩ B = B ∩ A A \cap B = B \cap A A ∩ B = B ∩ A ) Finding overlapping entities or shared attributes

MINUS A ∖ B A \setminus B A ∖ B Eliminates all duplicates Yes (Ascending by col 1, 2...) NO ( A − B ≠ B − A A - B \neq B - A A − B  = B − A ) Finding missing rows, orphan keys, or data audit discrepancies